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.
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
Thank you for share the wonderful and good post.
… [Trackback]
[…] Read More to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Find More to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Find More on to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Read More here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] There you will find 51622 more Info on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Find More Information here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Find More Information here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Read More Info here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Read More here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Here you will find 67332 more Info on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Read More Info here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Find More here to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Here you will find 62917 more Info to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] There you can find 18740 additional Info on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Find More here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Here you can find 37742 additional Information on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Read More here on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2015/01/sql-script-to-find-missing-indexes.html […]