Find Recently Executed Stored Procedures
This past weekend, we had an issue where replication fell far behind on one of our databases. The replicated database is used for all sorts of reporting, so the immediate need was to identify processes that may have been affected by the incomplete data.
Now, there’s hundreds of stored procedures that reference the affected database; the trick is finding out which ones are relevant. To do this, I used the sys.dm_exec_query_stats DMV. This does two things for me. One, it shows me a list of stored procedures in cache, meaning they’ve been executed relatively recently and are probably relevant to the search. Secondly, it shows me the last execution time, which in some cases may have been before the issue, meaning I do not need to worry about re-running those processes.
Here’s the query I used:
SELECT DB_NAME(dest.[dbid]) AS 'databaseName' , OBJECT_NAME(dest.objectid, dest.[dbid]) AS 'procName' , MAX(deqs.last_execution_time) AS 'last_execution' FROM sys.dm_exec_query_stats AS deqs Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.[TEXT] Like '%yourTableName%' -- replace And dest.[dbid] IS Not Null -- exclude ad-hocs GROUP BY DB_NAME(dest.[dbid]) , OBJECT_NAME(dest.objectid, dest.[dbid]) ORDER BY databaseName , procName OPTION (MaxDop 1);
This will return results similar to:
databaseName procName last_execution -------------------- ------------------------------ ----------------------- AdventureWorks ufnGetProductListPrice 2009-08-03 09:57:25.390 AdventureWorksDW DimProductCategoryGet_sp 2009-08-03 09:59:05.820 AdventureWorksDW DimProductGet_sp 2009-08-03 09:58:38.370
I want to stress that this is *not* a list of all referencing objects, but rather a list of recently executed stored procedures that are still in memory. This list may not be accurate if your cache has recently been flushed or if you’ve recently rebooted your server.
Poor (Wo)Man’s Graph
Lary shared this poor (wo)man’s graph with me today, and I thought it was pretty awesome:
SELECT OrderDate , COUNT(*) AS 'orders' , REPLICATE('=', COUNT(*)) AS 'orderGraph' , SUM(TotalDue) AS 'revenue' , REPLICATE('$', SUM(TotalDue)/1000) AS 'revenueGraph' FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate Between '2003-07-15' And '2003-07-31' GROUP BY OrderDate ORDER BY OrderDate;
This will return a simple but effective “graph” for you:
orderDate orders orderGraph revenue revenueGraph ---------- ------ ------------------------------ -------- ---------------------------------------- 2003-07-15 19 =================== 34025.24 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-16 14 ============== 26687.65 $$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-17 16 ================ 32411.93 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-18 9 ========= 18634.91 $$$$$$$$$$$$$$$$$$$ 2003-07-19 13 ============= 19603.23 $$$$$$$$$$$$$$$$$$$$ 2003-07-20 24 ======================== 47522.80 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-21 9 ========= 11781.62 $$$$$$$$$$$$ 2003-07-22 17 ================= 32322.50 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-23 15 =============== 30906.44 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-24 28 ============================ 51107.90 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-25 15 =============== 27058.10 $$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-26 18 ================== 41076.49 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-27 15 =============== 22169.88 $$$$$$$$$$$$$$$$$$$$$$ 2003-07-28 16 ================ 23945.80 $$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-29 25 ========================= 51122.95 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 2003-07-30 12 ============ 23476.44 $$$$$$$$$$$$$$$$$$$$$$$ 2003-07-31 18 ================== 36266.76 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Who needs Reporting Services when you’ve got REPLICATE? 
Index Defrag Script Update
A couple of people pointed out to me that the stats rebuild feature in my defrag script will only complete for one database. Whoopsies! I’ve fixed the bug and updated the script in my previous post, so if you’re using my defrag script, please update it. Thanks to Derick and SuperCoolMoss for letting me know about the bug.
I’ve also received a couple of other requests for feature enhancements. I’m currently swamped at work and outside of work, but as soon as I get time, I will release the latest version. Realistically, it’ll probably be sometime after summer ends. 
Thank you to everyone for their comments and suggestions!
Random Number Generator in T-SQL
Filed under: Performance & Tuning, SQL Tips, Syndication
Ever need to generate a random number in T-SQL? I have, on a couple of different occasions. I’m pretty sure that there’s several different ways of doing this in T-SQL, but here’s what I use:
DECLARE @maxRandomValue TINYINT = 100 , @minRandomValue TINYINT = 0; SELECT CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND() + @minRandomValue AS TINYINT) AS 'randomNumber';
This approach uses the RAND() function to generate a random seed; it also ensures that the value returned is between the specified min and max value. I’ve been using this method in one stored procedure that’s called a couple of hundred times per second, and it seems to perform pretty well.
What method do YOU use to generate a random number? Is it faster than this method?
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.
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; ----------------------------------------------------------------- */
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!
Partitioning Example
The following code will walk you through the process of creating a partitioned table:
-------------------------------- -- Create A Partitioned Table -- -------------------------------- /* Create a partition function. */ CREATE Partition FUNCTION [test_monthlyDateRange_pf] (SMALLDATETIME) AS Range RIGHT FOR VALUES ('2008-01-01', '2008-02-01', '2008-03-01'); Go /* Associate the partition function with a partition scheme. */ CREATE Partition Scheme test_monthlyDateRange_ps AS Partition test_monthlyDateRange_pf All TO ([PRIMARY]); Go /* Create your first partitioned table! Make sure the data types match. */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate SMALLDATETIME Not Null CONSTRAINT PK_orders PRIMARY KEY CLUSTERED ( orderDate , order_id ) ) ON test_monthlyDateRange_ps(orderDate); Go /* Create some records to play with. */ INSERT INTO dbo.orders SELECT '2007-12-31' UNION All SELECT '2008-01-02' UNION All SELECT '2008-01-03' UNION All SELECT '2008-01-04' UNION All SELECT '2008-02-01' UNION All SELECT '2008-02-02' UNION All SELECT '2008-03-01' UNION All SELECT '2008-03-02'; /* The $partition function can be used to interrogate partition data. Let's use it to see where those records are physically located. */ SELECT $partition.test_monthlyDateRange_pf(orderDate) AS 'partition_number' , * FROM dbo.orders; /* By default, all new indexes are created on the partition. Let's create an aligned index */ CREATE NONCLUSTERED INDEX IX_orders_aligned ON dbo.orders(order_id) ON test_monthlyDateRange_ps(orderDate); /* Now let's create an un-aligned index. We'll need to specify the filegroup. */ CREATE NONCLUSTERED INDEX IX_orders_nonaligned ON dbo.orders(order_id) ON [PRIMARY]; -- can be any filegroup /* Review your indexes */ EXECUTE SP_HELPINDEX orders;
Using the previous code as a building block, let’s try swapping partitions:
-------------------------- -- Swap Out A Partition -- -------------------------- /* We need to drop our un-aligned index; otherwise we'll get an error when we attempt to do the switch. */ DROP INDEX IX_orders_nonaligned ON dbo.orders; /* Create the table to hold the data you're swapping out. The table structures must match identically; however, DO NOT partition this table. */ CREATE TABLE dbo.orders_stage_swapOut ( order_id INT Not Null , orderDate SMALLDATETIME Not Null CONSTRAINT PK_orders_stage_swapOut PRIMARY KEY CLUSTERED ( orderDate , order_id ) ) ON [PRIMARY]; Go /* Create the table to hold the data you're swapping in. The table structures must match identically; however, DO NOT partition this table. */ CREATE TABLE dbo.orders_stage_swapIn ( order_id INT Not Null , orderDate SMALLDATETIME Not Null CONSTRAINT PK_orders_stage_swapIn PRIMARY KEY CLUSTERED ( orderDate , order_id ) ) ON [PRIMARY]; Go /* Populate the table you're swapping in. */ INSERT INTO dbo.orders_stage_swapIn SELECT -5, '2008-02-02' UNION All SELECT -4, '2008-02-03' UNION All SELECT -3, '2008-02-04' UNION All SELECT -2, '2008-02-05' UNION All SELECT -1, '2008-02-06'; /* Create any indexes on your table to match the indexes on your partitioned table. */ CREATE NONCLUSTERED INDEX IX_orders_stage_swapIn ON dbo.orders_stage_swapIn(order_id); /* Add a check constraint for the partition to be swapped in. This step is required. */ ALTER TABLE dbo.orders_stage_swapIn WITH CHECK ADD CONSTRAINT orders_stage_swapIn_orderDateCK CHECK (orderDate >= '2008-02-01' And orderDate < '2008-03-01'); Go /* Swap out the old partition. */ ALTER TABLE dbo.orders Switch Partition 3 TO dbo.orders_stage_swapOut; Go /* Swap in the new partition. */ ALTER TABLE dbo.orders_stage_swapIn Switch TO dbo.orders Partition 3; Go --------------------- -- Check your data -- --------------------- /* You should have 2 records in here. */ SELECT * FROM dbo.orders_stage_swapOut; /* You should have 5 records here. */ SELECT * FROM dbo.orders WHERE orderDate >= '2008-02-01' And orderDate < '2008-03-01'; /* There should be no records in this table. */ SELECT * FROM dbo.orders_stage_swapIn; SELECT $partition.test_monthlyDateRange_pf(orderDate) AS 'partition_number' , * FROM dbo.orders; /* Clean-up time! Drop Table dbo.orders Drop Table dbo.orders_stage_swapOut Drop Table dbo.orders_stage_swapIn Drop Partition Scheme test_monthlyDateRange_ps Drop Partition Function [test_monthlyDateRange_pf] */
Pretty easy, huh?
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
