About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, February 21, 2013

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?
  17. /**********************
  18. 1. Review missing indexes.
  19. Let's create a view to look at them.
  20. ***********************/
  21. if object_id('dbo.MissingIndexes') is null
  22.    exec sp_executesql N'create view dbo.MissingIndexes as select ''''as Stub'
  23. GO
  24.  
  25. ALTER VIEW dbo.MissingIndexes
  26. AS
  27. SELECT    id.statement,
  28.         cast(gs.avg_total_user_cost * gs.avg_user_impact * ( gs.user_seeks + gs.user_scans )as int) AS Impact,
  29.         cast(gs.avg_total_user_cost as numeric(10,2)) as [Average Total Cost],
  30.         cast(gs.avg_user_impact as int) as [% Reduction of Cost],
  31.         gs.user_seeks + gs.user_scans as [Missed Opportunities],
  32.         id.equality_columns as [Equality Columns],
  33.         id.inequality_columns as [Inequality Columns],
  34.         id.included_columns as [Included Columns]
  35.         FROM sys.dm_db_missing_index_group_stats AS gs
  36.         JOIN sys.dm_db_missing_index_groups AS ig ON gs.group_handle = ig.index_group_handle
  37.         JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
  38. GO
  39.  
  40. --Check out your view
  41. SELECT * FROM dbo.MissingIndexes
  42. ORDER BY Impact desc
  43.  
  44. /**********************
  45. 2. Make a list of the columns which look interesting
  46. ***********************/
  47. /**********************
  48. 3. Review indexes already on the table.
  49. This will give you a sense of the current 'weight' of indexes for the table.
  50. Look at urgent impact values of 1,000,000+
  51. Investigate impact values of 20,000+
  52. ***********************/
  53. SELECT    OBJECT_NAME(ps.object_id) AS object_name,
  54.         ps.index_id,
  55.         ISNULL(si.name, '(heap)') AS index_name,
  56.         CAST(ps.reserved_page_count * 8 / 1024.0 / 1024.0 AS NUMERIC(10, 2)) AS reserved_GB,
  57.         ps.row_count,
  58.         ps.partition_number,
  59.         ps.in_row_reserved_page_count,
  60.         ps.lob_reserved_page_count,
  61.         ps.row_overflow_reserved_page_count
  62. FROM
  63. sys.dm_db_partition_stats ps
  64. LEFT JOIN sys.indexes AS si ON ps.object_id = si.object_id AND ps.index_id = si.index_id
  65. WHERE
  66. OBJECT_NAME(ps.object_id) = 'ENTER_DATABASE_NAME_HERE'
  67.  
  68. --Check for indexes already on the table which may combine with your desired index.
  69. --For example, there may be an existing index which you can add key columns or includes to,
  70. --That's always better than creating a new index!
  71. --What is our table keyed on?
  72. exec sp_helpindex [ENTER_DATABASE_NAME_HERE]
  73. /**********************
  74. 4. Create an index - if needed
  75. ***********************/
  76.  
  77. --What did this do to our missing index recommendations?
  78. SELECT *
  79. FROM dbo.MissingIndexes
  80. ORDER BY Impact desc
  81. --Wait for some time to pass...
  82. --Queries are running marathons, taking long walks on beaches, and enjoying the sunset.
  83. /**********************
  84. 5. Review index usage
  85. ***********************/
  86. SELECT    o.name as [Object Name],
  87.         s.index_id as [Index ID],
  88.         ps.partition_number as [Partition Num],
  89.         i.name as [Index Name],
  90.         i.type_desc as [Index Type],
  91.         s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read],
  92.         s.user_updates [Total Queries Which Wrote],
  93.         ps.row_count as [Row Count],
  94.         CASE WHEN s.user_updates < 1 THEN 100
  95.              ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0
  96.         END AS [Reads Per Write]
  97. FROM
  98. sys.dm_db_index_usage_stats s
  99. JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id and s.index_id=ps.index_id
  100. JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
  101. JOIN sys.objects o ON s.object_id = o.object_id
  102. JOIN sys.schemas c ON o.schema_id = c.schema_id
  103. WHERE
  104. s.database_id=db_id()
  105. and o.name = 'ENTER_DATABASE_NAME_HERE'
  106.  
  107. /**********************
  108. 6. Do we still have missing indexes?
  109. **********************/
  110. SELECT *
  111. FROM dbo.MissingIndexes
  112. ORDER BY Impact desc

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


 

No comments: