Index Info

Categories: News, Professional, Scripts
Tags: , ,
Comments: 3 Comments
Published on: March 31, 2010

I recently came across an interesting script to help show index info and usage.  As I began to dissect the script, I noticed that the script was intended for SQL 2005 and beyond but was using SQL 2000 compatibility views.  I decided I would take it upon myself to update the script to use the 2005 objects and not the compatibility objects.

The design of the script is to give a basic view of the usage of the index and then try to evaluate the cost of that index.  If the index updates far outweigh the queries that use it, then the index may not be a useful index.  Furthermore, the index cost probably outweighs its use.

The original query employed the use of dbo.sysobjects and dbo.sysindexes.  The reason for the use of dbo.sysindexes was merely to calculate the size of the index.  The use of dbo.sysobjects seemed like an oversight.  The area that I ran into a sticky point was with calculating the size of the index.  The problem was in figuring out a method to execute the entire query and have it perform as well as the original query.  The first attempt yielded a query that was absolutely abysmal.

WITH EqColumns AS (
SELECT OBJECT_ID,index_id,COUNT(index_id) AS IndCount
	FROM sys.index_columns 
	WHERE is_included_column = 0
	GROUP BY OBJECT_ID,Index_id
), IncludeColumns AS (
SELECT OBJECT_ID,index_id,COUNT(index_id) AS IndCount
	FROM sys.index_columns 
	WHERE is_included_column = 1
	GROUP BY OBJECT_ID,Index_id
)
 
SELECT OBJECT_NAME(a.OBJECT_ID) AS TableName
	, c.name AS IndexName
	, c.type_desc IndexType
	, CASE c.is_unique 
		WHEN 1 THEN 
			CASE is_primary_key 
				WHEN 1 THEN 'Primary Key'
				ELSE 'Unique'
			END 
		ELSE 
			CASE c.is_unique_cOnstraint 
				WHEN 1 THEN 'Unique Constraint' 
				ELSE 'Performance'
			END 
		END 'IndexUsage'
	, e.name 'FileGroup'
	, (	SELECT CONVERT(DECIMAL(16,1),(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 1024)))
	FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps
	WHERE ps.database_id = a.database_id and ps.index_id = a.index_id)  AS IndexSizeMB
	, isnull(ec.IndCount,0) AS EqualityColumns
	, isnull(ic.IndCount,0) AS IncludedColumns
	, (a.user_seeks + a.user_scans + a.user_lookups) AS UserRequests
	, (a.user_updates) AS UserUpdates
	, a.last_user_update AS LastUpdate
	, CAST(a.user_seeks + a.user_scans + a.user_lookups AS REAL) / CAST(CASE a.user_updates WHEN 0 THEN 1 ELSE a.user_updates END AS REAL) AS RatioRequestsToUpdates
	FROM sys.dm_db_index_usage_stats a
		--Inner Join #indstats ps
		--	On a.index_id = ps.index_id
		--	And a.database_id = ps.database_id
		INNER Join sys.objects AS o
			ON (a.OBJECT_ID = o.OBJECT_ID)
		INNER Join sys.indexes AS c
			ON (a.OBJECT_ID = c.OBJECT_ID and a.index_id = c.index_id)
		INNER Join sys.filegroups e
			ON c.data_space_id = e.data_space_id
		LEFT Outer Join EqColumns ec
			ON a.OBJECT_ID = ec.OBJECT_ID
			And a.index_id = ec.index_id
		LEFT Outer Join IncludeColumns ic
			ON a.OBJECT_ID = ic.OBJECT_ID
			And a.index_id = ic.index_id
	WHERE o.type = 'U'							-- exclude system tables
		And c.type <> 0							-- exclude HEAPs
		And c.is_disabled = 0					-- Only active indexes
		And a.database_id = DB_ID()				-- for current database Only
	ORDER BY RatioRequestsToUpdates DESC

The method I am using to calculate the size whilst using the SQL 2005 objects was to use the function sys.dm_db_index_physical_stats.  I employed this directly as a subquery in the above posted query.  This version takes in excess of 7 minutes to return results.  I moved on from it to find a better method.

