I don't know if it's ever happened to you, but I always find myself wishing I had documented what the devil I was thinking when I added an index to a table. I mean, it's all well and good to create an index, but it's annoying when six months later you can't remember what it was for. Is anything actually using this index? Can I maybe drop it?
Yes, yes, I suppose I could document my indexes, and I suppose some people do that kind of thing... but it just seems like so much... work.
Wouldn't it be cool if there was a way to automatically document which stored procedures use which indexes? And how many times each procedure used each index? And when the last time that was? And was integrated into SQL Server Management Studio, and all this with no work required on your part?
Of course it would be cool; so here it is. In this post, I'll just present the code, and what it will do for you; I'll walk through it in my next post.
Briefly, this script examines the current and cached query plans, and parses the info we want out of them; then it adds extended properties to each index.
After you run this script, your indexes will have extended properties like this, one for each stored procedure that uses it. Of course, the index had to have been in the query plan cache to show up here; since the script only takes a few seconds to run, I have a job that runs it every so often. Over the long run, all the indexes eventually get found in the cache; it just depends on how often your various applications run. Note that the date in each extended property is updated every time the script is run.
The Script
------------------------------------------------------------------------------- -- Document Indexes via Query Plans and Extended Properties.sql -- -- 2011-06-11 - Larry Leonard - http://sqlsoundings.blogspot.com -- -- (Note: In the comments below "index" is shorthand for "index or primary key -- constraint", and "stored procedure" is shorthand for "stored procedure, -- trigger, etc.".) -- -- This script first examines each currently executing query plan and extracts -- (a) the name of each index each query plan is using and (b) the name of the -- executing stored procedure for the query plan. An Extended Property for -- each stored procedure is added to the corresponding index(es). -- -- As this script is run, over time, every index (assuming it's ever caught in -- use) gathers a set of Extended Properties. Each Extended Property displays -- the *name* of the stored procedure that used it, how many *times* since the -- last SQL Server restart it used it, and the *date* this script last updated -- this Extended Property. For example: -- -- usp_CalculateInvoice Used this constraint 3315 times (2011-03-22) -- TR_Customer_Update Used this index 42 times (2011-05-01) -- -- This script runs against the the current database. It should be run on an -- irregular basis so that every query is more likely to be caught executing -- at least once. How long it takes for every query to have executed at least -- once depends entirely on your system. If after a sufficient amount of time -- has passed, an index does not have any Extended Properties created by this -- script, that doesn't prove the index is unused, only that the index needs -- investigation. Don't drop indexes based solely on the usage of this script. -- You should read and test this script thoroughly before using it in a -- production environment. ------------------------------------------------------------------------------- RAISERROR('"Document Indexes via Query Plans and Extended Properties.sql" starting on "%s"' , 10, 1, @@SERVERNAME) WITH NOWAIT, LOG IF DB_ID() < 5 BEGIN RAISERROR('This is a system database.', 16, 1) RETURN END SET NOCOUNT ON SET XACT_ABORT ON -- We don't want to kill some other process doing real work. SET DEADLOCK_PRIORITY -10 ------------------------------------------------------------------------------- -- Variables. None of these should need to be changed in normal use. ------------------------------------------------------------------------------- DECLARE @DatabaseName sysname = '' DECLARE @ProcedureName sysname = '' DECLARE @SchemaName sysname = '' DECLARE @SchemaNameUnquoted sysname = '' DECLARE @TableName sysname = '' DECLARE @TableNameUnquoted sysname = '' DECLARE @IndexName sysname = '' DECLARE @PlanUseCount INT = 0 DECLARE @PlanUseCountString NVARCHAR(50) = '' DECLARE @Len INT = 0 DECLARE @IndexNamePrev sysname = '' DECLARE @IndexNameUnquoted sysname = '' DECLARE @ExtendedPropertyName sysname = '' DECLARE @ExtendedPropertyValue NVARCHAR(3750) = '' -- The value of an extended property is a sql_variant value that can contain up to 7,500 bytes of data. DECLARE @ExtendedPropertyValueOld NVARCHAR(3750) = '' DECLARE @Level2Type VARCHAR(50) = '' DECLARE @Now NVARCHAR(50) = CONVERT(NVARCHAR, CAST(GETDATE() AS DATE), 120) DECLARE @Msg NVARCHAR(2048) = '' DECLARE @Error INT = 0 DECLARE @AddedCount INT = 0 DECLARE @UpdatedCount INT = 0 DECLARE @UnchangedCount INT = 0 RAISERROR('Looking for indexes in the plan cache...', 10, 1) WITH NOWAIT DECLARE cur CURSOR FOR WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), cteCachedPlansXml AS ( SELECT DISTINCT cp.plan_handle AS plan_handle , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Database', N'varchar(128)'), RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Database', N'varchar(128)'), RelOp.op.value(N'IndexScan [1]/Object[1]/@Database', N'varchar(128)')) AS DatabaseName , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Schema', N'varchar(128)'), RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Schema', N'varchar(128)'), RelOp.op.value(N'IndexScan [1]/Object[1]/@Schema', N'varchar(128)')) AS SchemaName , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Table', N'varchar(128)'), RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Table', N'varchar(128)'), RelOp.op.value(N'IndexScan [1]/Object[1]/@Table', N'varchar(128)')) AS TableName , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Index', N'varchar(128)'), RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Index', N'varchar(128)'), RelOp.op.value(N'IndexScan [1]/Object[1]/@Index', N'varchar(128)')) AS IndexName , QUOTENAME(OBJECT_NAME(qp.objectid)) AS ProcedureName , ISNULL(cp.usecounts, 0) AS PlanUseCount FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op) WHERE qp.dbid = DB_ID() AND cp.cacheobjtype IN ('Compiled Plan', 'Compiled Plan Stub', 'Parse Tree', 'Extended Proc', 'CLR Compiled Func', 'CLR Compiled Proc') AND cp.objtype IN ('Proc', 'Prepared', 'Adhoc', 'ReplProc', 'Trigger', 'View', 'Default', 'UsrTab', 'SysTab', 'Check', 'Rule') ) , cteCachedPlans AS ( SELECT DISTINCT ctecpx.SchemaName AS SchemaName , ctecpx.TableName AS TableName , ctecpx.IndexName AS IndexName , SUBSTRING(ctecpx.IndexName, 2, LEN(ctecpx.IndexName) - 2) AS IndexNameUnquoted , CASE MIN(CAST(i.is_primary_key as int)) WHEN 0 THEN 'INDEX' ELSE 'CONSTRAINT' END AS Level2Type , ctecpx.ProcedureName AS ProcedureName , MIN(ctecpx.PlanUseCount) AS PlanUseCountMin , MAX(ctecpx.PlanUseCount) AS PlanUseCountMax , SUM(ctecpx.PlanUseCount) AS PlanUseCountSum FROM cteCachedPlansXml ctecpx JOIN sys.dm_exec_query_stats qs ON ctecpx.plan_handle = qs.plan_handle JOIN sys.indexes i ON OBJECT_ID(ctecpx.TableName) = i.object_id AND INDEXPROPERTY(OBJECT_ID(ctecpx.TableName), SUBSTRING(ctecpx.IndexName, 2, LEN(ctecpx.IndexName) - 2), 'IndexId') = i.index_id JOIN sys.tables t ON i.object_id = t.object_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE st.dbid = DB_ID() AND ctecpx.DatabaseName = QUOTENAME(DB_NAME()) AND ctecpx.SchemaName != QUOTENAME('sys') AND ctecpx.SchemaName IS NOT NULL AND ctecpx.TableName NOT LIKE '$[$#%' ESCAPE '$' AND ctecpx.TableName IS NOT NULL AND ctecpx.IndexName IS NOT NULL AND ctecpx.ProcedureName IS NOT NULL AND t.type_desc NOT IN ('SQL_SCALAR_FUNCTION') -- Added to as I find them. GROUP BY ctecpx.SchemaName , ctecpx.TableName , ctecpx.IndexName , ctecpx.ProcedureName ) , cteIndexExtendedProperties AS ( SELECT ctecp.SchemaName , ctecp.TableName , ctecp.IndexName , ctecp.IndexNameUnquoted , ctecp.ProcedureName , ctecp.PlanUseCountSum , ISNULL(CAST(ep.value AS NVARCHAR(3750)), '') AS ExtendedPropertyValue , ctecp.Level2Type AS Level2Type FROM cteCachedPlans ctecp LEFT JOIN sys.extended_properties ep ON ep.major_id = OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName, 'U') AND ep.minor_id = INDEXPROPERTY(OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName, 'U'), ctecp.IndexNameUnquoted, 'IndexId') WHERE ISNULL(ep.class_desc, 'INDEX') = 'INDEX' ) , ctePkExtendedProperties AS ( SELECT ctecp.SchemaName , ctecp.TableName , ctecp.IndexName , ctecp.IndexNameUnquoted , ctecp.ProcedureName , ctecp.PlanUseCountSum , ISNULL(CAST(ep.value AS NVARCHAR(3750)), '') AS ExtendedPropertyValue , ctecp.Level2Type AS Level2Type FROM cteCachedPlans ctecp LEFT JOIN sys.extended_properties ep ON ep.major_id = OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName + '.' + ctecp.IndexName, 'PK') AND ep.minor_id = 0 -- 0 means on the index, not on any columns. WHERE ISNULL(ep.class_desc, 'OBJECT_OR_COLUMN') = 'OBJECT_OR_COLUMN' ) , cteExtendedProperties AS ( SELECT cteiep.SchemaName AS SchemaName , cteiep.TableName AS TableName , cteiep.IndexName AS IndexName , cteiep.ProcedureName AS ProcedureName , SUM(cteiep.PlanUseCountSum) AS PlanUseCount , MIN(cteiep.ExtendedPropertyValue) AS ExtendedPropertyValue , ISNULL(cteiep.Level2Type, ctepkep.Level2Type) AS Level2Type FROM cteIndexExtendedProperties cteiep LEFT JOIN ctePkExtendedProperties ctepkep ON cteiep.SchemaName = ctepkep.SchemaName AND cteiep.TableName = ctepkep.TableName AND cteiep.IndexName = ctepkep.IndexName AND cteiep.ProcedureName = ctepkep.ProcedureName GROUP BY cteiep.SchemaName, cteiep.TableName, cteiep.IndexName, cteiep.ProcedureName, ISNULL(cteiep.Level2Type, ctepkep.Level2Type) ) SELECT SchemaName , TableName , IndexName , ProcedureName , PlanUseCount , ExtendedPropertyValue , Level2Type FROM cteExtendedProperties ORDER BY SchemaName , TableName , IndexName , ProcedureName , PlanUseCount , ExtendedPropertyValue , Level2Type ------------------------------------------------------------------------------- -- Now that we've created the SELECT statement, iterate it through a cursor. ------------------------------------------------------------------------------- OPEN cur RAISERROR('Found %d query plans with index usage in the cache', 10, 1, @@CURSOR_ROWS) WITH NOWAIT FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY -- Build some values we're going to need. SET @Len = LEN(@ProcedureName) SET @ExtendedPropertyName = SUBSTRING(@ProcedureName, 2, @Len - 2) SET @Len = LEN(@SchemaName) SET @SchemaNameUnquoted = SUBSTRING(@SchemaName, 2, @Len - 2) SET @Len = LEN(@TableName) SET @TableNameUnquoted = SUBSTRING(@TableName, 2, @Len - 2) SET @Len = LEN(@IndexName) SET @IndexNameUnquoted = SUBSTRING(@IndexName, 2, @Len - 2) SET @Len = LEN(@Level2Type) SET @Level2Type = @Level2Type + SPACE(LEN('CONSTRAINT') - @Len) SET @ExtendedPropertyValueOld = @ExtendedPropertyValue SET @PlanUseCountString = REPLACE(CONVERT(NVARCHAR(50), CAST(@PlanUseCount AS MONEY), 1), '.00', '') SET @PlanUseCountString = SPACE(10 - LEN(@PlanUseCountString)) + @PlanUseCountString SET @ExtendedPropertyValue = 'Used this ' + LOWER(@Level2Type) + ' ' + @PlanUseCountString SET @ExtendedPropertyValue += CASE @PlanUseCount WHEN 1 THEN ' time' ELSE ' times' END SET @ExtendedPropertyValue += ' (' + @Now + ')' -- If the index name is new, create a display heading for it; just for readability. IF @IndexName != @IndexNamePrev BEGIN SET @IndexNamePrev = @IndexName SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted RAISERROR(' ', 10, 1) WITH NOWAIT RAISERROR(@Msg, 10, 1) WITH NOWAIT END -- If the value hasn't changed, nothing to do. BEGIN TRAN IF @ExtendedPropertyValue = @ExtendedPropertyValueOld BEGIN RAISERROR(' Unchanged: %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT SET @UnchangedCount += 1 END ELSE IF EXISTS (SELECT * FROM sys.extended_properties ep WHERE ep.class_desc = 'INDEX' AND ep.name = @ExtendedPropertyName AND ep.major_id = OBJECT_ID(@SchemaNameUnquoted + '.' + @TableNameUnquoted, 'U') AND ep.minor_id = INDEXPROPERTY(OBJECT_ID(@SchemaNameUnquoted + '.' + @TableNameUnquoted, 'U'), @IndexNameUnquoted, 'IndexId') ) OR EXISTS (SELECT * FROM sys.extended_properties ep LEFT JOIN sys.key_constraints kc ON ep.major_id = kc.parent_object_id AND ep.minor_id = 0 WHERE ep.class_desc = 'OBJECT_OR_COLUMN' AND ep.name = @ExtendedPropertyName AND ep.major_id = OBJECT_ID(@SchemaNameUnquoted + '.' + @IndexNameUnquoted, 'PK') ) BEGIN -- Update any existing one. EXEC @Error = sys.sp_updateextendedproperty @name = @ExtendedPropertyName, @value = @ExtendedPropertyValue, @level0type = 'SCHEMA', @level0name = @SchemaNameUnquoted, @level1type = 'TABLE', @level1name = @TableNameUnquoted, @level2type = @Level2Type, @level2name = @IndexNameUnquoted IF @Error = 0 BEGIN SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted RAISERROR(' Updated: %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT SET @UpdatedCount += 1 END ELSE BEGIN SET @Msg = ISNULL(ERROR_MESSAGE(), '{ No error }') RAISERROR(@Msg, 16, 1) WITH NOWAIT, LOG END END ELSE BEGIN -- Write extended property. EXEC @Error = sys.sp_addextendedproperty @name = @ExtendedPropertyName, @value = @ExtendedPropertyValue, @level0type = 'SCHEMA', @level0name = @SchemaNameUnquoted, @level1type = 'TABLE', @level1name = @TableNameUnquoted, @level2type = @Level2Type, @level2name = @IndexNameUnquoted IF @Error = 0 BEGIN SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted RAISERROR(' Added: %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT SET @AddedCount += 1 END ELSE BEGIN SET @Msg = ISNULL(ERROR_MESSAGE(), '{ No error }') RAISERROR(@Msg, 16, 42) WITH NOWAIT, LOG END END -- Done with this extended property. COMMIT END TRY BEGIN CATCH -- MS bug: if sp_addextendedproperty or sp_dropextendedproperty throw an exception, they -- leave a transaction open. You can verify this by examining the @@TRANCOUNT value. SET @Msg = ' *** ERROR ' + CAST(@@ERROR AS NVARCHAR) + ': ' + ISNULL(ERROR_MESSAGE(), '{ No error }') + CHAR(10) SET @Msg += ' The @@TRANCOUNT is ' + CAST(@@TRANCOUNT AS NVARCHAR) + CHAR(10) + ' ' -- If 1, the transaction is committable. -- If -1, the transaction is uncommittable and should be rolled back. -- If 0, there is no transaction and a commit or rollback operation would generate an error. IF XACT_STATE() = -1 BEGIN SET @Msg += 'The transaction is in an uncommittable state. Rolling back all transactions.' ROLLBACK END ELSE IF XACT_STATE() = 1 BEGIN SET @Msg += 'The transaction is committable. Committing transaction.' COMMIT END -- If this is a deadlock, the rest of the indexes on this table will probably deadlock too. -- Skip to the next table. IF ERROR_NUMBER() = 1205 BEGIN WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type IF QUOTENAME(@TableNameUnquoted) != @TableName BREAK RAISERROR(' Skipping %s.%s.%s to get to the next table', 10, 1, @SchemaName, @TableName, @IndexName) END END ELSE BEGIN -- If it wasn't a deadlock, something else bad must have happenned, so we don't want to -- continue - this will cause an abort that displays the error message. RAISERROR(@Msg, 16, 1) WITH NOWAIT, LOG END END CATCH -- Next row. FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type END -- Close and deallocate the cursor. CLOSE cur DEALLOCATE cur RAISERROR(' ', 10, 1) WITH NOWAIT RAISERROR('Done - Extended properties: added %d new, updated %d existing, %d unchanged', 10, 1, @AddedCount, @UpdatedCount, @UnchangedCount) WITH NOWAIT, LOG GO
0 comments:
Post a Comment