Friday, November 4, 2011

Find Your Missing Indexes In SQL Server

The major tasks for all application DBA's like me, is to identifying the slowest running SQL queries and missing indexes and fix the issue..... huhuhuhu...... 

All performance problems are usually result of missing indexes and drawback of too many indexes is it will unnecessarily increases the database size.

On SQL Server 2005 got introduced Dynamic Management Views (DMV’s) which are very powerful tool for tvning the performance related issues. One of the drawbacks of this DMV's is most of them holding aggregate values since the last server restart.



When you run a SQL query, the SQL Server determines what indexes to be used here; if indexes are not found then it will make a note of it. Through DMV's we can get these information and we have to take action accordingly.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 10 
ROUND(A.avg_total_user_cost *
       A.avg_user_impact
        * (A.user_seeks + A.user_scans),0)
                 AS [Total Cost]
 ,C.[statement] AS [Table Name]
 ,equality_columns
 ,inequality_columns
 ,included_columns


FROM sys.dm_db_missing_index_groups B
INNER JOIN sys.dm_db_missing_index_group_stats A
ON A.group_handle = B.index_group_handle
INNER JOIN sys.dm_db_missing_index_details C
ON C.index_handle = B.index_handle
ORDER BY [Total Cost] DESC
GO

In above query I used 3 DMV’s to pull the missing indexes in the server.
1. sys.dm_db_missing_index_details  (This DMV Contains details of the database, schema, and table. The missing index relates to how the index usage has been identified in queries such as equality or inequality.)

2. sys.dm_db_missing_index_group_stats (This DMV Contains details of how often the index would have been used, how it would be used seek or scan, and a measure of its helpfulness.)

3. sys.dm_db_missing_index_groups (This DMV I used for linking between above 1 and 2 DMV’s together.)

Find the Result below:


My Recommendations:-

Frequently, database systems have used for either reporting or OLAP. Reporting systems usually involve a few long running queries, whereas transactional systems typically involve many queries running quickly.

Once executed above query it will give you Top 10 missing indexes which are costing more to your server. 

Doesn’t Implement blindly suggested missing indexes through above query, since every index has a cost associated with them.

In OLAP environment when data is inserted into a table, accordingly data have to update any associated indexes are there; this can increase the query duration more so think before creating an index in OLAP environment.

And some indexes may improve the performance of updates or deletes since these queries often have a WHERE clause.
Using stopwatch, DMVs or the Statistics Time (I/O) command to measure the impact of adding an index.

I suggest you use these methods to determine the impact of an index before you implement in production environment.


NOTE: Always monitor the  SQL Server Performance Monitoring through native or third party tools.
About the Missing Indexes Feature. Finding Missing Indexes. Improve SQL performance find your missing indexes. Reducing I/O with the 'Missing Indexes' DMVs. A Look at Missing Indexes. missing indexes query missing indexes in sql. missing indexes sql server. missing indexes dmv. script to find missing indexes in sql server. sql server missing indexes query. missing indexes in sql server 2008. find missing indexes sql server. Using SQL Server DMVs to Identify Missing Indexes. Are you using SQL's Missing Index DMVs. Missing Indexes in sql server 2012. Missing Indexes in sql server 2008. Missing Indexes in sql server 2005. Limitations of the Missing Indexes Feature. Use the Missing Indexes Feature to optimize and tune sql queries. Tracking Down Missing Indexes in SQL Azure.

2 comments:

Post a Comment