The next attempt was to move the subquery into a CTE.

WITH indexstats AS (
SELECT database_id,index_id,CONVERT(DECIMAL(16,1),(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 1024))) AS IndexSizeMB
	FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps
GROUP BY database_id,index_id
), EqColumns AS (
SELECT OBJECT_ID,index_id,COUNT(index_id) AS IndCount
	FROM sys.index_columns 
	WHERE is_included_column = 0
	GROUP BY OBJECT_ID,Index_id
), IncludeColumns AS (
SELECT OBJECT_ID,index_id,COUNT(index_id) AS IndCount
	FROM sys.index_columns 
	WHERE is_included_column = 1
	GROUP BY OBJECT_ID,Index_id
)
 
SELECT OBJECT_NAME(a.OBJECT_ID) AS TableName
	, c.name AS IndexName
	, c.type_desc IndexType
	, CASE c.is_unique 
		WHEN 1 THEN 
			CASE is_primary_key 
				WHEN 1 THEN 'Primary Key'
				ELSE 'Unique'
			END 
		ELSE 
			CASE c.is_unique_cOnstraint 
				WHEN 1 THEN 'Unique Constraint' 
				ELSE 'Performance'
			END 
		END 'IndexUsage'
	, e.name 'FileGroup'
	, ps.IndexSizeMB
	, isnull(ec.IndCount,0) AS EqualityColumns
	, isnull(ic.IndCount,0) AS IncludedColumns
	, (a.user_seeks + a.user_scans + a.user_lookups) AS UserRequests
	, (a.user_updates) AS UserUpdates
	, a.last_user_update AS LastUpdate
	, CAST(a.user_seeks + a.user_scans + a.user_lookups AS REAL) / CAST(CASE a.user_updates WHEN 0 THEN 1 ELSE a.user_updates END AS REAL) AS RatioRequestsToUpdates
	FROM sys.dm_db_index_usage_stats a
		INNER Join indexstats ps
			ON a.index_id = ps.index_id
			And a.database_id = ps.database_id
		INNER Join sys.objects AS o
			ON (a.OBJECT_ID = o.OBJECT_ID)
		INNER Join sys.indexes AS c
			ON (a.OBJECT_ID = c.OBJECT_ID and a.index_id = c.index_id)
		INNER Join sys.filegroups e
			ON c.data_space_id = e.data_space_id
		LEFT Outer Join EqColumns ec
			ON a.OBJECT_ID = ec.OBJECT_ID
			And a.index_id = ec.index_id
		LEFT Outer Join IncludeColumns ic
			ON a.OBJECT_ID = ic.OBJECT_ID
			And a.index_id = ic.index_id
	WHERE o.type = 'U'							-- exclude system tables
		And c.type <> 0							-- exclude HEAPs
		And c.is_disabled = 0					-- Only active indexes
		And a.database_id = DB_ID()				-- for current database Only
	ORDER BY RatioRequestsToUpdates DESC

This version returns in about thirty seconds.  Still not acceptable but substantially better.  The original query returned in roughly three seconds.  It is also important to note that the accuracy of both queries is equal.  Since both return the same result set, the only focus is on performance.  The delay in this query completely revolves around the use of the function.  Armed with the knowledge that the real delay is in the use of that function, there needs to be alternative method to pull back that information.  I decided to use a temp table to stage the data.  Using this temp table I could test directly the efficiency of the query.

CREATE TABLE #indstats (indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,database_id BIGINT,index_id BIGINT,IndexSizeMB DECIMAL(16,1))
 
INSERT INTO #indstats (database_id,index_id,IndexSizeMB)
	SELECT database_id,index_id,CONVERT(DECIMAL(16,1),(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 1024))) AS IndexSizeMB
	FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps
	GROUP BY database_id,index_id 
