Continuing on the theme of SQL Server 2016, my boss sent me a copy of Kalen Delaney’s (b|t) SQL Server 2016 In-Memory Internals white paper. I delved into in-memory internals when SQL Server 2014 released, but then other projects got in the way of my delving time.
I created an in-memory table to explore the contents of the in-memory log records. To do this, I used the table-based function fn_dblog_xtp. If you are familar with fn_dblog, fn_dblog_xtp contains the same column list, with 12 new columns added. A sample SELECT statement is shown below, with the new columns highlighted:
USE yourdatabase; SELECT f.[Current LSN], f.Operation, f.Context, f.[Transaction ID], f.operation_desc, f.tx_end_timestamp, f.total_size, f.xtp_object_id f.newrow_identity, f.newrow_data, f.newrow_datasize, f.oldrow_begin_timestamp, f.oldrow_identity, f.oldrow_key_data, f.oldrow_key_datasize, f.xtp_description, f.[Log Record Fixed Length], f.[Log Record Length], f.[Flag Bits], f.[Log Reserve], f.[Log Record] FROM fn_dblog_xtp(NULL,NULL) WHERE Operation LIKE '%HK%';
The two NULL parameters being passed are the start and end Log Sequence Numbers (LSN). Passing NULLs will return all log records. The WHERE Operation LIKE ‘%HK%’ will return only log records related to in-memory tables.
Transactions against an in-memory table will return a single row in the result set from fn_dblog, while the result set from fn_dblog_xtp will contain a row for all activity. For example, if you insert 100 rows into an in-memory table, fn_dblog will contain 3 records: a BEGIN TRANSACTION, a single row for the INSERT, and a COMMIT TRANSACTION. fn_dblog_xtp will contain 102 records: a BEGIN TRANSACTION, and a row for each row inserted, and a COMMIT TRANSACTION.
One of the new columns in fn_dblog_xtp is xtp_object_id. I tried to join this to the sys.objects table to return the object name, but the values didn’t match. After banging my head against my monitor for a while, I posed the question to #sqlhelp on Twitter. Andreas Wolter (b|t) responded that a correlation can be made using DMV sys.memory_optimized_tables_internal_attributes. Excited, I tried to join fn_dblog_xtp to the DMV, but was disappointed.
On further examiniation, I determined the xtp_object_id from fn_dblog_xtp was a very large positive number while xtp_object_id from the DMV was a very large negative number. I looked closer and found that the first in-memory object created in a database will receives two xtp_object_id values, 2147483648 and -2147483648. Both of these values are increment for additional in-memory objects. So your second in-memory object will receive values of 2147483649 and -2147483647.
So, to bring a very long story to a close, you can join fn_dblog_xtp to the DMV using the following query:
SELECT f.[Current LSN], f.Operation, f.Context, f.[Transaction ID], f.operation_desc, f.tx_end_timestamp, f.total_size, OBJECT_NAME(m.object_id) AS ObjectName, f.newrow_identity, f.newrow_data, f.newrow_datasize, f.oldrow_begin_timestamp, f.oldrow_identity, f.oldrow_key_data, f.oldrow_key_datasize, f.xtp_description, f.[Log Record Fixed Length], f.[Log Record Length], f.[Flag Bits], f.[Log Reserve], f.[Log Record] FROM fn_dblog_xtp(NULL,NULL) AS f INNER JOIN sys.memory_optimized_tables_internal_attributes m ON (f.xtp_object_id - 2147483648) = (m.xtp_object_id + 2147483648) WHERE Operation LIKE '%HK%'
Once again, I hope this saves some folks frustration interrogating the transaction log for in-memory objects.
I finally am getting to work with SQL Server 2016 and spent the past two days building out an Availability Group to test the new read-only replica functionality. This allows connections with an ApplicationIntent of ReadOnly to round-robin between all instances in the READ_ONLY_ROUTING_LIST, load balancing the read-only activity.
For my test, I built out three replicas and set all three to be readable secondaries. Using Microsoft’s documentation on configuring read-only routing, I coded the following:
ALTER AVAILABILITY GROUP SKREEBYHA MODIFY REPLICA ON 'AGNODE1\SKREEBYAG' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG') ) );
To test the new setup, I used the SQLCMD examples from Eric Russo’s blog post on Load Balancing 101. Running the following command:
SQLCMD -S SKREEBYLIST -K ReadOnly -d testdb -Q "SELECT @@SERVERNAME"
returned AGNODE2\SKREEBYAG in each case. I could not figure out what I was doing wrong. After much frustration, I noticed the routing check query at the bottom of Eric’s post:
SELECT ar_Primary.replica_server_name 'Primary', ar_secondary.replica_server_name 'Route_to', ror.routing_priority 'Priority' FROM sys.availability_read_only_routing_lists ror INNER JOIN sys.availability_replicas ar_Primary ON ror.replica_id = ar_Primary.replica_id INNER JOIN sys.availability_replicas ar_secondary ON ror.read_only_replica_id = ar_secondary.replica_id INNER JOIN sys.availability_groups ag ON ag.group_id = ar_Primary.group_id WHERE ag.name = 'SKREEBYHA' ORDER BY 1,3
Running it returned the following results:
So, in technical terms, something was screwy. Looking at Eric’s post again, I noticed his third example for setting a routing list:
ALTER AVAILABILITY GROUP SQL2016AG MODIFY REPLICA ON 'MyNode3' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST= (('MyNode4','MyNode1','MyNode2')) ) )
Notice the second set of parentheses around the READ_ONLY_ROUTING_LIST value. Based on this, I changed my code to:
ALTER AVAILABILITY GROUP SKREEBYHA MODIFY REPLICA ON 'AGNODE1\SKREEBYAG' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=(('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG')) ) );
and, voila, the routing list check query returned the following:
Rerunning the SQLCMD test, the read-only connections round-robined between AGNODE2 and AGNODE3.
It’s been a while since my last blog post. Since then, I’ve been to PASS Summit, which was the best one yet. I’ve presented at SQL Saturdays in Portland, Cleveland, and Iowa City. And, most excitingly, started a new job with BlueMetal. I am in the middle of week 3 and am really excited about the new challenge.
I’ll be presenting on Using PowerShell to Automate Your Restore Strategy at SQL Saturday Columbus on July 16th. It will be my first time in Columbus, Ohio and I am looking forward to seeing old friends, making new ones, and teaching and learning. You can register and find more information at the link above. If you are in the area, don’t miss the opportunity to get a free day of training while networking with your peers in the SQL Server community.
Previously, I’ve blogged about returning formatted estimated_completion_time values from sys.dm_exec_requests and getting sortable output from sp_whoisactive. In this post I combine the two, joining the two result sets together. In my environment, we use Ola Hallengren’s Maintenance Solution to run our backups. Because the backup job defaults to run backups for all user databases, it can be difficult to tell which database is currently being backed up.
USE [master] GO --Drop procedure if it exists IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_join_whoisactive_execrequests]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].sp_join_whoisactive_execrequests GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* ============================================= -- Author: Frank Gill -- Create date: 2015-10-27 -- Description: This procedure executes Adam Machanic's sp_whoisactive, dumps the results to a temp table, and then joins those results to -- sys.dm_exec_requests. This will return estimated completion time for the following commands along with the statement being executed -- in a single pass: -- ALTER INDEX REORGANIZE -- AUTO_SHRINK option with ALTER DATABASE -- BACKUP DATABASE -- DBCC CHECKDB -- DBCC CHECKFILEGROUP -- DBCC CHECKTABLE -- DBCC INDEXDEFRAG -- DBCC SHRINKDATABASE -- DBCC SHRINKFILE -- RECOVERY -- RESTORE DATABASE, -- ROLLBACK -- TDE ENCRYPTION -- ============================================= */ CREATE PROCEDURE sp_join_whoisactive_execrequests AS BEGIN IF OBJECT_ID('tempdb..#WhoIsActive') IS NOT NULL BEGIN SELECT 'Dropping' DROP TABLE #WhoIsActive END CREATE TABLE #WhoIsActive ([dd hh:mm:ss.mss] VARCHAR(20) ,[dd hh:mm:ss.mss (avg)] VARCHAR(20) ,[session_id] SMALLINT ,[sql_text] XML ,[sql_command] XML ,[login_name] SYSNAME ,[wait_info] NVARCHAR(4000) ,[tran_log_writes] NVARCHAR(4000) ,[CPU] VARCHAR(30) ,[tempdb_allocations] VARCHAR(30) ,[tempdb_current] VARCHAR(30) ,[blocking_session_id] SMALLINT ,[blocked_session_count] VARCHAR(30) ,[reads] VARCHAR(30) ,[writes] VARCHAR(30) ,[physical_reads] VARCHAR(30) ,[query_plan] XML ,[used_memory] VARCHAR(30) ,[status] VARCHAR(30) ,[tran_start_time] DATETIME ,[open_tran_count] VARCHAR(30) ,[percent_complete] VARCHAR(30) ,[host_name] SYSNAME ,[database_name] SYSNAME ,[program_name] SYSNAME ,[start_time] DATETIME ,[login_time] DATETIME ,[request_id] INT ,[collection_time] DATETIME) /* Execute sp_whoisactive and write the result set to the temp table created above */ EXEC master.dbo.sp_WhoIsActive @get_plans = 2, @get_outer_command = 1, @get_transaction_info = 1, @get_avg_time = 1, @find_block_leaders = 1, @destination_table = #WhoIsActive /* Join the #whoisactive temp table to sys.dm_exec_requests to get estimated completion time and query information in one pass */ SELECT CASE WHEN ((r.estimated_completion_time/1000)%3600/60) < 10 THEN '0' + CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60) ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60) END + ':' + CASE WHEN ((r.estimated_completion_time/1000)%60) < 10 THEN '0' + CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60) ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60) END AS [Time Remaining], r.percent_complete, r.session_id, w.login_name, w.[host_name], w.sql_command, w.sql_text FROM #WhoIsActive w RIGHT OUTER JOIN sys.dm_exec_requests r ON r.session_id = w.session_id WHERE r.percent_complete > 0; END
This procedure takes the output from sp_whoisactive and joins it to sys.dm_exec_requests on session_id, returning the estimated completion time and current backup statement in a single pass. Here is a sample result set from the procedure (Click to Enlarge):
The sql_command column contains the full command being run, in this example Ola Hallengren’s DatabaseBackup procedure. The sql_text column contains the actual SQL statement being run, BACKUP DATABASE [AdventureWorks2012] TO DISK…. The DatabaseBackup procedure runs database backups in alphabetical order based on the database name, so this allows tracking a job progress.
Many thanks to Adam Machanic (b|t) for sp_whoisactive, Ola Hallengren (b|t) for his Maintenance Solution, and Aaron Bertrand (b|t) for his advice on formatting the estimated completion time more elegantly. I finally had an opportunity to use it.
I hope you can put this procedure to good use. Let me know if you have any questions.
SQL Saturday Iowa City is coming up Saturday, May 30th. I will be there presenting How to Identify What Data is Corrupt at 1pm. It will be my third year in Iowa City and it is always a wonderful time. You can find more information and register for the event here. If you’ve never been to a SQL Saturday, it is a wonderful opportunity to get a free days of training and network with your fellow SQL Server professionals.
Last week, I posted Identifying Corrupt Rows USING DBCC CHECKDB and DBCC PAGE. I wrote the scripts in that post to identify corrupt rows in a client’s database. As we continued to investigate, things got strange. Repeated runs of DBCC CHECKDB were generating different results. Each one showed serious corruption, but on different pages with each run. In an effort to remove the storage system from the list of potential causes, I needed to move to data and log files for the database to a a different storage array. This post from TechNet gave me the steps I needed to run.
In this case, only one database had to be moved. To move more than one database, I wrote the following script to generate the statements specified in the TechNet article. In addition to the ALTER DATABASE statements, the script checks to see if the database is part of an availability group. If it is, a statement is generated to remove it. As adding a database to an availability group is far more complicated than removing it, I will leave that to the user.
As usual, I hope you find this useful. And remember, executing code you find on the internet against your production instance always has the potential to become a resume updating event. Use caution and test.
/*--------------------------------------------------------------- Created By - Frank Gill Created On - 2014-10-13 Script to generate ALTER DATABASE statement to move a database to a different storage location ---------------------------------------------------------------*/ USE master; SET NOCOUNT ON; /* Drop temp table if it exists */ IF OBJECT_ID('tempdb..#dbfiles') IS NOT NULL BEGIN DROP TABLE #dbfiles; END /* Declare local variables and create temp table */ DECLARE @dbname SYSNAME; DECLARE @agname SYSNAME; DECLARE @sqlstr NVARCHAR(4000); DECLARE @physicalname NVARCHAR(260); DECLARE @logicalname SYSNAME; DECLARE @filetype TINYINT; DECLARE @datapath NVARCHAR(1000); DECLARE @logpath NVARCHAR(1000); DECLARE @agdb BIT = 0; DECLARE @loopcount INT = 1; DECLARE @looplimit INT; CREATE TABLE #dbfiles (logicalname SYSNAME ,physicalname NVARCHAR(260) ,filetype TINYINT) /* Initialize variables */ SET @dbname = 'Your_Database'; SET @datapath = 'Your_Data_Path'; SET @logpath = 'Your_Log_Path'; /* If the database is part of an availability group, generate a statement to remove it prior to taking the database offline */ IF(SELECT replica_ID FROM sys.databases WHERE name = @dbname) IS NOT NULL BEGIN PRINT '/* This database is part of an availability group and must be removed prior to taking the database offline You will need to add the database back to the availability group after the database files are moved */' SELECT @agname = g.name FROM sys.databases d INNER JOIN sys.availability_replicas r ON d.replica_id = r.replica_id INNER JOIN sys.availability_groups g ON g.group_id = r.group_id WHERE d.name = @dbname; SET @sqlstr = 'ALTER AVAILABILITY GROUP [' + @agname + '] REMOVE DATABASE [' + @dbname + '];'; PRINT @sqlstr; SET @agdb = 1; END /* Generate a statement to set the database offline */ SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'; PRINT @sqlstr; /* Get a list of database files for your database and insert them into the temp table for use in the loop */ SET @sqlstr = 'INSERT INTO #dbfiles SELECT name, physical_name, type FROM ' + @dbname + '.sys.database_files;' EXEC sp_executesql @sqlstr; /* Set the loop limit to the number of files returned from the insert statement */ SET @looplimit = @@ROWCOUNT; WHILE @loopcount <= @looplimit BEGIN /* Get the first set of physical and logical file name and generate an ALTER DATABASE statement using the file type to determine the new physical location */ SELECT TOP 1 @logicalname = logicalname, @physicalname = REVERSE(SUBSTRING(REVERSE(physicalname),1,(CHARINDEX('\',REVERSE(physicalname))-1))), @filetype = filetype FROM #dbfiles; IF @filetype = 0 BEGIN SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' MODIFY FILE (NAME = ' + @logicalname + ' ,FILENAME = ''' + @datapath + @physicalname + '''' + ');'; END ELSE IF @filetype = 1 BEGIN SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' MODIFY FILE (NAME = ' + @logicalname + ' ,FILENAME = ''' + @logpath + @physicalname + '''' + ');'; END PRINT @sqlstr; SET @loopcount += 1; DELETE TOP (1) FROM #dbfiles END /* Generate a statement to bring the database back online */ SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' SET ONLINE'; PRINT @sqlstr; IF @agdb = 1 BEGIN PRINT '/* Reminder! ' + @dbname + 'is part of an availability group You will need to add the database back to the availability group if necessary */' END
Four months ago, when I blogged about giving all the presentations, things were starting to ramp up pat my new job. I’ve really enjoyed the last few months, but the combination of my presenting schedule and the responsibilities of the new gig has left little time for blogging.
This week, I ran into a corruption issue that gave me a chance to make a deeper dive into internals than I have in a while. It also gave me the opportunity to develop a script to dump the results of DBCC PAGE to a table.
This week, we discovered corruption in a client’s database we were migrating. Digging deeper into the results of DBCC CHECKDB, I found that six objects in the database were corrupted badly enough to require data loss. One of those objects contained 47 irreparable pages.
In an effort to provide the client as much information as possible about the corrupted data, I set out to identify the rows contained on the corrupt pages. Here are the scripts I used.
First, I needed to create tables to hold the results of DBCC CHECKDB and DBCC PAGE. For the PageResults table, I guessed at the column definitions. For DBCCResults, I made all of the numeric columns BIGINT to prevent conversion errors.
USE [Your_Database] GO /* Create table to hold the results of DBCC CHECKDB */ /****** Object: Table [dbo].[DBCCResults] Script Date: 9/30/2014 11:00:47 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF OBJECT_ID('Your_Database..DBCCResults') IS NOT NULL BEGIN DROP TABLE DBCCResults; END IF OBJECT_ID('Your_Database..PageResults') IS NOT NULL BEGIN DROP TABLE PageResults; END CREATE TABLE PageResults (ParentObject VARCHAR(100) ,[Object] VARCHAR(1000) ,[Field] VARCHAR(100) ,[VALUE] VARCHAR(1000)) CREATE TABLE [dbo].[DBCCResults]( [Error] [bigint] NULL, [Level] [bigint] NULL, [State] [bigint] NULL, [MessageText] [varchar](7000) NULL, [RepairLevel] [varchar](7000) NULL, [Status] [bigint] NULL, [DbId] [bigint] NULL, [DbFragID] [bigint] NULL, [ObjId] [bigint] NULL, [IndId] [bigint] NULL, [PartID] [bigint] NULL, [AllocID] [bigint] NULL, [RidDbid] [bigint] NULL, [RidPruid] [bigint] NULL, [File] [bigint] NULL, [Page] [bigint] NULL, [Slot] [bigint] NULL, [RefDbid] [bigint] NULL, [RefPruId] [bigint] NULL, [RefFile] [bigint] NULL, [RefPage] [bigint] NULL, [RefSlot] [bigint] NULL, [Allocation] [bigint] NULL ) ON [PRIMARY] GO
After creating the tables, I needed to generate DBCC CHECKDB statements for each database. The script below uses sys.databases as the driver, and you can modify the results with an appropriate WHERE clause. The script is set to print the DBCC CHECKDB commands. Replacing the PRINT statement with EXEC sp_executesql will execute the statement after generation. Upon execution, the results of DBCC CHECKDB will be inserted into the DBCC CHECKDB table.
USE master; GO /* Drop temp table if it exists */ IF OBJECT_ID('tempdb..#databases') IS NOT NULL BEGIN DROP TABLE #databases END /* Declare local variables and create temp tables */ DECLARE @sqlstr NVARCHAR(2000); DECLARE @dbname SYSNAME; DECLARE @loopcount TINYINT = 1; DECLARE @looplimit TINYINT; CREATE TABLE #databases (dbname SYSNAME); /* This will generate statements for all user databases Change the WHERE clause to limit or expand the results */ INSERT INTO #databases SELECT name FROM master.sys.databases WHERE name > 4; /* Get the loop limit */ SELECT @looplimit = @@ROWCOUNT; /* Build the DBCC CHECKDB statement for each database This code will print each statement Replace the PRINT statement with EXEC sp_executesql @sql str to execute the code */ WHILE @loopcount <= @looplimit BEGIN SELECT TOP 1 @dbname = dbname FROM #databases; SELECT @sqlstr = 'INSERT INTO Your_Database..DBCCResults EXEC(''DBCC CHECKDB(' + @dbname + ') WITH TABLERESULTS, NO_INFOMSGS'')'; PRINT @sqlstr; SELECT @loopcount += 1; DELETE FROM #databases WHERE dbname = @dbname; END
Once, the DBCCResults table was populated, I needed to dump the contents of the irreparable pages. For corrupted pages, DBCC CHECKDB returns two possible RepairLevels — repair_rebuild indicates the page can be rebuilt to fix the corruption; repair_allow_data_loss means that the page is corrupted so badly it cannot be repaired. The only option is to delete the page and restore consistency to the object structure. This link from Paul Randal provides a much more detailed explanation.
USE Your_Database; /* Drop temp tables if they exist */ SET NOCOUNT ON; IF OBJECT_ID('tempdb..#DBCCPages') IS NOT NULL BEGIN DROP TABLE #DBCCPages; END IF OBJECT_ID('tempdb..#PageResults') IS NOT NULL BEGIN DROP TABLE #PageResults; END /* Create temp tables */ CREATE TABLE #PageResults (ParentObject VARCHAR(100) ,[Object] VARCHAR(1000) ,[Field] VARCHAR(100) ,[VALUE] VARCHAR(1000)) /* Declare local variables */ DECLARE @loopcount INT = 1; DECLARE @looplimit INT; DECLARE @sqlstr NVARCHAR(4000); DECLARE @pagenum BIGINT; /* Select information about the corrupt data This example selects rows with a RepairLevel of repair_allow_data_loss for a single object */ SELECT DISTINCT O.name, T.RepairLevel, T.IndId, T.PartID, T.Page INTO #DBCCPages FROM DBCCResults T INNER JOIN Your_Database.sys.objects O ON t.ObjId = O.object_id WHERE RepairLevel = 'repair_allow_data_loss' AND O.name = 'Your_Table' ORDER BY O.name, T.Page; /* Set the loop limit */ SET @looplimit = @@ROWCOUNT; /* Build a DBCC PAGE statement for each corrupt page and execute it Insert the results into the #PageResults temp table */ WHILE @loopcount <= @looplimit BEGIN SELECT TOP 1 @pagenum = Page FROM #DBCCPages SET @sqlstr = 'DBCC PAGE (Your_Database,1,' + CAST(@pagenum AS NVARCHAR) + ',3) WITH TABLERESULTS' INSERT INTO PageResults EXEC sp_executesql @sqlstr; SET @loopcount += 1; DELETE FROM #DBCCPages WHERE Page = @pagenum; END
Because I needed to provide the client with a list of rows affected by the corruption, I had to dump all pages identified as repair_allow_data_loss. The script below queries DBCC Results to return a list of PageIDs for the irreparable pages. It runs DBCC PAGE…WITH TABLERESULTS for each PageID and writes the results to the PageResults table. Here is a sample of the output:
You can see that the results contain a set of rows for each slot, or table row, contained on the page. I needed to take each set of those rows and pivot them into a single row. The script below does that. You will need to add a MIN(CASE statement for each row you want to pivot.
USE Your_Database; /* Select data from PageResults to return the key value for each row contained on a corrupt page */ SELECT ParentObject, MIN(CASE Field WHEN 'col1' THEN VALUE END) AS col1, MIN(CASE Field WHEN 'col2' THEN VALUE END) AS col2, MIN(CASE Field WHEN 'col3' THEN VALUE END) AS col3, MIN(CASE Field WHEN 'col4' THEN VALUE END) AS col4, MIN(CASE Field WHEN 'col5' THEN VALUE END) AS col5 INTO Your_Database..PageResults FROM #PageResults GROUP BY ParentObject ORDER BY col1, col2, col3, col4, col5;
I hope this post provides more insight into the tools and methods available for analyzing corruption and determining its extent.