For Day 28 of this series, we start out with Query #63, which is Overall Index Usage – Reads. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by reads. Query #63 is shown in Figure 1.
1: --- Index Read/Write stats (all tables in current DB) ordered by Reads (Query 63) (Overall Index Usage - Reads)
2: SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id,
3: s.user_seeks, s.user_scans, s.user_lookups,
4: s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
5: s.user_updates AS [Writes],
6: i.type_desc AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,
7: s.last_user_scan, s.last_user_lookup, s.last_user_seek
8: FROM sys.indexes AS i WITH (NOLOCK)
9: LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
10: ON i.[object_id] = s.[object_id]
11: AND i.index_id = s.index_id
12: AND s.database_id = DB_ID()
13: WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
14: ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC OPTION (RECOMPILE); -- Order by reads
15:
16:
17: -- Show which indexes in the current database are most active for Reads
Figure 1: Query #63 Overall Index Usage – Reads
This query shows you which indexes in the current database have the most cumulative reads (including seeks, scans and lookups) since the instance was last restarted or the index was created. This helps you understand your workload, and shows you which indexes are the most valuable for your workload. Another use for this query is to help identify possible data compression candidates. If you find an index on a large table with a high number of reads and a low number of writes, then it might be a good candidate for data compression if the data is highly compressible.
Query #64 is Overall Index Usage – Writes. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by writes. Query #64 is shown in Figure 2.
1: --- Index Read/Write stats (all tables in current DB) ordered by Writes (Query 64) (Overall Index Usage - Writes)
2: SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id,
3: s.user_updates AS [Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
4: i.type_desc AS [Index Type], i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,
5: s.last_system_update, s.last_user_update
6: FROM sys.indexes AS i WITH (NOLOCK)
7: LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
8: ON i.[object_id] = s.[object_id]
9: AND i.index_id = s.index_id
10: AND s.database_id = DB_ID()
11: WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
12: ORDER BY s.user_updates DESC OPTION (RECOMPILE); -- Order by writes
13:
14: -- Show which indexes in the current database are most active for Writes
Figure 2: Query #64 Overall Index Usage – Writes
This query shows you which indexes in the current database have the most cumulative writes since the instance was last restarted or the index was created. This helps you understand your workload, and shows you which indexes are the most volatile in your workload. Another use for this query is to help identify possible indexes that you might consider dropping. If you find a non-clustered, non-key index on a table with a high number of writes and a very low number of reads, then you might want to drop that index, after doing some further investigation. You want to make sure that your instance has been running long enough so that you have seen your complete workload so that you don’t drop an index that is actually needed for queries that have not run yet.