SQL Script to find the missing indexes

Script to find the missing indexes

Performance tuning in SQL is important exercise and index creation is an important part of it. Sometimes base on the frequent SQL queries, we need to create some indexes or missing indexes which are enhance the query performance. Below script will help in finding the missing indexes.

SELECT db_name(d.database_id) dbname
, object_name(d.object_id) tablename
, d.equality_columns
, d.inequality_columns
, d.included_columns
,'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle)
 + '_' + CONVERT (varchar, d.index_handle)
+ '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'
+ ' ON ' + d.statement
+ ' (' + ISNULL (d.equality_columns,'')
+ CASE WHEN d.equality_columns IS NOT NULL 
AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (d.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
FROM  sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
WHERE  d.database_id =  'Database_id' and d.object_id =  d.object_id
ORDER BY 2 DESC

We need to pass the id (Database_id) of the database in the above query. This id of the database can be get with the help of the below query. This query returns the id of all the databases deployed on the server in which this query is executed.

select * from sys.databases

Please note:-

Please run the missing index SQL query on the development server or the test server first before running on the Production server. Also too many indexes on a table can adversely impact the query performance. So before creating the indexes return by the above query, first create the suggested missing indexes on the development database server or the UAT database server and see its effect on the query performance. If you got satisfied with performance increase then only execute the index creation script for missing indexes on the Production server.

 

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in DBA, SQL Advanced, SQL Indexes, SQL Server and tagged , . Bookmark the permalink.

29 Responses to SQL Script to find the missing indexes

  1. best essays says:

    Thank you for share the wonderful and good post.

  2. Pingback: lapak qq

  3. Pingback: victory tailgate

  4. Pingback: xemtructiepbongda

  5. Pingback: https://eksperci.edu.pl/

  6. Pingback: Ts911

  7. Pingback: New Build Property Investment Alicante

  8. Pingback: Dream Market Exit Scam

  9. Pingback: fun88

  10. Pingback: บาคาร่าออนไลน์

  11. Pingback: ฉีดฟิลเลอร์

  12. Pingback: buy cbd oil

  13. Pingback: dubai9999

  14. Pingback: Online magazine about events in the world of investment.

  15. Pingback: mega poker99

  16. Pingback: kompas qq

  17. Pingback: naya santali video

  18. Pingback: uruguai turismo

  19. Pingback: daftar warnetqq

  20. Pingback: sahabat qq

  21. Pingback: www.rajaqqtop.com

  22. Pingback: dang ky fun88

  23. Pingback: XE88 Download For Android And iOS Iphone

  24. Pingback: bookcase

  25. Pingback: paito sgp

  26. Pingback: fashion show backdrop

  27. Pingback: Skrotning av bil

  28. Pingback: mejaqq

  29. Pingback: fowling board decals

Leave a Reply