Design Smarter Indexes

A helpful script for investigating indexes, thanks to Brent Ozar Unlimited.

Code Snippet

1. /**********************
2. This is a demo script from http://brentozar.com
3. Scripts provided for testing/demo purposes only.
4. This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
5. http://creativecommons.org/licenses/by-nc-sa/3.0/
6. ***********************/
7.  
8. /**********************
9. 0. Preliminary Check
10. How long has our instance been up?
11. We want to know to contextualize the number of times the index was needed.
12. **********************/
13. select cast(datediff(hh,sqlserver_start_time,GETDATE())/24.0 as numeric(10,1)) as [Days Uptime]
14. from sys.dm_os_sys_info
15.  
16. --What does this mean for missing index data?
18. /**********************
19. 1. Review missing indexes.
20. Let's create a view to look at them.
21. ***********************/
22. if object_id('dbo.MissingIndexes') is null
23.    exec sp_executesql N'create view dbo.MissingIndexes as select ''''as Stub'
24. GO
25.  
26. ALTER VIEW dbo.MissingIndexes
27. AS
28. SELECT    id.statement,
29.         cast(gs.avg_total_user_cost * gs.avg_user_impact * ( gs.user_seeks + gs.user_scans )as int) AS Impact,
30.         cast(gs.avg_total_user_cost as numeric(10,2)) as [Average Total Cost],
31.         cast(gs.avg_user_impact as int) as [% Reduction of Cost],
32.         gs.user_seeks + gs.user_scans as [Missed Opportunities],
33.         id.equality_columns as [Equality Columns],
34.         id.inequality_columns as [Inequality Columns],
35.         id.included_columns as [Included Columns]
36.         FROM sys.dm_db_missing_index_group_stats AS gs
37.         JOIN sys.dm_db_missing_index_groups AS ig ON gs.group_handle = ig.index_group_handle
38.         JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
39. GO
40.  
41. --Check out your view
42. SELECT * FROM dbo.MissingIndexes
43. ORDER BY Impact desc
44.  
45. /**********************
46. 2. Make a list of the columns which look interesting
47. ***********************/
49. /**********************
50. 3. Review indexes already on the table.
51. This will give you a sense of the current 'weight' of indexes for the table.
52. Look at urgent impact values of 1,000,000+
53. Investigate impact values of 20,000+
54. ***********************/
56. SELECT    OBJECT_NAME(ps.object_id) AS object_name,
57.         ps.index_id,
58.         ISNULL(si.name, '(heap)') AS index_name,
59.         CAST(ps.reserved_page_count * 8 / 1024.0 / 1024.0 AS NUMERIC(10, 2)) AS reserved_GB,
60.         ps.row_count,
61.         ps.partition_number,
62.         ps.in_row_reserved_page_count,
63.         ps.lob_reserved_page_count,
64.         ps.row_overflow_reserved_page_count
65. FROM
66. sys.dm_db_partition_stats ps
67. LEFT JOIN sys.indexes AS si ON ps.object_id = si.object_id AND ps.index_id = si.index_id
68. WHERE
69. OBJECT_NAME(ps.object_id) = 'ENTER_DATABASE_NAME_HERE'
70.  
71. --Check for indexes already on the table which may combine with your desired index.
73. --For example, there may be an existing index which you can add key columns or includes to,
74. --That's always better than creating a new index!
75. --What is our table keyed on?
77. exec sp_helpindex [ENTER_DATABASE_NAME_HERE]
79. /**********************
80. 4. Create an index - if needed
81. ***********************/
82.  
83. --What did this do to our missing index recommendations?
84. SELECT *
85. FROM dbo.MissingIndexes
86. ORDER BY Impact desc
88. --Wait for some time to pass...
90. --Queries are running marathons, taking long walks on beaches, and enjoying the sunset.
91. /**********************
92. 5. Review index usage
93. ***********************/
94. SELECT    o.name as [Object Name],
95.         s.index_id as [Index ID],
96.         ps.partition_number as [Partition Num],
97.         i.name as [Index Name],
98.         i.type_desc as [Index Type],
99.         s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read],
100.         s.user_updates [Total Queries Which Wrote],
101.         ps.row_count as [Row Count],
102.         CASE WHEN s.user_updates < 1 THEN 100
103.              ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0
104.         END AS [Reads Per Write]
105. FROM
106. sys.dm_db_index_usage_stats s
107. JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id and s.index_id=ps.index_id
108. JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
109. JOIN sys.objects o ON s.object_id = o.object_id
110. JOIN sys.schemas c ON o.schema_id = c.schema_id
111. WHERE
112. s.database_id=db_id()
113. and o.name = 'ENTER_DATABASE_NAME_HERE'
114.  
115. /**********************
116. 6. Do we still have missing indexes?
117. **********************/
119. SELECT *
120. FROM dbo.MissingIndexes
121. ORDER BY Impact desc

 -Source: http://www.brentozar.com/archive/2011/09/kendra-little-explains-how-design-smarter-indexes/