;
WITH EqColumns AS (
SELECT OBJECT_ID,index_id,COUNT(index_id) AS IndCount
	FROM sys.index_columns 
	WHERE is_included_column = 0
	GROUP BY OBJECT_ID,Index_id
), IncludeColumns AS (
SELECT OBJECT_ID,index_id,COUNT(index_id) AS IndCount
	FROM sys.index_columns 
	WHERE is_included_column = 1
	GROUP BY OBJECT_ID,Index_id
)
 
SELECT OBJECT_NAME(a.OBJECT_ID) AS TableName
	, c.name AS IndexName
	, c.type_desc IndexType
	, CASE c.is_unique 
		WHEN 1 THEN 
			CASE is_primary_key 
				WHEN 1 THEN 'Primary Key'
				ELSE 'Unique'
			END 
		ELSE 
			CASE c.is_unique_cOnstraint 
				WHEN 1 THEN 'Unique Constraint' 
				ELSE 'Performance'
			END 
		END 'IndexUsage'
	, e.name 'FileGroup'
	, ps.IndexSizeMB
	, isnull(ec.IndCount,0) AS EqualityColumns
	, isnull(ic.IndCount,0) AS IncludedColumns
	, (a.user_seeks + a.user_scans + a.user_lookups) AS UserRequests
	, (a.user_updates) AS UserUpdates
	, a.last_user_update AS LastUpdate
	, CAST(a.user_seeks + a.user_scans + a.user_lookups AS REAL) / CAST(CASE a.user_updates WHEN 0 THEN 1 ELSE a.user_updates END AS REAL) AS RatioRequestsToUpdates
	FROM sys.dm_db_index_usage_stats a
		INNER Join #indstats ps
			ON a.index_id = ps.index_id
			And a.database_id = ps.database_id
		INNER Join sys.objects AS o
			ON (a.OBJECT_ID = o.OBJECT_ID)
		INNER Join sys.indexes AS c
			ON (a.OBJECT_ID = c.OBJECT_ID and a.index_id = c.index_id)
		INNER Join sys.filegroups e
			ON c.data_space_id = e.data_space_id
		LEFT Outer Join EqColumns ec
			ON a.OBJECT_ID = ec.OBJECT_ID
			And a.index_id = ec.index_id
		LEFT Outer Join IncludeColumns ic
			ON a.OBJECT_ID = ic.OBJECT_ID
			And a.index_id = ic.index_id
	WHERE o.type = 'U'							-- exclude system tables
		And c.type <> 0							-- exclude HEAPs
		And c.is_disabled = 0					-- Only active indexes
		And a.database_id = DB_ID()				-- for current database Only
	ORDER BY RatioRequestsToUpdates DESC
 
DROP TABLE #indstats

The population of the temp table consumes 97% of the query cost.  The prior version of the query also showed a cost of 97% associated directly with the function.  If I populate the temp table prior to the execution of the remainder of the script, then I can compare performance of this script versus the original.  When comparing, this version runs in about 1 second.

The next steps from here would be to employ a method that populates a table in an “Admin” database.  By using a pre-populated table I can maintain good performance to gain the information concerning Index Usage.

3 Comments - Leave a comment
  1. [...] This post was mentioned on Twitter by Jason Brimhall. Jason Brimhall said: New post: Index Info (http://bit.ly/8ZU1MN) [...]

  2. [...] This new idea was born out of necessity for me about a year ago.  I was having a hard time finding why my primary file group was still so large after moving all of the User objects out of that filegroup and into new filegroups.  I couldn’t find the tying factor – until I checked for BLOBs that were created in the Primary Filegroup by default.  To move them was a manual process and is a different topic from the crux of this one.  Since the initial report to find the BLOBs, I have evolved it somewhat to try and find the size of each object, as well as to report on the usage related with each.  To do this, I needed to use a method I posted about in a different post about finding some Index Information. [...]

  3. See SQLServercentral from some changes I made to this (original) script.
    Always nice to see how we (the community) help each other!

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
March 2010
M T W T F S S
« Feb   Apr »
1234567
891011121314
15161718192021
22232425262728
293031  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, April 20, 2014