Replication Monitor
In my last blog post, I provided a script to view replication latency. Ian Kirk took the script and ran with it, adding centralized execution and permanent logging. I’ve tweaked it a little bit further and deployed to production. So far, so good.
Here’s the latest and greatest for those interested:
IF OBJECT_ID('dbo.dba_replicationMonitor') IS Null BEGIN CREATE TABLE dbo.dba_replicationMonitor ( monitor_id INT IDENTITY(1,1) Not Null , monitorDate SMALLDATETIME Not Null , publicationName sysname Not Null , publicationDB sysname Not Null , iteration INT Null , tracer_id INT Null , distributor_latency INT Null , subscriber VARCHAR(1000) Null , subscriber_db VARCHAR(1000) Null , subscriber_latency INT Null , overall_latency INT Null ); END; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_replicationLatencyMonitor_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_replicationLatencyMonitor_sp; PRINT 'Procedure dba_replicationLatencyMonitor_sp dropped'; END; Go SET Quoted_Identifier ON Go SET ANSI_Nulls ON Go CREATE PROCEDURE dbo.dba_replicationLatencyMonitor_sp /* Declare Parameters */ @publicationToTest sysname = N'yourPublicationName' , @publicationDB sysname = N'yourPublicationDB' , @replicationDelay VARCHAR(10) = N'00:00:30' , @iterations INT = 5 , @iterationDelay VARCHAR(10) = N'00:00:30' , @displayResults BIT = 0 , @deleteTokens BIT = 1 AS /********************************************************************************* Name: dba_replicationLatencyMonitor_sp Author: Michelle F. Ufford Purpose: Retrieves the amount of replication latency in seconds Notes: Default settings will run 1 test every minute for 5 minutes. @publicationToTest = defaults to yourPublicationName publication @publicationDB = the database that is the source for the publication. The tracer procs are found in the publishing DB. @replicationDelay = how long to wait for the token to replicate; probably should not set to anything less than 10 (in seconds) @iterations = how many tokens you want to test @iterationDelay = how long to wait between sending test tokens (in seconds) @displayResults = print results to screen when complete @deleteTokens = whether you want to retain tokens when done Called by: DBA Date Initials Description ---------------------------------------------------------------------------- 2008-11-20 MFU Initial Release 2008-11-24 ILK Tweaked to allow for centralized execution Replaced temp table with permanent table. 2008-11-25 MFU More tweaking, added publication data to dba_replicationMonitor, fixed NULL latency data, moved dba_replicationMonitor creation out of proc ********************************************************************************* Exec dbo.dba_replicationLatencyMonitor_sp @publicationToTest = N'myTestPublication' , @publicationDB = N'sandbox_publisher' , @replicationDelay = N'00:00:05' , @iterations = 1 , @iterationDelay = N'00:00:05' , @displayResults = 1 , @deleteTokens = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare Variables */ DECLARE @currentIteration INT , @tokenID BIGINT , @currentDateTime SMALLDATETIME , @sqlStatement NVARCHAR(200) , @parmDefinition NVARCHAR(500); DECLARE @tokenResults TABLE ( iteration INT Null , tracer_id INT Null , distributor_latency INT Null , subscriber VARCHAR(1000) Null , subscriber_db VARCHAR(1000) Null , subscriber_latency INT Null , overall_latency INT Null ); /* Initialize our variables */ SELECT @currentIteration = 0 , @currentDateTime = GETDATE(); WHILE @currentIteration < @iterations BEGIN /* Prepare the stored procedure execution string */ SET @sqlStatement = N'Execute ' + @publicationDB + N'.sys.sp_postTracerToken ' + N'@publication = @VARpublicationToTest , ' + N'@tracer_token_id = @VARtokenID OutPut;' /* Define the parameters used by the sp_ExecuteSQL later */ SET @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARtokenID bigint OutPut'; /* Insert a new tracer token in the publication database */ EXECUTE SP_EXECUTESQL @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARtokenID = @TokenID OUTPUT; /* Give a few seconds to allow the record to reach the subscriber */ WAITFOR Delay @replicationDelay; /* Prepare our statement to retrieve tracer token data */ SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_helpTracerTokenHistory ' + N'@publication = @VARpublicationToTest , ' + N'@tracer_id = @VARtokenID' , @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARtokenID bigint'; /* Store our results for retrieval later */ INSERT INTO @tokenResults ( distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) EXECUTE SP_EXECUTESQL @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARtokenID = @TokenID; /* Assign the iteration and token id to the results for easier investigation */ UPDATE @tokenResults SET iteration = @currentIteration + 1 , tracer_id = @tokenID WHERE iteration IS Null; /* Wait for the specified time period before creating another token */ WAITFOR Delay @iterationDelay; /* Avoid endless looping... :) */ SET @currentIteration = @currentIteration + 1; END; /* Display our results */ IF @displayResults = 1 BEGIN SELECT iteration , tracer_id , IsNull(distributor_latency, 0) AS 'distributor_latency' , subscriber , subscriber_db , IsNull(subscriber_latency, 0) AS 'subscriber_latency' , IsNull(overall_latency, IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0)) AS 'overall_latency' FROM @tokenResults; END; /* Store our results */ INSERT INTO dbo.dba_replicationMonitor ( monitorDate , publicationName , publicationDB , iteration , tracer_id , distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) SELECT @currentDateTime , @publicationToTest , @publicationDB , iteration , tracer_id , IsNull(distributor_latency, 0) , subscriber , subscriber_db , IsNull(subscriber_latency, 0) , IsNull(overall_latency, IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0)) FROM @tokenResults; /* Delete the tracer tokens if requested */ IF @deleteTokens = 1 BEGIN SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_deleteTracerTokenHistory ' + N'@publication = @VARpublicationToTest , ' + N'@cutoff_date = @VARcurrentDateTime' , @parmDefinition = N'@VARpublicationToTest sysname, ' + N'@VARcurrentDateTime datetime'; EXECUTE SP_EXECUTESQL @sqlStatement , @parmDefinition , @VARpublicationToTest = @publicationToTest , @VARcurrentDateTime = @currentDateTime; END; SET NOCOUNT OFF; RETURN 0; END Go SET Quoted_Identifier OFF; Go SET ANSI_Nulls ON; Go
Note: All of my stored procedures have standardized error handling that I remove before posting to avoid confusion; you may want to implement your own error handling.
Checking Replication Latency with T-SQL
This post may only appeal to small subset of DBA’s. 
This particular script was born of a need to view replication latency in production. I had to investigate whether the replication latency issues we were experiencing in production were occurring on the publisher (us) or the subscriber (them), and address accordingly. “Why just not use Replication Monitor?”, you might ask. Good question! I would definitely use Replication Monitor if it were available to me; however, I do not have the necessary permissions to the Distributor. 
Fortunately, SQL 2005 provides us with some tools to help: sp_postTracerToken and sp_helpTracerTokenHistory. This allows us to access the same Tracer Tokens feature that is in Replication Monitor.
So that’s the backstory, and here’s the script I came up with. For those interested, it’d be pretty easy to modify this script to use a permanent table and regularly log replication latency.
CREATE PROCEDURE dbo.dba_replicationLatencyGet_sp /* Declare Parameters */ @publicationToTest sysname = N'yourPublicationName' , @replicationDelay VARCHAR(10) = N'00:00:30' , @iterations INT = 5 , @iterationDelay VARCHAR(10) = N'00:00:30' , @deleteTokens BIT = 1 , @deleteTempTable BIT = 1 AS /********************************************************************************* Name: dba_replicationLatencyGet_sp Author: Michelle F. Ufford Purpose: Retrieves the amount of replication latency in seconds Notes: Default settings will run 1 test every minute for 5 minutes. @publicationToTest = change the default to your publication @replicationDelay = how long to wait for the token to replicate; probably should not set to anything less than 10 (in seconds) @iterations = how many tokens you want to test @iterationDelay = how long to wait between sending test tokens (in seconds) @deleteTokens = whether you want to retain tokens when done @deleteTempTable = whether or not to retain the temporary table when done. Data stored to ##tokenResults; set @deleteTempTable flag to 0 if you do not want to delete when done. Called by: DBA Date Initials Description ---------------------------------------------------------------------------- 2008-11-20 MFU Initial Release ********************************************************************************* Exec dbo.dba_replicationLatencyGet_sp @publicationToTest = N'yourPublicationName' , @replicationDelay = N'00:00:05' , @iterations = 1 , @iterationDelay = N'00:00:05' , @deleteTokens = 1 , @deleteTempTable = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare Variables */ DECLARE @currentIteration INT , @tokenID BIGINT , @currentDateTime SMALLDATETIME; IF OBJECT_ID('tempdb.dbo.##tokenResults') IS Null BEGIN CREATE TABLE ##tokenResults ( iteration INT Null , tracer_id INT Null , distributor_latency INT Null , subscriber VARCHAR(1000) Null , subscriber_db VARCHAR(1000) Null , subscriber_latency INT Null , overall_latency INT Null ); END; /* Initialize our variables */ SELECT @currentIteration = 0 , @currentDateTime = GETDATE(); WHILE @currentIteration < @iterations BEGIN /* Insert a new tracer token in the publication database */ EXECUTE sys.sp_postTracerToken @publication = @publicationToTest, @tracer_token_id = @tokenID OUTPUT; /* Give a few seconds to allow the record to reach the subscriber */ WAITFOR Delay @replicationDelay; /* Store our results in a temp table for retrieval later */ INSERT INTO ##tokenResults ( distributor_latency , subscriber , subscriber_db , subscriber_latency , overall_latency ) EXECUTE sys.sp_helpTracerTokenHistory @publicationToTest, @tokenID; /* Assign the iteration and token id to the results for easier investigation */ UPDATE ##tokenResults SET iteration = @currentIteration + 1 , tracer_id = @tokenID WHERE iteration IS Null; /* Wait for the specified time period before creating another token */ WAITFOR Delay @iterationDelay; /* Avoid endless looping... :) */ SET @currentIteration = @currentIteration + 1; END; SELECT * FROM ##tokenResults; IF @deleteTempTable = 1 BEGIN DROP TABLE ##tokenResults; END; IF @deleteTokens = 1 BEGIN EXECUTE sp_deleteTracerTokenHistory @publication = @publicationToTest, @cutoff_date = @currentDateTime; END; SET NOCOUNT OFF; RETURN 0; END Go
Bulk Inserts with XML
Filed under: Performance & Tuning, SQL Tips, T-SQL Scripts
Last week, I blogged about how to perform one-to-many inserts with table-valued parameters, a feature new in 2008. For those who do not yet have 2008 or will not have it in the near future, it may still be beneficial to use XML for bulk inserts.
Here’s a pretty simple example of how to accomplish this:
/* Create some tables to work with */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate DATETIME Not Null , customer_id INT Not Null CONSTRAINT PK_orders PRIMARY KEY CLUSTERED(order_id) ); CREATE TABLE dbo.orderDetails ( orderDetail_id INT IDENTITY(1,1) Not Null , order_id INT Not Null , lineItem INT Not Null , product_id INT Not Null CONSTRAINT PK_orderDetails PRIMARY KEY CLUSTERED(orderDetail_id) CONSTRAINT FK_orderDetails_orderID FOREIGN KEY(order_id) REFERENCES dbo.orders(order_id) ); Go /* Create a new procedure using an XML parameter */ CREATE PROCEDURE dbo.insert_orderXML_sp @orderDate DATETIME , @customer_id INT , @orderDetailsXML XML AS BEGIN SET NOCOUNT ON; DECLARE @myOrderID INT; INSERT INTO dbo.orders ( orderDate , customer_id ) VALUES ( @orderDate , @customer_id ); SET @myOrderID = SCOPE_IDENTITY(); INSERT INTO dbo.orderDetails ( order_id , lineItem , product_id ) SELECT @myOrderID , myXML.VALUE('./@lineItem', 'int') , myXML.VALUE('./@product_id', 'int') FROM @orderDetailsXML.nodes('/orderDetail') AS nodes(myXML); SET NOCOUNT OFF; END GO /* Call our stored procedure */ EXECUTE dbo.insert_orderXML_sp @orderDate = '2008-01-01' , @customer_id = 101 , @orderDetailsXML = '<orderDetail lineItem="1" product_id="123" /> <orderDetail lineItem="2" product_id="456" /> <orderDetail lineItem="3" product_id="789" /> <orderDetail lineItem="4" product_id="246" /> <orderDetail lineItem="5" product_id="135" />'; /* Check our data */ SELECT * FROM dbo.orders; SELECT * FROM dbo.orderDetails; /* Clean up our mess */ DROP PROCEDURE insert_orderXML_sp; DROP TABLE dbo.orderDetails; DROP TABLE dbo.orders;
I’ve found that this is more efficient when performing large parent/child inserts, i.e. 1 parent record to 100 child records. Keep in mind that there’s a point where doing an insert with XML is more expensive than using a traditional INSERT stored procedure. I haven’t run any tests yet to help define what that threshold is… more to come on this in the near future.
More on the Nodes() Method can be found here in Books Online: http://msdn.microsoft.com/en-us/library/ms188282(SQL.90).aspx
Update: I’ve just learned that the “value” keyword is case-sensitive. Apparently my code box plug-in was defaulting “value” to “VALUE.”
Here’s the error message you’ll get if you don’t have “value” in lower-case:
Cannot find either column “myXML” or the user-defined function or aggregate “myXML.VALUE”, or the name is ambiguous.
Updated Index Defrag Script (2005, 2008)
Filed under: Performance & Tuning, SQL 2008, T-SQL Scripts
Thanks to everyone who left a comment or sent me an e-mail regarding the Index Defrag Script. I’ve received some great feedback and requests for features. I’ve also had some questions regarding how to use it, which I will answer at the end of this post.
Changes include:
– separate version for both Enterprise and Standard editions
- Standard edition removes partitioning and online options
– output option to see fragmentation levels
– page_count added to the log table
I’ve also verified that this script works well in SQL 2008.
Enterprise Version:
IF EXISTS(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_indexDefragLog') BEGIN DROP TABLE dbo.dba_indexDefragLog; PRINT 'dba_indexDefragLog table dropped!'; END CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) NOT NULL , objectID INT NOT NULL , objectName NVARCHAR(130) NOT NULL , indexID INT NOT NULL , indexName NVARCHAR(130) NOT NULL , partitionNumber SMALLINT not null , fragmentation FLOAT NOT NULL , page_count INT NOT NULL , dateTimeStart DATETIME NOT NULL , durationSeconds INT NOT NULL CONSTRAINT PK_indexDefragLog PRIMARY KEY CLUSTERED (indexDefrag_id) ); PRINT 'dba_indexDefragLog Table Created'; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') IS Null BEGIN EXECUTE ('Create Procedure dbo.dba_indexDefrag_sp As Print ''Hello World!'''); RAISERROR('Procedure dba_indexDefrag_sp created.' , 10, 1); END; Go SET ANSI_Nulls ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET NOCOUNT ON; SET Numeric_RoundAbort OFF; SET Quoted_Identifier ON; Go ALTER PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 10.0 /* in percent, will not defrag if fragmentation less than specified */ , @rebuildThreshold FLOAT = 30.0 /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild */ , @executeSQL BIT = 1 /* 1 = execute; 0 = print command only */ , @tableName VARCHAR(4000) = Null /* Option to specify a table name */ , @printCommands BIT = 0 /* 1 = print commands; 0 = do not print commands */ , @printFragmentation BIT = 0 /* 1 = print fragmentation prior to defrag; 0 = do not print */ , @defragDelay CHAR(8) = '00:00:05' /* time to wait between defrag commands */ AS /******************************************************************** Name: dba_indexDefrag_sp Author: Michelle F. Ufford Purpose: Defrags all indexes for the current database Notes: This script was designed for SQL Server 2005 Enterprise Edition. CAUTION: Monitor transaction log if executing for the first time! @minFragmentation defaulted to 10%, will not defrag if fragmentation if less than specified. @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; > than 30% will result in rebuild instead @onlineRebuild 1 = online rebuild; 0 = offline rebuild @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @tableName Specify if you only want to defrag indexes for a specific table @printCommands 1 = print commands to screen; 0 = do not print commands @printFragmentation 1 = print fragmentation to screen; 0 = do not print fragmentation @defragDelay time to wait between defrag commands; gives the server some time to catch up Called by: SQL Agent Job or DBA Date Initials Description ---------------------------------------------------------------- 2008-10-27 MFU Initial Release 2008-11-17 MFU Added page_count to log table , added @printFragmentation option ******************************************************************** Exec dbo.dba_indexDefrag_sp @executeSQL = 1 , @printCommands = 1 , @minFragmentation = 0 , @printFragmentation = 1; ********************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare our variables */ DECLARE @objectID INT , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(130) , @objectName NVARCHAR(130) , @indexName NVARCHAR(130) , @partitionNumber SMALLINT , @partitions SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT; /* Just a little validation... */ IF @minFragmentation Not Between 0.00 And 100.0 SET @minFragmentation = 10.0; IF @rebuildThreshold Not Between 0.00 And 100.0 SET @rebuildThreshold = 30.0; IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]' SET @defragDelay = '00:00:05'; /* Determine which indexes to defrag using our user-defined parameters */ SELECT OBJECT_ID AS objectID , index_id AS indexID , partition_number AS partitionNumber , avg_fragmentation_in_percent AS fragmentation , page_count , 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */ INTO #indexDefragList FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited') WHERE avg_fragmentation_in_percent > @minFragmentation And index_id > 0 OPTION (MaxDop 1); /* Create a clustered index to boost performance a little */ CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(objectID, indexID, partitionNumber); /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @fragmentation = fragmentation , @indexID = indexID , @partitionNumber = partitionNumber , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; /* Look up index information */ SELECT @objectName = QUOTENAME(o.name) , @schemaName = QUOTENAME(s.name) FROM sys.objects AS o INNER Join sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.OBJECT_ID = @objectID; SELECT @indexName = QUOTENAME(name) FROM sys.indexes WHERE OBJECT_ID = @objectID And index_id = @indexID And type > 0; /* Determine if the index is partitioned */ SELECT @partitionCount = COUNT(*) FROM sys.partitions WHERE OBJECT_ID = @objectID And index_id = @indexID; /* Look for LOBs */ SELECT TOP 1 @containsLOB = column_id FROM sys.columns WITH (NOLOCK) WHERE [OBJECT_ID] = @objectID And (system_type_id In (34, 35, 99) -- 34 = image, 35 = text, 99 = ntext Or max_length = -1); -- varbinary(max), varchar(max), nvarchar(max), xml /* See if we should rebuild or reorganize; handle thusly */ IF @fragmentation < @rebuildThreshold And @partitionCount <= 1 SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + N' ReOrganize'; IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) = 0 -- Cannot rebuild if the table has one or more LOB And @partitionCount <= 1 BEGIN /* We should always rebuild online if possible (SQL 2005 Enterprise) */ IF @onlineRebuild = 0 SET @rebuildCommand = N' Rebuild With (Online = Off, MaxDop = 1)'; ELSE SET @rebuildCommand = N' Rebuild With (Online = On, MaxDop = 1)'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + @rebuildCommand; END; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + N' ReOrganize' + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); -- no MaxDop needed, single threaded operation /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); EXECUTE (@sqlCommand); SET @dateTimeEnd = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , durationSeconds ) SELECT @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @dateTimeStart , DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd); /* Just a little breather for the server */ WAITFOR Delay @defragDelay; /* Print if specified to do so */ IF @printCommands = 1 PRINT N'Executed: ' + @sqlCommand; END ELSE /* Looks like we're not executing, just print the commands */ BEGIN IF @printCommands = 1 PRINT @sqlCommand; END /* Update our index defrag list when we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 SELECT idl.objectID , o.name AS 'tableName' , idl.indexID , i.name AS 'indexName' , idl.fragmentation , idl.page_count FROM #indexDefragList AS idl Join sys.objects AS o ON idl.objectID = o.OBJECT_ID Join sys.indexes AS i ON idl.objectID = i.OBJECT_ID And idl.indexID = i.index_id; /* When everything is done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; SET NOCOUNT OFF; RETURN 0 END Go
Standard Version:
IF EXISTS(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_indexDefragLog') BEGIN DROP TABLE dbo.dba_indexDefragLog; PRINT 'dba_indexDefragLog table dropped!'; END CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) NOT NULL , objectID INT NOT NULL , objectName NVARCHAR(130) NOT NULL , indexID INT NOT NULL , indexName NVARCHAR(130) NOT NULL , fragmentation FLOAT NOT NULL , page_count INT NOT NULL , dateTimeStart DATETIME NOT NULL , durationSeconds INT NOT NULL CONSTRAINT PK_indexDefragLog PRIMARY KEY CLUSTERED (indexDefrag_id) ); PRINT 'dba_indexDefragLog Table Created'; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'), N'IsProcedure') IS Null BEGIN EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp As Print ''Hello World!'''); RAISERROR('Procedure dba_indexDefragStandard_sp created.' , 10, 1); END; Go SET ANSI_Nulls ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET NOCOUNT ON; SET Numeric_RoundAbort OFF; SET Quoted_Identifier ON; Go ALTER PROCEDURE dbo.dba_indexDefragStandard_sp /* Declare Parameters */ @minFragmentation FLOAT = 10.0 /* in percent, will not defrag if fragmentation less than specified */ , @rebuildThreshold FLOAT = 30.0 /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */ , @executeSQL BIT = 1 /* 1 = execute; 0 = print command only */ , @tableName VARCHAR(4000) = Null /* Option to specify a table name */ , @printCommands BIT = 0 /* 1 = print commands; 0 = do not print commands */ , @printFragmentation BIT = 0 /* 1 = print fragmentation prior to defrag; 0 = do not print */ , @defragDelay CHAR(8) = '00:00:05' /* time to wait between defrag commands */ AS /******************************************************************** Name: dba_indexDefragStandard_sp Author: Michelle F. Ufford Purpose: Defrags all indexes for the current database Notes: This script was designed for SQL Server 2005 Standard edition. CAUTION: Monitor transaction log if executing for the first time! @minFragmentation defaulted to 10%, will not defrag if fragmentation if less than specified. @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; > than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @tableName Specify if you only want to defrag indexes for a specific table @printCommands 1 = print commands to screen; 0 = do not print commands @printFragmentation 1 = print fragmentation to screen; 0 = do not print fragmentation @defragDelay time to wait between defrag commands; gives the server some time to catch up Called by: SQL Agent Job or DBA Date Initials Description ---------------------------------------------------------------- 2008-10-27 MFU Initial Release 2008-11-17 MFU Added page_count to log table , added @printFragmentation option ******************************************************************** Exec dbo.dba_indexDefragStandard_sp @executeSQL = 1 , @printCommands = 1 , @minFragmentation = 0 , @printFragmentation = 1; ********************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare our variables */ DECLARE @objectID INT , @indexID INT , @schemaName NVARCHAR(130) , @objectName NVARCHAR(130) , @indexName NVARCHAR(130) , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT; /* Just a little validation... */ IF @minFragmentation Not Between 0.00 And 100.0 SET @minFragmentation = 10.0; IF @rebuildThreshold Not Between 0.00 And 100.0 SET @rebuildThreshold = 30.0; IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]' SET @defragDelay = '00:00:05'; /* Determine which indexes to defrag using our user-defined parameters */ SELECT OBJECT_ID AS objectID , index_id AS indexID , avg_fragmentation_in_percent AS fragmentation , page_count , 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */ INTO #indexDefragList FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited') WHERE avg_fragmentation_in_percent > @minFragmentation And index_id > 0 OPTION (MaxDop 1); /* Create a clustered index to boost performance a little */ CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(objectID, indexID); /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @fragmentation = fragmentation , @indexID = indexID , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; /* Look up index information */ SELECT @objectName = QUOTENAME(o.name) , @schemaName = QUOTENAME(s.name) FROM sys.objects AS o INNER Join sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.OBJECT_ID = @objectID; SELECT @indexName = QUOTENAME(name) FROM sys.indexes WHERE OBJECT_ID = @objectID And index_id = @indexID And type > 0; /* Look for LOBs */ SELECT TOP 1 @containsLOB = column_id FROM sys.columns WITH (NOLOCK) WHERE [OBJECT_ID] = @objectID And (system_type_id In (34, 35, 99) -- 34 = image, 35 = text, 99 = ntext Or max_length = -1); -- varbinary(max), varchar(max), nvarchar(max), xml /* See if we should rebuild or reorganize; handle thusly */ IF @fragmentation < @rebuildThreshold Or IsNull(@containsLOB, 0) > 0 -- Cannot rebuild if the table has one or more LOB SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + N' ReOrganize;' ELSE SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + ' Rebuild ' + 'With (MaxDop = 1)'; -- minimize impact on server /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); EXECUTE (@sqlCommand); SET @dateTimeEnd = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( objectID , objectName , indexID , indexName , fragmentation , page_count , dateTimeStart , durationSeconds ) SELECT @objectID , @objectName , @indexID , @indexName , @fragmentation , @pageCount , @dateTimeStart , DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd); /* Just a little breather for the server */ WAITFOR Delay @defragDelay; /* Print if specified to do so */ IF @printCommands = 1 PRINT N'Executed: ' + @sqlCommand; END ELSE /* Looks like we're not executing, just print the commands */ BEGIN IF @printCommands = 1 PRINT @sqlCommand; END /* Update our index defrag list when we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE objectID = @objectID And indexID = @indexID; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 SELECT idl.objectID , o.name AS 'tableName' , idl.indexID , i.name AS 'indexName' , idl.fragmentation , idl.page_count FROM #indexDefragList AS idl JOIN sys.objects AS o ON idl.objectID = o.OBJECT_ID JOIN sys.indexes AS i ON idl.objectID = i.OBJECT_ID AND idl.indexID = i.index_id; /* When everything is done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; SET NOCOUNT OFF; RETURN 0 END Go
For those who are having troubles with this script…
1) “Not all of my indexes were defragged!” or “Nothing happened when I executed this script.”
This script will only defrag those indexes that surpass the specified threshold. If you’re not seeing your index in the output, try executing this:
EXEC dbo.dba_indexDefrag_sp @executeSQL = 0 , @printCommands = 1 , @minFragmentation = 0 , @printFragmentation = 1;
Check to see what your index’s fragmentation level is. Maybe it’s not as fragmented as you feared.
2) “My indexes are still fragmented after running this script.”
To quote The Powers That Be (aka Microsoft)…
“In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.” — Reorganizing and Rebuilding Indexes
3) “Can I use this in my production environment?”
That really depends on your environment. I’ve successfully used this in some very large production environments. However, I wouldn’t exactly recommend executing the script in the middle of a business day on a billion+ row, heavily fragmented, unpartitioned table, either.
If you’re not sure what the impact will be, execute the commands-only version of the script…
EXEC dbo.dba_indexDefrag_sp @executeSQL = 0 , @printCommands = 1 , @printFragmentation = 1;
… then execute the statements one at a time. Make sure you monitor tempdb and the transaction log to ensure you don’t have any space issues.
If you have any additional questions or suggestions for this script, leave me a comment and I’ll be happy to help.
Performance Comparison of Singleton, XML, and TVP Inserts
Filed under: Performance & Tuning, SQL 2008, SQL Tips
As promised, today I took a look at the performance of bulk inserts using XML and Table-Valued Parameters. I also compared it against singleton inserts to show the value in the bulk-insert approach.
My tests were pretty simple: insert 100 records using each method. Each test was executed 10 times to ensure consistency. The duration was recorded in microseconds.
The goal was to compare the performance of the inserts. Because I was executing this entire test within SQL Server, I had to isolate only the actual insert transactions and ignore everything else, such as the loading of the data; that work would normally be performed by the calling application.
So without further ado… screenshots of the Profiler traces: (click to enlarge)
Single Insert Method
XML Method
Table-Valued Parameter Method
Summary
| Method | Avg CPU | Avg Reads | Avg Writes | Avg Duration (micro) |
| Singleton Method | 3 | 202 | 0 | 13378 |
| XML Method | 0 | 222 | 0 | 3124 |
| TVP Method | 1 | 207 | 0 | 780 |
As expected, both the XML and the TVP method performed significantly better than the single-insert method. As hoped, the table-valued parameter arguably performed the best of all 3.
One-to-Many Inserts with Table-Valued Parameters
There’s been much talk about table-valued parameters, but I’ve yet to see an example illustrating one of the greatest potential benefits of this new feature (at least, imho): one-to-many inserts.
But first, for those not yet up to speed on this new feature…
What are Table-Valued Parameters?
According to SQL Books Online 2008:
Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
That’s great, but why do I care?
This means that a single proc call with table-valued parameters can insert numerous records into one or more tables; for example, inserting a parent record into TableA (header) and several related child records into TableB (details). This has great potential when used with .NET, i.e. a web service that sends sales data to SQL Server.
In 2005, this can be accomplished by using XML and temp tables. My hope is this new feature will outperform the XML method. In my next post, I’ll follow up with a performance comparison to see if table-valued parameters live up to the hype.
In the mean-time, let’s take a look at how we would actually execute this in 2008.
NOTE: This is pseudo-code, so for clarity’s sake, essentials like error handling are absent.
/* Create some tables to work with */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate DATE Not Null , customer_id INT Not Null CONSTRAINT PK_orders PRIMARY KEY CLUSTERED(order_id) ); CREATE TABLE dbo.orderDetails ( orderDetail_id INT IDENTITY(1,1) Not Null , order_id INT Not Null , lineItem INT Not Null , product_id INT Not Null CONSTRAINT PK_orderDetails PRIMARY KEY CLUSTERED(orderDetail_id) CONSTRAINT FK_orderDetails_orderID FOREIGN KEY(order_id) REFERENCES dbo.orders(order_id) ); /* Create our new table types */ CREATE TYPE dbo.orderTable AS TABLE ( orderDate DATE , customer_id INT ); GO CREATE TYPE dbo.orderDetailTable AS TABLE ( lineItem INT , product_id INT ); GO /* Let's check out our new data types */ SELECT * FROM sys.types WHERE [name] IN ('orderTable', 'orderDetailTable'); GO /* Create a new procedure using a table-valued parameter */ CREATE PROCEDURE dbo.insert_orderTVP_sp @myOrderTable orderTable READONLY , @myOrderDetailTable orderDetailTable READONLY AS BEGIN SET NOCOUNT ON; DECLARE @myOrderID INT; INSERT INTO dbo.orders SELECT orderDate , customer_id FROM @myOrderTable; SET @myOrderID = SCOPE_IDENTITY(); INSERT INTO dbo.orderDetails SELECT @myOrderID , lineItem , product_id FROM @myOrderDetailTable; SET NOCOUNT OFF; END GO /* Call our new proc! */ DECLARE @myTableHeaderData AS orderTable , @myTableDetailData AS orderDetailTable; INSERT INTO @myTableHeaderData (orderDate, customer_id) SELECT GETDATE(), 101; INSERT INTO @myTableDetailData (lineItem, product_id) SELECT 10, 123 UNION ALL SELECT 20, 456 UNION ALL SELECT 30, 789; EXECUTE dbo.insert_orderTVP_sp @myTableHeaderData , @myTableDetailData; /* Check our data */ SELECT * FROM dbo.orders; SELECT * FROM dbo.orderDetails; /* Clean up our mess */ DROP PROCEDURE insert_orderTVP_sp; DROP TABLE dbo.orderDetails; DROP TABLE dbo.orders; DROP TYPE orderTable; DROP TYPE orderDetailTable;
Large Updates on Replicated Tables
Late last night, I executed a 70mm update on a replicated table. This was a somewhat low priority update, so the primary goal (aside from the data change) was to avoid any issues on the publishing and subscribing servers, including replication latency errors. I have performed many large updates in the past, but this was the first on a replicated table.
To minimize impact, one of our system DBA’s suggested the use of a replicated stored procedure. Basically, instead of replicating each of the 70mm updates as a singleton transaction, the replicated stored procedure is called on the subscribing server, which then performs the bulk update locally. This was my first time using it and it worked beautifully.
Another of my colleagues, Jeff M., suggested the use of a control table. Normally, I would just output the last affected ID and update the script manually. However, this also worked so well that it will be adopted in all future update scripts.
Using the following pseudo-script and a replicated stored procedure, I was able to execute the update on 70mm records in 11.5 hours with *zero* impact on the servers and replication. I could’ve probably increased the batch size and reduced the execution time even further, but as I mentioned, this was a low priority update, so there was no need to push it.
This process should work equally well on non-replicated updates; merely replace the Execute statement with the actual update.
/************ Chunked Update Script with Control Table ************/ /* --------------------- Preparation Script --------------------- */ /* Note: Execute this section in a separate query window */ USE yourDatabase; Go SET NOCOUNT ON; /* Create a control table to facilitate tweaking of parameters */ CREATE TABLE dbo.scratch_largeUpdate_control ( sizeOfBatch INT , waitForDelay CHAR(8) , minRecordToUpdate INT , maxRecordToUpdate INT ); /* Create your control data; you only want 1 row in this table */ INSERT INTO dbo.scratch_largeUpdate_control (sizeOfBatch, waitForDelay, minRecordToUpdate, maxRecordToUpdate) SELECT 10000, '00:00:05', 40297132, 107459380; /* Update Script */ UPDATE dbo.scratch_largeUpdate_control SET sizeOfBatch = 100000 , waitForDelay = '00:00:30'; /* ------------------------ Update Script ------------------------ */ USE yourDatabase; Go SET NOCOUNT ON; DECLARE @batchSize INT , @minID INT , @maxID INT , @procMinID INT , @procMaxID INT , @delay CHAR(8) , @statusMsg VARCHAR(1000); BEGIN Try IF @@SERVERNAME Not In ('PRODSERVER') RAISERROR('Sorry, this cannot be executed here!', 16, 1); IF Not Exists( SELECT OBJECT_ID FROM sys.objects WHERE [name] = 'scratch_largeUpdate_control' And type = 'U' ) RAISERROR ('ERROR: Control table does not exist!', 16, 1) WITH NoWait; ELSE SELECT @minID = minRecordToUpdate , @maxID = maxRecordToUpdate , @batchSize = sizeOfBatch , @delay = waitForDelay FROM dbo.scratch_largeUpdate_control WITH (NoLock); SET @statusMsg = 'Beginning update; batch size of ' + CAST(@batchSize AS VARCHAR(10)) + ', delay of ' + @delay + ' defined. Estimate ' + CAST((((@maxID - @minID) / @batchSize) + 1) AS VARCHAR(10)) + ' iterations to be performed.' RAISERROR (@statusMsg, 10, 1) WITH NoWait; WHILE @minID < @maxID BEGIN SELECT @procMinID = @minID , @procMaxID = (@minID + (@batchSize - 1)); /* Execute actual update code here OR Call a replicated stored procedure, i.e. */ EXECUTE dbo.myReplicatedUpdateProc @minRecordID = @procMinID , @maxRecordID = @procMaxID; SET @statusMsg = 'Updating records ' + CAST(@minID AS VARCHAR(10)) + ' through ' + CAST((@minID + (@batchSize - 1)) AS VARCHAR(10)) + '...'; RAISERROR (@statusMsg, 10, 1) WITH NoWait; /* Update our control table with the last successfully updated record ID. In the event of an error, we can start from here. */ UPDATE dbo.scratch_largeUpdate_control SET minRecordToUpdate = @minID + @batchSize; SELECT @minID = @minID + @batchSize; WAITFOR Delay @delay; -- breather for the server /* Check to see if our control values have changed */ IF Not Exists( SELECT * FROM dbo.scratch_largeUpdate_control WITH (NoLock) WHERE @batchSize = sizeOfBatch And @delay = waitForDelay) BEGIN /* There was a change, so grab our new values */ SELECT @batchSize = sizeOfBatch , @delay = waitForDelay FROM dbo.scratch_largeUpdate_control WITH (NoLock) /* Print a status message with the new values */ SET @statusMsg = 'Parameters changed: batch size = ' + CAST(@batchSize AS VARCHAR(10)) + ', delay = ' + @delay; RAISERROR (@statusMsg, 10, 1) WITH NoWait; END END RAISERROR ('Success!', 10, 1) WITH NoWait; END Try /* Handle your errors */ BEGIN Catch SET @statusMsg = 'An error has occurred and the last ' + 'transaction has been rolled back. ' + 'Last record successfully updated was ' + 'record_id = ' + CAST((@minID + (@batchSize - 1)) AS VARCHAR(10)); RAISERROR (@statusMsg, 16, 1) WITH NoWait; /* Return the error message */ SELECT Error_Number() , Error_Procedure() , DB_NAME() , Error_Line() , Error_Message() , Error_Severity() , Error_State(); END Catch; /* -------------------------- Clean-Up ---------------------------- Drop Table dbo.scratch_largeUpdate_control; ----------------------------------------------------------------- */
SQL Quiz – DBA Mistakes
Yesterday, Jason Massie called me out to answer the SQL Quiz begun by Chris Shaw. The goal is to share a mistake you’ve made and what you’ve learned from it; hopefully, others will learn vicariously through you and avoid the same mistakes.
I didn’t have to think too hard about this one. I’ve made some small mistakes in my time as DBA, but only one really big one. You may have noticed my fondness for partitioning. My first partitioning project was to transition some very large existing tables to a new partitioning scheme. The plan was to stage the new partitioned schema and the newly partitioned data in the staging database, then perform a database rename. To make things easier on myself, the script I was using in DEV and TEST first dropped the tables and then rebuilt them. (I’m sure by now you know where this is going…)
One morning, while still in the midst of development, I sat down at my desk, opened up the script, and pressed F5. My first clue that I was in the wrong environment was the errors about dropping replicated tables.
Ultimately, the failure to drop replicated tables was what prevented this from being a total disaster. Nonetheless, several small look-up tables and one really large table (>1bil rows) were gone, all in the matter of 2 seconds.
What did I learn?
1) Never skip my morning coffee. Never.
2) I now always prefix my DEV and TEST scripts with something like this:
[cc lang="tsql"]
Use databaseName;
Go
If @@ServerName Not In (‘DEVSERVER’, ‘TESTSERVER’)
RaisError(‘Sorry, this cannot be executed here!’, 15, 1);
Else
/* Execute Code */
[/cc]
I’m calling on Jeff Belina, SQL Developer Extraordinaire. He doesn’t have a blog but that doesn’t mean he can’t post. 
P.S. Thanks for the plug, Jason!
Row Concatenation in T-SQL
Filed under: Performance & Tuning, SQL Tips, T-SQL Scripts
Have you ever needed to create a comma-delimited list of related records, grouped by a parent record? This is one of those business requirements that just happens to pop up every so often. For example, turning this:
| AuthorID | BookName |
| 1 | “Great Expectations” |
| 1 | “David Copperfield” |
| 1 | “Bleak House” |
| 2 | “Divine Comedy” |
| 3 | “Pride and Prejudice” |
| 3 | “Mansfield Park” |
into this:
| AuthorID | ListOfBooks |
| 1 | “Great Expectations”, “David Copperfield”, “Bleak House” |
| 2 | “Divine Comedy” |
| 3 | “Pride and Prejudice”, “Mansfield Park” |
There’s a handful of ways you can handle this, especially when dealing with small data sets. However, the problem becomes a bit more tricky when dealing with large record sets (i.e. hundreds of thousands or even millions of records). My first attempt at a solution used a CTE (common table expression). It did the job but was very slow. Looking for a better solution, I discovered the XML method.
To give you a quick example:
/* Create a table variable to play with */ DECLARE @myTable TABLE (customerID INT, textData VARCHAR(10)); /* Populate some test rescords */ INSERT INTO @myTable SELECT 1, 'abc' UNION All SELECT 1, 'def' UNION All SELECT 2, 'uvw' UNION All SELECT 2, 'xyz' /* Just take a look at the data before we format it */ SELECT * FROM @myTable; /* Let's take a look at what For XML Raw will return for us */ SELECT textData FROM @myTable WHERE customerID = 1 ORDER BY textData FOR XML Raw; /* Now consolidate the data, using the For XML Raw option to concatenate the textData column */ SELECT customerID , REPLACE( REPLACE( REPLACE( ( SELECT textData FROM @myTable AS a WHERE a.customerID = b.customerID ORDER BY textData FOR XML Raw) , '"/><row textData="', ', ') , '<row textData="', '') , '"/>', '') AS 'textData' FROM @myTable b GROUP BY customerID;
This has become my default method for handling this report requirement. While discussing this with a colleague, he mentioned using an approach that was similar in design but used Cross Apply on the XML. Wanting to see which performed better, I ran the two following queries in the AdventureWorks sample database (2008):
/* Method 1 */ SELECT ProductsOrdered , COUNT(*) AS 'salesOrders' FROM ( SELECT SalesOrderID , REPLACE( REPLACE( REPLACE( ( SELECT TOP 10 ProductID FROM Sales.SalesOrderDetail AS sod WITH (NoLock) WHERE soh.SalesOrderID = sod.SalesOrderID ORDER BY ProductID FOR XML Raw) , '"/><row ProductID="', ', ') , '<row ProductID="', '') , '"/>', '') AS 'ProductsOrdered' FROM Sales.SalesOrderHeader AS soh WITH (NoLock) ) x GROUP BY ProductsOrdered ORDER BY COUNT(*) DESC OPTION (MaxDop 1); /* Method 2 */ SELECT ProductsOrdered , COUNT(*) AS 'salesOrders' FROM ( SELECT SalesOrderID , SUBSTRING(ProductsOrdered, 1, LEN(ProductsOrdered) - 1) AS 'ProductsOrdered' FROM Sales.SalesOrderHeader AS soh WITH (NoLock) Cross Apply ( SELECT TOP 10 CAST(ProductID AS VARCHAR(10)) + ', ' FROM Sales.SalesOrderDetail AS sod WITH (NoLock) WHERE sod.SalesOrderID = soh.SalesOrderID ORDER BY ProductID FOR XML PATH('') ) X(ProductsOrdered) ) x GROUP BY ProductsOrdered ORDER BY COUNT(*) DESC;
Here’s the results: (click to enlarge)
Row Concatenation Comparison
As evidenced in the image above, the first method has slightly higher CPU and double the duration. The 2nd method had almost double the writes and significantly more reads.
I was hoping for a clear winner, but apparently each method has its benefits. I’ll probably continue to stick with my original, more resource-friendly method (Method 1), unless someone suggests a better solution.
Max INT Identity Value Reached (DBCC CheckIdent)
One of my colleagues shared the following experience.
Recently, the identity column on one of his tables reached the maximum value allowable for an INT data type (2,147,483,647). Obviously, this caused all sorts of errors, as the application was unable to insert records into the table. This is a customer-facing table, so the issue needed to be resolved quickly. Converting the column to a BIGINT would require a significant amount of down-time. The solution? Reseed the identity column to -2,147,483,648 and have the values count upward toward zero. This took only seconds and the app was back up immediately. This is, of course, a temporary solution; it allows the team to schedule a more convenient time to resolve the issue.
Here’s how to do this:
DBCC CHECKIDENT ('tableName', RESEED, -2147483648);
Thanks, Peter, for sharing this tip!
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
Around the Web
SQLRockstar Blog Ranking
