Find Missing Indexes before going into production
Find missing indexes the right way! First of all, finding a missing index is not a rocket science. Always find and create missing indexes before you deploy the code to the production environment.
DBA’s and engineers try their best to keep the database in a good state by defragging indexes, updating statistics and by performing various other database operation to improve query performance.
However, to find missing indexes is not as easy as querying a simple table or executing a stored procedure.
Furthermore, many clients always complain about poorly performing queries. This is probably one of the top 10 issues reported by clients. And probably most of the query performance issues stem from missing indexes.
Therefore, in this article, we will look at how to find missing indexes.
Find and create any missing indexes
No matter how powerful your server is, with large amounts of data, it is even more important to make sure your queries are tuned and that the underlying tables have proper indexes to help boost query performance.
Therefore, the below script provides you with a way to find missing indexes for a specific database and also helps you with exact commands to create those missing indexes.
You can automate this script to check across all your user databases on a production server.
Finally, this script helps answer your question on how to find missing indexes. This is a very useful and most accurate and noteworthy script that can be used for identifying and creating missing indexes.
SQL Server Best Practices Script provided by: HTTPS://SQLOPS.COM The Most Advanced Risk and Health Audit for Production Databases
SELECT UPPER(DB_Name()) as 'DATABASE', Object_Name(SQLOPS_MsgIdxDetails.object_id) as 'OBJECT NAME', Schema_Name(SQLOPS_SysObj.schema_id) as 'SCHEMA NAME', 'CREATE INDEX '+DB_Name()+'_SQLOPS_'+ Object_Name(SQLOPS_MsgIdxDetails.object_id)+'_' + CONVERT (varchar, SQLOPS_MsgIdxGrp.index_group_handle) + '_' + CONVERT (varchar, SQLOPS_MsgIdxDetails.index_handle) + ' ON ' + SQLOPS_MsgIdxDetails.statement + ' (' + ISNULL (SQLOPS_MsgIdxDetails.equality_columns,'') + CASE WHEN SQLOPS_MsgIdxDetails.equality_columns IS NOT NULL AND SQLOPS_MsgIdxDetails.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (SQLOPS_MsgIdxDetails.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + SQLOPS_MsgIdxDetails.included_columns + ');', '') AS 'CREATE INDEX COMMAND', --Online Index will work only if you have Enterprise edition Cast(round(SQLOPS_MsgIdxGrpStats.avg_total_user_cost,2) as varchar)+'%' as 'ESTIMATED CURRENT COST', Cast(SQLOPS_MsgIdxGrpStats.avg_user_impact as varchar)+'%' as 'CAN BE IMPROVED', SQLOPS_MsgIdxGrpStats.last_user_seek as 'LAST USER SEEK', 'SCRIPT PROVIDED BY HTTPS://SQLOPS.COM' as 'CREDITS' FROM sys.dm_db_missing_index_groups AS SQLOPS_MsgIdxGrp INNER JOIN sys.dm_db_missing_index_group_stats AS SQLOPS_MsgIdxGrpStats ON SQLOPS_MsgIdxGrpStats.group_handle = SQLOPS_MsgIdxGrp.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS SQLOPS_MsgIdxDetails ON SQLOPS_MsgIdxGrp.index_handle = SQLOPS_MsgIdxDetails.index_handle INNER JOIN sys.objects as SQLOPS_SysObj ON SQLOPS_MsgIdxDetails.object_id = SQLOPS_SysObj.object_id ORDER BY 4 desc*Note: Most of the numbers are estimated
Database – Name of the database where indexes are missing
Object Name – Name of the object on which the index is missing
Schema Name – Name of the schema that owns the object
Create Index Command – Command to create missing indexes
Estimated Current Cost – The current cost of the query performance with index missing.
Can be improved – Percentage of improvement as a result of creating the missing index.
Last User Seek – Date and time the query last fetched the data
Note: Above practice is just one of the several thousands of audit checks performed by SQLOPS Risk and Health Assessment for Production Databases. Your investment is worth every penny! 100% risk-free with the money back guarantee.
