Index Defrag Script Updates – Beta Testers Needed
January 26, 2010 by Michelle Ufford · 4 Comments
Filed under: Miscellaneous, Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
Filed under: Miscellaneous, Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
Update: Wow! I’ve received a ton of responses to my request for beta testers. Thank you all! The SQL Community is really amazing. I’ll hopefully have the new version online in just a few days. 
Over the last few months, I’ve received many great comments and suggestions regarding my Index Defrag Script v3.0. I’ve just recently had time to implement most of these suggestions, plus some other things that I thought would be useful.
Here’s some of what you can look forward to shortly:
- Probably the single most requested feature, the new version of the script allows you to set a time limit for index defrags.
- There’s now a static table for managing the status of index defrags. This way, when your time limit is reached, you can pick up where you left off the next day, without the need to rescan indexes.
- There’s now an option to prioritize defrags by range scan counts, fragmentation level, or page counts.
- For those using partitioning, there is now an option to exclude the right-most populated partition from defrags (in theory, the one you’re writing to in a sliding-window scenario).
- Options such as page count limits and SORT_IN_TEMPDB are now parameterized.
- I’ve enhanced error logging.
- … and more!
Right now, I’m looking for a few folks who are willing to beta test the script. If you’re interested, please send me an e-mail at michelle at sqlfool dot com with the editions of SQL Server you can test this on (i.e. 2005 Standard, 2008 Enterprise, etc.).
Thank you!
Monitoring Process for Performance Counters
September 16, 2009 by Michelle Ufford · 6 Comments
Filed under: Performance & Tuning, Syndication, T-SQL Scripts
Filed under: Performance & Tuning, Syndication, T-SQL Scripts
Recently I needed to create a process to monitor performance counters over a short period of time. We were going to implement a change and we wanted to compare performance before and after to see if there was any impact.
To do this, I first created a couple of tables. One table is used to actually store the monitored values. The second table is used for configuration; you insert only the counters you want to monitor.
/* Create the table to store our logged perfmon counters */ CREATE TABLE dbo.dba_perfCounterMonitor ( capture_id INT IDENTITY(1,1) Not Null , captureDate SMALLDATETIME Not Null , objectName NVARCHAR(128) Not Null , counterName NVARCHAR(128) Not Null , instanceName NVARCHAR(128) Not Null , VALUE FLOAT(6) Not Null , valueType NVARCHAR(10) Not Null CONSTRAINT PK_dba_perfCounterMonitor PRIMARY KEY CLUSTERED(capture_id) ); /* Create the table that controls which counters we're going to monitor */ CREATE TABLE dbo.dba_perfCounterMonitorConfig ( objectName NVARCHAR(128) Not Null , counterName NVARCHAR(128) Not Null , instanceName NVARCHAR(128) Null );
If you leave the instanceName NULL in the config table, it’ll monitor all instances. Now we’re going to insert some sample performance counters into the config table. The counters you’re interested in can, and likely will, vary.
/* Insert some perfmon counters to be monitored */ INSERT INTO dbo.dba_perfCounterMonitorConfig SELECT 'SQLServer:Buffer Manager', 'Page Life Expectancy', Null UNION All SELECT 'SQLServer:Locks', 'Lock Requests/sec', Null UNION All SELECT 'SQLServer:Locks', 'Lock Waits/sec', Null UNION All SELECT 'SQLServer:Locks', 'Lock Wait Time (ms)', Null UNION All SELECT 'SQLServer:Buffer Manager', 'Page reads/sec', Null UNION All SELECT 'SQLServer:Buffer Manager', 'Page writes/sec', Null UNION All SELECT 'SQLServer:Buffer Manager', 'Buffer cache hit ratio', Null UNION All SELECT 'SQLServer:Databases', 'Transactions/sec', 'AdventureWorks' UNION All SELECT 'SQLServer:General Statistics', 'Processes blocked', Null;
Now let’s create our proc. This proc will run for a specified time period and will *average* the counters over that time. I personally take snapshots every 15 seconds for 4 minutes; I have a scheduled task that runs this every 5 minutes. It’s not perfect, but it gives me a good idea of what’s happening on the server.
CREATE PROCEDURE dbo.dba_perfCounterMonitor_sp /* Declare Parameters */ @samplePeriod INT = 240 /* how long to sample, in seconds */ , @sampleRate CHAR(8) = '00:00:15' /* how frequently to sample, in seconds */ , @displayResults BIT = 0 /* display the results when done */ AS /********************************************************************************* Name: dba_perfCounterMonitor_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Monitors performance counters. Uses the dba_perfCounterMonitorConfig table to manage which perf counters to monitor. @samplePeriod - specifies how long the process will try to monitor performance counters; in seconds. @sampleRate - how long inbetween samples; in seconds. The average values over sample period is then logged to the dba_perfCounterMonitor table. Notes: There are 3 basic types of performance counter calculations: Value/Base: these calculations require 2 counters. The value counter (cntr_type = 537003264) has to be divided by the base counter (cntr_type = 1073939712). Per Second: these counters are store cumulative values; the value must be compared at 2 different times to calculate the difference (cntr_type = 537003264). Point In Time: these counters show what the value of the counter is at the current point-in-time (cntr_type = 65792). No calculation is necessary to derive the value. Called by: DBA Date User Description ---------------------------------------------------------------------------- 2009-09-04 MFU Initial Release ********************************************************************************* Exec dbo.dba_perfCounterMonitor_sp @samplePeriod = 60 , @sampleRate = '00:00:01' , @displayResults = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET Numeric_RoundAbort OFF; BEGIN /* Declare Variables */ DECLARE @startTime DATETIME , @endTime DATETIME , @iteration INT; SELECT @startTime = GETDATE() , @iteration = 1; DECLARE @samples TABLE ( iteration INT Not Null , objectName NVARCHAR(128) Not Null , counterName NVARCHAR(128) Not Null , instanceName NVARCHAR(128) Not Null , cntr_value FLOAT Not Null , base_value FLOAT Null , cntr_type BIGINT Not Null ); BEGIN Try /* Start a new transaction */ BEGIN TRANSACTION; /* Grab all of our counters */ INSERT INTO @samples SELECT @iteration , RTRIM(dopc.OBJECT_NAME) , RTRIM(dopc.counter_name) , RTRIM(dopc.instance_name) , RTRIM(dopc.cntr_value) , (SELECT cntr_value FROM sys.dm_os_performance_counters AS dopc1 WHERE dopc1.OBJECT_NAME = pcml.objectName And dopc1.counter_name = pcml.counterName + ' base' And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name)) , dopc.cntr_type FROM sys.dm_os_performance_counters AS dopc Join dbo.dba_perfCounterMonitorConfig AS pcml ON dopc.OBJECT_NAME = pcml.objectName And dopc.counter_name = pcml.counterName And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name); /* During our sample period, grab our counter values and store the results */ WHILE GETDATE() < DATEADD(SECOND, @samplePeriod, @startTime) BEGIN SET @iteration = @iteration + 1; INSERT INTO @samples SELECT @iteration , RTRIM(dopc.OBJECT_NAME) , RTRIM(dopc.counter_name) , RTRIM(dopc.instance_name) , dopc.cntr_value , (SELECT cntr_value FROM sys.dm_os_performance_counters AS dopc1 WHERE dopc1.OBJECT_NAME = pcml.objectName And dopc1.counter_name = pcml.counterName + ' base' And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name)) , dopc.cntr_type FROM sys.dm_os_performance_counters AS dopc Join dbo.dba_perfCounterMonitorConfig AS pcml ON dopc.OBJECT_NAME = pcml.objectName And dopc.counter_name = pcml.counterName And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name); /* Wait for a small delay */ WAITFOR Delay @sampleRate; END; /* Grab our end time for calculations */ SET @endTime = GETDATE(); /* Store the average of our point-in-time counters */ INSERT INTO dbo.dba_perfCounterMonitor ( captureDate , objectName , counterName , instanceName , VALUE , valueType ) SELECT @startTime , objectName , counterName , instanceName , AVG(cntr_value) , 'value' FROM @samples WHERE cntr_type = 65792 GROUP BY objectName , counterName , instanceName; /* Store the average of the value vs the base for cntr_type = 537003264 */ INSERT INTO dbo.dba_perfCounterMonitor ( captureDate , objectName , counterName , instanceName , VALUE , valueType ) SELECT @startTime , objectName , counterName , instanceName , AVG(cntr_value)/AVG(IsNull(base_value, 1)) , 'percent' FROM @samples WHERE cntr_type = 537003264 GROUP BY objectName , counterName , instanceName; /* Compare the first and last values for our cumulative, per-second counters */ INSERT INTO dbo.dba_perfCounterMonitor ( captureDate , objectName , counterName , instanceName , VALUE , valueType ) SELECT @startTime , objectName , counterName , instanceName , (MAX(cntr_value) - MIN(cntr_value)) / DATEDIFF(SECOND, @startTime, @endTime) , 'value' FROM @samples WHERE cntr_type = 272696576 GROUP BY objectName , counterName , instanceName; /* Should we display the results of our most recent execution? */ IF @displayResults = 1 SELECT captureDate , objectName , counterName , instanceName , VALUE , valueType FROM dbo.dba_perfCounterMonitor WITH (NoLock) WHERE captureDate = CAST(@startTime AS SMALLDATETIME) ORDER BY objectName , counterName , instanceName; /* If you have an open transaction, commit it */ IF @@TRANCOUNT > 0 COMMIT TRANSACTION; END Try BEGIN Catch /* Whoops, there was an error... rollback! */ IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; /* Return an error message and log it */ EXECUTE dbo.dba_logError_sp; END Catch; SET NOCOUNT OFF; RETURN 0; END Go
Like I said, it’s not perfect, but it gets the job done.
Getting an error about dba_logError_sp? Take a look at my error handling proc.
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.
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!
Index Defrag Script, v3.0
June 23, 2009 by Michelle Ufford · 89 Comments
Filed under: Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
Filed under: Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
I’ve just completed the latest version of my index defrag script! Here’s a brief list of the updates:
- Fixed a bug with the LOB logic. In the previous version, after a LOB was encountered, all subsequent indexes would be reorganized.
- Added support for stat rebuilds after the index defrag is complete (@rebuildStats)
- Added an exclusion list table (dba_indexDefragExclusion) to support index scheduling
- Modified logging to show which defrags are “in progress”; added columns to dba_indexDefragLog
- Added support for the defrag of the model and msdb databases
- Added @scanMode as a configurable parameter
So what can this index defrag script do? Well, for starters, you can:
- Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
- Run this one script from a centralized database for all databases on a server
- Run this script for a specific database or table
- Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
- Defrag individual partitions
- Log its actions and the duration of the defrag
- Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
- Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
- Schedule specific indexes to only be defragged on weekends, or every other day
To use this last option, you need to add a record to the dba_indexDefragExclusion table. I think all of the columns are pretty self-explanatory except the [exclusionMask] column. The way this works is each day of the week is assigned a value:
1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday
Take a SUM of the values for the days that you want excluded. So if you want an index to only be defragged on weekends, you would add up Monday through Friday (2+4+8+16+32) and use a value of 62 for the exclusionMask column. For a little more information on how this works, check out my blog post on Bitwise Operations.
Please note: if you don’t insert any records into the dba_indexDefragExclusion table, by default all indexes will be defragged every run-time if they exceed the specified thresholds. This is normal behavior and may be perfectly fine in your environment. However, if the dba_indexDefragExclusion table does not exist, the script will fail.
I try to document each parameter within the code, so check the comments section in the script for a full list of parameters and what they do.
Special thanks to everyone who helped beta test this script!
Without further ado, the script:
/* Drop Table Scripts: Drop Table dbo.dba_indexDefragLog; Drop Table dbo.dba_indexDefragExclusion; */ IF Not Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] In (N'dba_indexDefragLog', 'dba_indexDefragExclusion')) BEGIN CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) Not Null , databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , partitionNumber SMALLINT Not Null , fragmentation FLOAT Not Null , page_count INT Not Null , dateTimeStart DATETIME Not Null , dateTimeEnd DATETIME Null , durationSeconds INT Null CONSTRAINT PK_indexDefragLog PRIMARY KEY CLUSTERED (indexDefrag_id) ); PRINT 'dba_indexDefragLog Table Created'; CREATE TABLE dbo.dba_indexDefragExclusion ( databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , exclusionMask INT Not Null /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */ CONSTRAINT PK_indexDefragExclusion PRIMARY KEY CLUSTERED (databaseID, objectID, indexID) ); PRINT 'dba_indexDefragExclusion Table Created'; END ELSE RAISERROR('One or more tables already exist. Please drop or rename before proceeding.', 16, 0); IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_indexDefrag_sp; PRINT 'Procedure dba_indexDefrag_sp dropped'; END; Go CREATE PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 5.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 */ , @DATABASE VARCHAR(128) = Null /* Option to specify a database name; null will return all */ , @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @scanMode VARCHAR(10) = N'LIMITED' /* Options are LIMITED, SAMPLED, and DETAILED */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @maxDopRestriction TINYINT = Null /* Option to restrict the number of processors for the operation; only in Enterprise */ , @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 */ , @debugMode BIT = 0 /* display some useful comments to help determine if/where issues occur */ , @rebuildStats BIT = 1 /* option to rebuild stats after completed index defrags */ AS /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags all indexes for the current database Notes: CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING. @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @tableName Specify if you only want to defrag indexes for a specific table, format = databaseName.schema.tableName; if not specified, all tables will be defragged. @scanMode Specifies which scan mode to use to determine fragmentation levels. Options are: LIMITED - scans the parent level; quickest mode, recommended for most cases. SAMPLED - samples 1% of all data pages; if less than 10k pages, performs a DETAILED scan. DETAILED - scans all data pages. Use great care with this mode, as it can cause performance issues. @onlineRebuild 1 = online rebuild; 0 = offline rebuild @maxDopRestriction Option to specify a processor limit for index rebuilds @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 a little time to catch up @debugMode 1 = display debug comments; helps with troubleshooting 0 = do not display debug comments @rebuildStats Affects only statistics that need to be rebuilt 1 = rebuild stats 0 = do not rebuild stats Called by: SQL Agent Job or DBA Date Initials Version Description ---------------------------------------------------------------------------- 2007-12-18 MFU 1.0 Initial Release 2008-10-17 MFU 1.1 Added @defragDelay, CIX_temp_indexDefragList 2008-11-17 MFU 1.2 Added page_count to log table , added @printFragmentation option 2009-03-17 MFU 2.0 Provided support for centralized execution , consolidated Enterprise & Standard versions , added @debugMode, @maxDopRestriction , modified LOB and partition logic 2009-06-18 MFU 3.0 Fixed bug in LOB logic, added @scanMode option , added support for stat rebuilds (@rebuildStats) , support model and msdb defrag , added columns to the dba_indexDefragLog table , modified logging to show "in progress" defrags , added defrag exclusion list (scheduling) ********************************************************************************* Exec dbo.dba_indexDefrag_sp @executeSQL = 0 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET Numeric_RoundAbort OFF; SET Quoted_Identifier ON; BEGIN IF @debugMode = 1 RAISERROR('Undusting the cogs and starting up...', 0, 42) WITH NoWait; /* Declare our variables */ DECLARE @objectID INT , @databaseID INT , @databaseName NVARCHAR(128) , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(128) , @objectName NVARCHAR(128) , @indexName NVARCHAR(128) , @partitionNumber SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT , @editionCheck BIT , @debugMessage VARCHAR(128) , @updateSQL NVARCHAR(4000) , @partitionSQL NVARCHAR(4000) , @partitionSQL_Param NVARCHAR(1000) , @LOB_SQL NVARCHAR(4000) , @LOB_SQL_Param NVARCHAR(1000) , @rebuildStatsID INT , @rebuildStatsSQL NVARCHAR(1000) , @indexDefrag_id INT; /* Create our temporary tables */ CREATE TABLE #indexDefragList ( databaseID INT , databaseName NVARCHAR(128) , objectID INT , indexID INT , partitionNumber SMALLINT , fragmentation FLOAT , page_count INT , defragStatus BIT , schemaName NVARCHAR(128) Null , objectName NVARCHAR(128) Null , indexName NVARCHAR(128) Null ); CREATE TABLE #databaseList ( databaseID INT , databaseName VARCHAR(128) , scanStatus BIT , statsStatus BIT ); CREATE TABLE #processor ( [INDEX] INT , Name VARCHAR(128) , Internal_Value INT , Character_Value INT ); IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait; /* 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'; IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED') SET @scanMode = 'LIMITED'; /* Make sure we're not exceeding the number of processors we have available */ INSERT INTO #processor EXECUTE XP_MSVER 'ProcessorCount'; IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor) SELECT @maxDopRestriction = Internal_Value FROM #processor; /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) SET @editionCheck = 1 -- supports online rebuilds ELSE SET @editionCheck = 0; -- does not support online rebuilds IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait; /* Retrieve the list of databases to investigate */ INSERT INTO #databaseList SELECT database_id , name , 0 -- not scanned yet for fragmentation , 0 -- statistics not yet updated FROM sys.databases WHERE name = IsNull(@DATABASE, name) And [name] Not In ('master', 'tempdb')-- exclude system databases And [STATE] = 0; -- state must be ONLINE IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait; /* Loop through our list of databases */ WHILE (SELECT COUNT(*) FROM #databaseList WHERE scanStatus = 0) > 0 BEGIN SELECT TOP 1 @databaseID = databaseID FROM #databaseList WHERE scanStatus = 0; SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...'; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Determine which indexes to defrag using our user-defined parameters */ INSERT INTO #indexDefragList SELECT database_id AS databaseID , QUOTENAME(DB_NAME(database_id)) AS 'databaseName' , [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 */ , Null AS 'schemaName' , Null AS 'objectName' , Null AS 'indexName' FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode) WHERE avg_fragmentation_in_percent >= @minFragmentation And index_id > 0 -- ignore heaps And page_count > 8 -- ignore objects with less than 1 extent And index_level = 0 -- leaf-level nodes only, supports @scanMode OPTION (MaxDop 2); /* Keep track of which databases have already been scanned */ UPDATE #databaseList SET scanStatus = 1 WHERE databaseID = @databaseID; END CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(databaseID, objectID, indexID, partitionNumber); /* Delete any indexes from our to-do that are also in our exclusion list for today */ DELETE idl FROM #indexDefragList AS idl Join dbo.dba_indexDefragExclusion AS ide ON idl.databaseID = ide.databaseID And idl.objectID = ide.objectID And idl.indexID = ide.indexID WHERE exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) > 0; SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!' FROM #indexDefragList; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait; /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @indexID = indexID , @databaseID = databaseID , @databaseName = databaseName , @fragmentation = fragmentation , @partitionNumber = partitionNumber , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait; /* Look up index information */ SELECT @updateSQL = N'Update idl Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From #indexDefragList As idl Inner Join ' + @databaseName + '.sys.objects As o On idl.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' And i.type > 0 And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10)); EXECUTE SP_EXECUTESQL @updateSQL; /* Grab our object names */ SELECT @objectName = objectName , @schemaName = schemaName , @indexName = indexName FROM #indexDefragList WHERE objectID = @objectID And indexID = @indexID And databaseID = @databaseID; IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait; /* Determine if the index is partitioned */ SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT; IF @debugMode = 1 RAISERROR(' Seeing if there''s any LOBs to be handled...', 0, 42) WITH NoWait; /* Determine if the table contains LOBs */ SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*) From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT; IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait; /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */ IF @fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1 BEGIN SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = @sqlCommand + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); END; /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */ IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 BEGIN /* Set online rebuild options; requires Enterprise Edition */ IF @onlineRebuild = 1 And @editionCheck = 1 SET @rebuildCommand = N' Rebuild With (Online = On'; ELSE SET @rebuildCommand = N' Rebuild With (Online = Off'; /* Set processor restriction options; requires Enterprise Edition */ IF @maxDopRestriction IS Not Null And @editionCheck = 1 SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')'; ELSE SET @rebuildCommand = @rebuildCommand + N')'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; END; /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN IF @debugMode = 1 RAISERROR(' Executing SQL statements...', 0, 42) WITH NoWait; /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart ) SELECT @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @dateTimeStart; SET @indexDefrag_id = SCOPE_IDENTITY(); /* Execute our defrag! */ EXECUTE SP_EXECUTESQL @sqlCommand; SET @dateTimeEnd = GETDATE(); /* Update our log with our completion time */ UPDATE dbo.dba_indexDefragLog SET dateTimeEnd = @dateTimeEnd , durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd) WHERE indexDefrag_id = @indexDefrag_id; /* 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 printing the commands */ BEGIN IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait; IF @printCommands = 1 PRINT IsNull(@sqlCommand, 'error!'); END IF @debugMode = 1 RAISERROR(' Updating our index defrag status...', 0, 42) WITH NoWait; /* Update our index defrag list so we know we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying fragmentation results...', 0, 42) WITH NoWait; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , fragmentation , page_count FROM #indexDefragList; END; /* Do we want to rebuild stats? */ IF @rebuildStats = 1 BEGIN WHILE Exists(SELECT TOP 1 * FROM #databaseList WHERE statsStatus = 0) BEGIN /* Build our SQL statement to update stats */ SELECT TOP 1 @rebuildStatsSQL = 'Use [' + databaseName + ']; ' + 'Execute sp_updatestats;' , @rebuildStatsID = databaseID FROM #databaseList WHERE statsStatus = 0; SET @debugMessage = 'Rebuilding Statistics: ' + @rebuildStatsSQL; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Execute our stats update! */ EXECUTE SP_EXECUTESQL @rebuildStatsSQL; /* Keep track of which databases have been updated */ UPDATE #databaseList SET statsStatus = 1 WHERE databaseID = @rebuildStatsID; END; END; /* When everything is said and done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; DROP TABLE #databaseList; DROP TABLE #processor; IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NoWait; SET NOCOUNT OFF; RETURN 0 END Go SET Quoted_Identifier OFF SET ANSI_Nulls ON Go
Page Internals – Investigation Proc
As many of you know, I like to crawl around in page internals in my free time. It can be very enlightening, or just a good check to make sure that what you think is happening, is actually happening. To help with this process, I’ve created myself a little stored procedure that I can simply pass a few parameters to and have it return the page data for me. So for those who don’t have anything better to do who are as interested in page internals as I am, here’s my proc:
CREATE PROCEDURE dbo.dba_viewPageData_sp /* Declare Parameters */ @databaseName VARCHAR(128) , @tableName VARCHAR(128) = Null -- database.schema.tableName , @indexName VARCHAR(128) = Null , @fileNumber INT = Null , @pageNumber INT = Null , @printOption INT = 3 -- 0, 1, 2, or 3 , @pageType CHAR(4) = 'Leaf' -- Leaf, Root, or IAM AS /********************************************************************************* Name: dba_viewPageData_sp Author: Michelle Ufford Purpose: Retrieves page data for the specified table/page. Notes: Can pass either the table name or the pageID, but must pass one, or you'll end up with no results. If the table name is passed, it will return the first page. @tableName must be '<databaseName>.<schemaName>.<tableName>' in order to function correctly for cross-database joins. @printOption can be one of following values: 0 - print just the page header 1 - page header plus per-row hex dumps and a dump of the page slot array 2 - page header plus whole page hex dump 3 - page header plus detailed per-row interpretation Page Options borrowed from: https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx @pageType must be one of the following values: Leaf - returns the first page of the leaf level of your index or heap Root - returns the root page of your index IAM - returns the index allocation map chain for your index or heap Conversions borrowed from: http://sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine- sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work.aspx Called by: DBA Date User Description ---------------------------------------------------------------------------- 2009-05-06 MFU Initial release for public consumption ********************************************************************************* Exec dbo.dba_viewPageData_sp @databaseName = 'AdventureWorks' , @tableName = 'AdventureWorks.Sales.SalesOrderDetail' , @indexName = 'IX_SalesOrderDetail_ProductID' --, @fileNumber = 1 --, @pageNumber = 38208 , @printOption = 3 , @pageType = 'Root'; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET Numeric_RoundAbort OFF; BEGIN DECLARE @fileID INT , @pageID INT , @sqlStatement NVARCHAR(1200) , @sqlParameters NVARCHAR(255) , @errorMessage VARCHAR(100); BEGIN Try IF @fileNumber IS Null And @pageNumber IS Null And @tableName IS Null BEGIN SET @errorMessage = 'You must provide either a file/page number, or a table name!'; RAISERROR(@errorMessage, 16, 1); END; IF @pageType Not In ('Leaf', 'Root', 'IAM') BEGIN SET @errorMessage = 'You have entered an invalid page type; valid options are "Leaf", "Root", or "IAM"'; RAISERROR(@errorMessage, 16, 1); END; IF @fileNumber IS Null Or @pageNumber IS Null BEGIN SET @sqlStatement = CASE WHEN @pageType = 'Leaf' THEN 'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, SubString (au.first_page, 6, 1) + SubString (au.first_page, 5, 1))) , @p_pageID = Convert (varchar(20), Convert (int, SubString (au.first_page, 4, 1) + SubString (au.first_page, 3, 1) + SubString (au.first_page, 2, 1) + SubString (au.first_page, 1, 1)))' WHEN @pageType = 'Root' THEN 'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, SubString (au.root_page, 6, 1) + SubString (au.root_page, 5, 1))) , @p_pageID = Convert (varchar(20), Convert (int, SubString (au.root_page, 4, 1) + SubString (au.root_page, 3, 1) + SubString (au.root_page, 2, 1) + SubString (au.root_page, 1, 1)))' WHEN @pageType = 'IAM' THEN 'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, SubString (au.first_iam_page, 6, 1) + SubString (au.first_iam_page, 5, 1))) , @p_pageID = Convert (varchar(20), Convert (int, SubString (au.first_iam_page, 4, 1) + SubString (au.first_iam_page, 3, 1) + SubString (au.first_iam_page, 2, 1) + SubString (au.first_iam_page, 1, 1)))' END + 'From ' + QUOTENAME(PARSENAME(@databaseName, 1)) + '.sys.indexes AS i Join ' + QUOTENAME(PARSENAME(@databaseName, 1)) + '.sys.partitions AS p On i.[object_id] = p.[object_id] And i.index_id = p.index_id Join ' + QUOTENAME(PARSENAME(@databaseName, 1)) + '.sys.system_internals_allocation_units AS au On p.hobt_id = au.container_id Where p.[object_id] = Object_ID(@p_tableName) And au.first_page > 0x000000000000 ' + CASE WHEN @indexName IS Null THEN ';' ELSE 'And i.name = @p_indexName;' END; SET @sqlParameters = '@p_tableName varchar(128) , @p_indexName varchar(128) , @p_fileID int OUTPUT , @p_pageID int OUTPUT'; EXECUTE SP_EXECUTESQL @sqlStatement , @sqlParameters , @p_tableName = @tableName , @p_indexName = @indexName , @p_fileID = @fileID OUTPUT , @p_pageID = @pageID OUTPUT; END ELSE BEGIN SELECT @fileID = @fileNumber , @pageID = @pageNumber; END; DBCC TraceOn (3604); DBCC Page (@databaseName, @fileID, @pageID, @printOption); DBCC TraceOff (3604); END Try BEGIN Catch PRINT @errorMessage; END Catch; SET NOCOUNT OFF; RETURN 0; END Go
This proc does have cross-database support, i.e. you can install it in your DBA database and use it to investigate data in other databases. Here’s an example…
EXEC dbo.dba_viewPageData_sp @databaseName = 'AdventureWorks' , @tableName = 'AdventureWorks.Sales.SalesOrderDetail' , @indexName = 'IX_SalesOrderDetail_ProductID';
… will return a nice data page:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. PAGE: (1:11000) BUFFER: BUF @0x0391F140 bpage = 0x0C0C0000 bhash = 0x00000000 bpageno = (1:11000) bdbid = 7 breferences = 0 bUse1 = 35177 bstat = 0x1c00009 blog = 0x21212159 bnext = 0x00000000
[waits for the "oohs" and "aahs" to subside...]
I also give you the option to specify a specific page, in case you want to follow the page trail (i.e. m_nextPage). I’m not really providing support for partitions, although I do have a little dirty piece of code to return a hobt with data if possible (i.e. “first_page > 0×000000000000″).
Update: Special thanks to Jeremiah Peschka and Adam Machanic for showing me
QUOTENAME(PARSENAME(@databaseName, 1))!
Also, thanks to Paul Randal for his excellent blog posts on this very topic! Check out his blog post on DBCC Page. The conversion code was borrowed from his sp_AllocationMetadata proc.
Update 2: I’ve made some quick changes based on some feedback from Twitter. Thanks for the suggestions and hopefully you enjoy the updates.
Happy crawling!
Automated Index Defrag Script
March 25, 2009 by Michelle Ufford · 35 Comments
Filed under: Performance & Tuning, SQL 2008, SQL Tips, T-SQL Scripts
Filed under: Performance & Tuning, SQL 2008, SQL Tips, T-SQL Scripts
Note: This script has been updated. You can find the latest version here: http://sqlfool.com/2009/06/index-defrag-script-v30/
So after much delay, here’s the latest and greatest version of my index defrag script.
A summary of the changes:
- Added support for centralized execution. Option to defrag indexes for a single database from another database, or for all non-system databases.
- Consolidated Enterprise and Standard versions of the script with new @editionCheck variable.
- Added parametrization for MaxDop restrictions during rebuilds; validates that the value does not exceed the actual number of processors on the server.
- Changed minimum fragmentation default value from 10 to 5 to match BOL recommendations.
- Limited defrags to objects with more than 8 pages.
- Added a debug option to give a little more insight into what’s happening and to assist with troubleshooting.
- Updated logic for handling partitions and LOBs.
And a couple of notes and explanations:
Don’t you know you can just pass NULL to sys.dm_db_index_physical_stats for the DatabaseID?
Yes, I realize you can do this. But I don’t want to defrag the system databases, i.e. tempdb, so I opted to handle it this way instead.
How long will this take?
It depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.
Where should I put this?
It’s up to you. If you have a database for items like centralized maintenance or scratch tables, that may be a good place for it. If you prefer, you could also put this in each individual database and call it locally, too. I would not put this in the master or msdb databases.
This is pretty close to a complete rewrite, so please let me know if you encounter any bugs. And now… the code!
IF Not Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE name = N'dba_indexDefragLog') BEGIN -- Drop Table dbo.dba_indexDefragLog CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) Not Null , databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) 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'; END IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_indexDefrag_sp; PRINT 'Procedure dba_indexDefrag_sp dropped'; END; Go CREATE PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 5.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 */ , @DATABASE VARCHAR(128) = Null /* Option to specify a database name; null will return all */ , @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @maxDopRestriction TINYINT = Null /* Option to restrict the number of processors for the operation; only in Enterprise */ , @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 */ , @debugMode BIT = 0 /* display some useful comments to help determine if/where issues occur */ AS /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags all indexes for one or more databases Notes: CAUTION: TRANSACTION LOG SIZE MUST BE MONITORED CLOSELY WHEN DEFRAGMENTING. @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @tableName Specify if you only want to defrag indexes for a specific table, format = databaseName.schema.tableName; if not specified, all tables will be defragged. @onlineRebuild 1 = online rebuild; 0 = offline rebuild @maxDopRestriction Option to specify a processor limit for index rebuilds @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 a little time to catch up @debugMode 1 = display debug comments; helps with troubleshooting 0 = do not display debug comments Called by: SQL Agent Job or DBA Date Initials Description ---------------------------------------------------------------------------- 2008-10-27 MFU Initial Release for public consumption 2008-11-17 MFU Added page-count to log table , added @printFragmentation option 2009-03-17 MFU Provided support for centralized execution, , consolidated Enterprise & Standard versions , added @debugMode, @maxDopRestriction , modified LOB and partition logic ********************************************************************************* Exec dbo.dba_indexDefrag_sp @executeSQL = 0 , @minFragmentation = 80 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1 , @database = 'AdventureWorks' , @tableName = 'AdventureWorks.Sales.SalesOrderDetail'; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Quoted_Identifier ON; BEGIN IF @debugMode = 1 RAISERROR('Dusting off the spiderwebs and starting up...', 0, 42) WITH NoWait; /* Declare our variables */ DECLARE @objectID INT , @databaseID INT , @databaseName NVARCHAR(128) , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(128) , @objectName NVARCHAR(128) , @indexName NVARCHAR(128) , @partitionNumber SMALLINT , @partitions SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT , @editionCheck BIT , @debugMessage VARCHAR(128) , @updateSQL NVARCHAR(4000) , @partitionSQL NVARCHAR(4000) , @partitionSQL_Param NVARCHAR(1000) , @LOB_SQL NVARCHAR(4000) , @LOB_SQL_Param NVARCHAR(1000); /* Create our temporary tables */ CREATE TABLE #indexDefragList ( databaseID INT , databaseName NVARCHAR(128) , objectID INT , indexID INT , partitionNumber SMALLINT , fragmentation FLOAT , page_count INT , defragStatus BIT , schemaName NVARCHAR(128) Null , objectName NVARCHAR(128) Null , indexName NVARCHAR(128) Null ); CREATE TABLE #databaseList ( databaseID INT , databaseName VARCHAR(128) ); CREATE TABLE #processor ( [INDEX] INT , Name VARCHAR(128) , Internal_Value INT , Character_Value INT ); IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait; /* Just a little validation... */ IF @minFragmentation Not Between 0.00 And 100.0 SET @minFragmentation = 5.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'; /* Make sure we're not exceeding the number of processors we have available */ INSERT INTO #processor EXECUTE XP_MSVER 'ProcessorCount'; IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor) SELECT @maxDopRestriction = Internal_Value FROM #processor; /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) SET @editionCheck = 1 -- supports online rebuilds ELSE SET @editionCheck = 0; -- does not support online rebuilds IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait; /* Retrieve the list of databases to investigate */ INSERT INTO #databaseList SELECT database_id , name FROM sys.databases WHERE name = IsNull(@DATABASE, name) And database_id > 4 -- exclude system databases And [STATE] = 0; -- state must be ONLINE IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait; /* Loop through our list of databases */ WHILE (SELECT COUNT(*) FROM #databaseList) > 0 BEGIN SELECT TOP 1 @databaseID = databaseID FROM #databaseList; SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...'; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Determine which indexes to defrag using our user-defined parameters */ INSERT INTO #indexDefragList SELECT database_id AS databaseID , QUOTENAME(DB_NAME(database_id)) AS 'databaseName' , [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 */ , Null AS 'schemaName' , Null AS 'objectName' , Null AS 'indexName' FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, N'Limited') WHERE avg_fragmentation_in_percent >= @minFragmentation And index_id > 0 -- ignore heaps And page_count > 8 -- ignore objects with less than 1 extent OPTION (MaxDop 1); DELETE FROM #databaseList WHERE databaseID = @databaseID; END CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(databaseID, objectID, indexID, partitionNumber); SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!' FROM #indexDefragList; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait; /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @indexID = indexID , @databaseID = databaseID , @databaseName = databaseName , @fragmentation = fragmentation , @partitionNumber = partitionNumber , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait; /* Look up index information */ SELECT @updateSQL = N'Update idl Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From #indexDefragList As idl Inner Join ' + @databaseName + '.sys.objects As o On idl.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' And i.type > 0 And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10)); EXECUTE SP_EXECUTESQL @updateSQL; /* Grab our object names */ SELECT @objectName = objectName , @schemaName = schemaName , @indexName = indexName FROM #indexDefragList WHERE objectID = @objectID And indexID = @indexID And databaseID = @databaseID; IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait; /* Determine if the index is partitioned */ SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT; IF @debugMode = 1 RAISERROR(' Seeing if there''s any LOBs to be handled...', 0, 42) WITH NoWait; /* Determine if the table contains LOBs */ SELECT @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT; IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait; /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */ IF @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1 BEGIN SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = @sqlCommand + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); END; /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */ IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 BEGIN /* Set online rebuild options; requires Enterprise Edition */ IF @onlineRebuild = 1 And @editionCheck = 1 SET @rebuildCommand = N' Rebuild With (Online = On'; ELSE SET @rebuildCommand = N' Rebuild With (Online = Off'; /* Set processor restriction options; requires Enterprise Edition */ IF @maxDopRestriction IS Not Null And @editionCheck = 1 SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')'; ELSE SET @rebuildCommand = @rebuildCommand + N')'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; END; /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN IF @debugMode = 1 RAISERROR(' Executing SQL statements...', 0, 42) WITH NoWait; /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); EXECUTE SP_EXECUTESQL @sqlCommand; SET @dateTimeEnd = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , durationSeconds ) SELECT @databaseID , @databaseName , @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 printing the commands */ BEGIN IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait; IF @printCommands = 1 PRINT IsNull(@sqlCommand, 'error!'); END IF @debugMode = 1 RAISERROR(' Updating our index defrag status...', 0, 42) WITH NoWait; /* Update our index defrag list so we know we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying fragmentation results...', 0, 42) WITH NoWait; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , fragmentation , page_count FROM #indexDefragList; END; /* When everything is said and done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; DROP TABLE #databaseList; DROP TABLE #processor; IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NoWait; SET NOCOUNT OFF; RETURN 0 END Go
Thanks to my beta testers, @scoinva, @davidmtate, @jdanton, and @SuperCoolMoss!
Special thanks to SCM for keeping on me to finish this.
Happy Defragging!
Michelle
Source: http://sqlfool.com/2009/03/automated-index-defrag-script/
Index Clean-Up Scripts
January 20, 2009 by Michelle Ufford · 12 Comments
Filed under: Performance & Tuning, Syndication, T-SQL Scripts
Filed under: Performance & Tuning, Syndication, T-SQL Scripts
I’ve been spending a lot of time lately looking at indexing in my production environments… dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this.
Here’s the script I use to find any un-used indexes. This relies heavily on the sys.dm_db_index_usage_stats DMV (2005+). This query will also return the SQL statements needed to drop the indexes for convenience. This does NOT mean you should necessarily drop the index. This is only a guide and a starting point; only you know how your application is used and whether SQL Server’s recommendations make sense.
Un-Used Indexes Script
DECLARE @dbid INT , @dbName VARCHAR(100); SELECT @dbid = DB_ID() , @dbName = DB_NAME(); WITH partitionCTE (OBJECT_ID, index_id, row_count, partition_count) AS ( SELECT [OBJECT_ID] , index_id , SUM([ROWS]) AS 'row_count' , COUNT(partition_id) AS 'partition_count' FROM sys.partitions GROUP BY [OBJECT_ID] , index_id ) SELECT OBJECT_NAME(i.[OBJECT_ID]) AS objectName , i.name , CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc AS 'indexType' , ddius.user_seeks , ddius.user_scans , ddius.user_lookups , ddius.user_updates , cte.row_count , CASE WHEN partition_count > 1 THEN 'yes' ELSE 'no' END AS 'partitioned?' , CASE WHEN i.type = 2 And i.is_unique_constraint = 0 THEN 'Drop Index ' + i.name + ' On ' + @dbName + '.dbo.' + OBJECT_NAME(ddius.[OBJECT_ID]) + ';' WHEN i.type = 2 And i.is_unique_constraint = 1 THEN 'Alter Table ' + @dbName + '.dbo.' + OBJECT_NAME(ddius.[OBJECT_ID]) + ' Drop Constraint ' + i.name + ';' ELSE '' END AS 'SQL_DropStatement' FROM sys.indexes AS i INNER Join sys.dm_db_index_usage_stats ddius ON i.OBJECT_ID = ddius.OBJECT_ID And i.index_id = ddius.index_id INNER Join partitionCTE AS cte ON i.OBJECT_ID = cte.OBJECT_ID And i.index_id = cte.index_id WHERE ddius.database_id = @dbid ORDER BY (ddius.user_seeks + ddius.user_scans + ddius.user_lookups) ASC , user_updates DESC;
This next script relies on several DMV’s (2005+) that identify missing indexes. While this is good information, the index recommendations do not always make sense and/or sometimes overlap. Also, these DMV’s store data since the SQL Server was last restarted, so if it’s been a while since your server was rebooted, this data may be out of date. This script also provides a SQL statement, in case you do decide to create the index, but it doesn’t take into consideration advanced parameters (i.e. sort_in_tempDB, Online, MaxDop, etc.) and only provides a basic create statement. Nonetheless, it’s another good starting point.
Missing Index Script
SELECT t.name AS 'affected_table' , 'Create NonClustered Index IX_' + t.name + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(10)) + ' On ' + ddmid.STATEMENT + ' (' + IsNull(ddmid.equality_columns,'') + CASE WHEN ddmid.equality_columns IS Not Null And ddmid.inequality_columns IS Not Null THEN ',' ELSE '' END + IsNull(ddmid.inequality_columns, '') + ')' + IsNull(' Include (' + ddmid.included_columns + ');', ';' ) AS sql_statement , ddmigs.user_seeks , ddmigs.user_scans , CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) AS 'est_impact' , ddmigs.last_user_seek FROM sys.dm_db_missing_index_groups AS ddmig INNER Join sys.dm_db_missing_index_group_stats AS ddmigs ON ddmigs.group_handle = ddmig.index_group_handle INNER Join sys.dm_db_missing_index_details AS ddmid ON ddmig.index_handle = ddmid.index_handle INNER Join sys.tables AS t ON ddmid.OBJECT_ID = t.OBJECT_ID WHERE ddmid.database_id = DB_ID() And CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) > 100 ORDER BY CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) DESC;
Error Handling in T-SQL
Error handling is one of those things in SQL Server that just doesn’t get the attention it deserves. Even a properly constructed stored procedure can still result in error, such as primary key or unique constraint errors.
Why should you care? Consider this real-world example:
You’re a DBA monitoring a well-performing environment. You deploy a new application to production. Suddenly, performance degrades but you do not know why. You look in your error log and see a whole mess of primary key errors. Digging into your newly deployed application, you find that you are now making an extra (and unnecessary) insert to the database, which is resulting in error and causing your performance issues.
This is just one example of many. Fortunately, SQL 2005 has really simplified the error handling process with features such as the Try/Catch block.
The basic components of error handling are:
- Try…Catch block (2005/2008)
- Error identification
- Transaction handling
- Error logging (optional)
- Error notification
As an early holiday gift, here’s a generic error handling process to get you started:
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_logError_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_logError_sp; PRINT 'Procedure dba_logError_sp dropped'; END; Go IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_errorLog'), N'IsTable') IS Null BEGIN CREATE TABLE dbo.dba_errorLog ( errorLog_id INT IDENTITY(1,1) , errorType CHAR(3) CONSTRAINT [DF_errorLog_errorType] DEFAULT 'sys' , errorDate DATETIME CONSTRAINT [DF_errorLog_errorDate] DEFAULT(GETDATE()) , errorLine INT , errorMessage NVARCHAR(4000) , errorNumber INT , errorProcedure NVARCHAR(126) , procParameters NVARCHAR(4000) , errorSeverity INT , errorState INT , databaseName NVARCHAR(255) CONSTRAINT PK_errorLog_errorLogID PRIMARY KEY CLUSTERED ( errorLog_id ) ); PRINT 'Table dba_errorLog created'; 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 CREATE PROCEDURE dbo.dba_logError_sp ( /* Declare Parameters */ @errorType CHAR(3) = 'sys' , @app_errorProcedure VARCHAR(50) = '' , @app_errorMessage NVARCHAR(4000) = '' , @procParameters NVARCHAR(4000) = '' , @userFriendly BIT = 0 , @forceExit BIT = 1 , @returnError BIT = 1 ) AS /*************************************************************** Name: dba_logError_sp Author: Michelle F. Ufford, http://sqlfool.com Purpose: Retrieves error information and logs in the dba_errorLog table. @errorType = options are "app" or "sys"; "app" are custom application errors, i.e. business logic errors; "sys" are system errors, i.e. PK errors @app_errorProcedure = stored procedure name, needed for app errors @app_errorMessage = custom app error message @procParameters = optional; log the parameters that were passed to the proc that resulted in an error @userFriendly = displays a generic error message if = 1 @forceExit = forces the proc to rollback and exit; mostly useful for application errors. @returnError = returns the error to the calling app if = 1 Called by: Another stored procedure Date Initials Description ---------------------------------------------------------------------------- 2008-12-16 MFU Initial Release **************************************************************** Exec dbo.dba_logError_sp @errorType = 'app' , @app_errorProcedure = 'someTableInsertProcName' , @app_errorMessage = 'Some app-specific error message' , @userFriendly = 1 , @forceExit = 1 , @returnError = 1; ****************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare Variables */ DECLARE @errorNumber INT , @errorProcedure VARCHAR(50) , @dbName sysname , @errorLine INT , @errorMessage NVARCHAR(4000) , @errorSeverity INT , @errorState INT , @errorReturnMessage NVARCHAR(4000) , @errorReturnSeverity INT , @currentDateTime SMALLDATETIME; DECLARE @errorReturnID TABLE (errorID VARCHAR(10)); /* Initialize Variables */ SELECT @currentDateTime = GETDATE(); /* Capture our error details */ IF @errorType = 'sys' BEGIN /* Get our system error details and hold it */ SELECT @errorNumber = Error_Number() , @errorProcedure = Error_Procedure() , @dbName = DB_NAME() , @errorLine = Error_Line() , @errorMessage = Error_Message() , @errorSeverity = Error_Severity() , @errorState = Error_State() ; END ELSE BEGIN /* Get our custom app error details and hold it */ SELECT @errorNumber = 0 , @errorProcedure = @app_errorProcedure , @dbName = DB_NAME() , @errorLine = 0 , @errorMessage = @app_errorMessage , @errorSeverity = 0 , @errorState = 0 ; END; /* And keep a copy for our logs */ INSERT INTO dbo.dba_errorLog ( errorType , errorDate , errorLine , errorMessage , errorNumber , errorProcedure , procParameters , errorSeverity , errorState , databaseName ) OUTPUT Inserted.errorLog_id INTO @errorReturnID VALUES ( @errorType , @currentDateTime , @errorLine , @errorMessage , @errorNumber , @errorProcedure , @procParameters , @errorSeverity , @errorState , @dbName ); /* Should we display a user friendly message to the application? */ IF @userFriendly = 1 SELECT @errorReturnMessage = 'An error has occurred in the database (' + errorID + ')' FROM @errorReturnID; ELSE SELECT @errorReturnMessage = @errorMessage; /* Do we want to force the application to exit? */ IF @forceExit = 1 SELECT @errorReturnSeverity = 15 ELSE SELECT @errorReturnSeverity = @errorSeverity; /* Should we return an error message to the calling proc? */ IF @returnError = 1 RAISERROR ( @errorReturnMessage , @errorReturnSeverity , 1 ) WITH NoWait; SET NOCOUNT OFF; RETURN 0; END Go
You would then call this proc in the following manner:
BEGIN Try /* If a business logic error exists, then call this proc */ IF 1 != 1 EXECUTE dbo.dba_logError_sp @errorType = 'app' , @app_errorProcedure = 'yourStoredProcedureName' , @app_errorMessage = '1 does not equal 1!' , @forceExit = 1; /* Start a new transaction */ BEGIN TRANSACTION; /* Do something */ /* If you have an open transaction, commit it */ IF @@TRANCOUNT > 0 COMMIT TRANSACTION; END Try BEGIN Catch /* Whoops, there was an error... rollback! */ IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; /* Grab our proc parameters */ SET @errorParameters = '@myVariable = ' + @myVariable; /* Return an error message and log it */ EXECUTE dbo.dba_logError_sp @procParameters = @errorParameters; END Catch;
Some things to keep in mind:
- Error handling is not a “one-size-fits-all” process. Make sure you’re handling the error appropriately for your environment.
- Be careful when working with nested transactions; you can sometimes get unexpected results.
- Only errors with a severity levels greater than 10 will be caught by the Catch block.
- You can initiate an error within your stored procedure by using RaisError().
Happy coding holidays!
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.
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
