Find missing indexes and improve query performance

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.

Credits: https://sqlops.com/sql-server-risk-assessment/

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…