Home > Handy Scripts, Tuning and Optimization > View Index Usage Statistics

View Index Usage Statistics

Time to time the indexes have to be watched for. While there is quite an argument b/w many DBAs (well, Oracle for sure, but I truly believe it goes for SQL Server as well as others RDBMS too – Data Structures are more or less the same for all relational databases) whether index fragmentation notion is true as such, or not…

As to me, I don’t have (and never had, actually) any doubts about indexes going out of sync, becoming unbalanced, browning (call it whatever you want) with high number of DMLs. DELETEs result in index leafs being cleared out, UPDATEs with values greater than the old ones, generally might lead page movement inside the index, which is both expensive operation and, once again, leafs fragmented blocks “inside” the index leaf level. Hence, indexes do need to be either defragmented or rebuilt. It just depends of their fragmentation level. But here, for example, is quite a “clash of titans” about this argument. There are some high-pro DBAs to believe indexes kind of don’t need to be rebuilt or that index rebuild costs highly outwaight any performance benefits. At the end of the day – your choice. As to me, I’m on “rebuild side” – no question.

SQL Server 2005 introduced a number of Dynamic Views (in SS they are called Dynamic Management Objects) to help figuring the index stats out.

They are:

sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats

sys.dm_db_index_physical_stats

Joining each of them with sys.indexes and either sys.objects or sys.tables will give all the necessary info.

I took this code from two articles by Greg Robidoux and Greg Larsen as the starting point and created an SP which can be called with different parameters to sort the results by different columns.

--=============================================================--

IF  EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(‘dbo.sp_view_index_usage_stats’) AND TYPE IN (‘P’, ‘PC’))
DROP PROCEDURE [dbo].[sp_view_index_usage_stats]
GO

CREATE PROCEDURE sp_view_index_usage_stats (@p_sort_column smallint, @p_sort_option VARCHAR(5))
AS
SET NOCOUNT ON

DECLARE @v_sql VARCHAR(MAX);
BEGIN

SET @p_sort_option = UPPER(@p_sort_option);

SET @v_sql =
SELECT
SCHEMA_NAME(o.schema_id) as SCHEMA_NAME,
OBJECT_NAME(a.object_id) AS OBJECT_NAME,
–access
i.name AS INDEX_NAME,
i.type_desc as INDEX_TYPE,
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT,
A.LEAF_GHOST_COUNT,
A.RANGE_SCAN_COUNT,
A.SINGLETON_LOOKUP_COUNT,
–latching and locking
A.PAGE_LATCH_WAIT_COUNT,
A.PAGE_IO_LATCH_WAIT_IN_MS,
A.ROW_LOCK_COUNT,
A.PAGE_LOCK_COUNT,
–physical I/O
A.PAGE_IO_LATCH_WAIT_COUNT,
A.PAGE_IO_LATCH_WAIT_IN_MS
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID = I.OBJECT_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],”IsUserTable”) = 1
ORDER BY’
;

SET @v_sql = CASE @p_sort_column
–access
WHEN 1 THEN @v_sql + ‘ A.LEAF_INSERT_COUNT’
WHEN 2 THEN @v_sql + ‘ A.LEAF_UPDATE_COUNT’
WHEN 3 THEN @v_sql + ‘ A.LEAF_DELETE_COUNT’
WHEN 4 THEN @v_sql + ‘ a.leaf_ghost_count’
WHEN 5 THEN @v_sql + ‘ a.range_scan_count’
WHEN 6 THEN @v_sql + ‘ a.singleton_lookup_count’
–latching and locking
WHEN 7 THEN @v_sql + ‘ a.page_latch_wait_count’
WHEN 8 THEN @v_sql + ‘ a.page_io_latch_wait_in_ms’
WHEN 9 THEN @v_sql + ‘ a.row_lock_count’
WHEN 10 THEN @v_sql + ‘ a.page_lock_count’
–physical I/O
WHEN 11 THEN @v_sql + ‘ a.page_io_latch_wait_count’
WHEN 12 THEN @v_sql + ‘ a.page_io_latch_wait_in_ms’ END;

SET @v_sql = CASE @p_sort_option
WHEN ‘DESC’ THEN @v_sql + ‘ DESC’
WHEN ‘ASC’ THEN @v_sql + ‘ ASC’ END;

EXECUTE(@v_sql)

END;

–=============================================================–

EXEC sp_view_index_usage_stats 1, ‘desc’ –leaf_insert_count
EXEC sp_view_index_usage_stats 2, ‘desc’ –leaf_update_count
EXEC sp_view_index_usage_stats 3, ‘desc’ –leaf_delete_count
EXEC sp_view_index_usage_stats 4, ‘desc’ –leaf_ghost_count
EXEC sp_view_index_usage_stats 5, ‘desc’ –range_scan_count
EXEC sp_view_index_usage_stats 6, ‘desc’ –singleton_lookup_count
EXEC sp_view_index_usage_stats 7, ‘desc’ –page_latch_wait_count
EXEC sp_view_index_usage_stats 8, ‘desc’ –page_io_latch_wait_in_ms
EXEC sp_view_index_usage_stats 9, ‘desc’ –row_lock_count
EXEC sp_view_index_usage_stats 10, ‘desc’ –page_lock_count
EXEC sp_view_index_usage_stats 11, ‘desc’ –page_io_latch_wait_count
EXEC sp_view_index_usage_stats 12, ‘desc’ –page_io_latch_wait_in_ms

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: