SSMS – Performance Tuning using graphical execution plans – Missing indexes hints


SQL Server Performance tuning is an interesting exercise, and I for one, can enjoy tuning performance for hours at an end. I believe that if a system is facing a performance issue, the issue is almost never the SQL Server. The order in which I attack a performance issue is:

  1. Look at the application code – this is always the bottle neck (by code, I mean both – T-SQL and the VC++/.NET, etc code)
  2. Look at the underlying hardware configuration, more specifically I/O (we are not talking about capacities, this is the configuration)
  3. Look at the underlying hardware capacities
  4. Finally look at the platform software (Windows OS and SQL Server)

Indexes play a vital role in performance tuning – just as the key to a good reference book is the quality and usability of it’s Index, similarly, the key to a highly performing database is the high selectivity and usability of it’s index.

Please NOTE: Indexes are not the “fix-all” solution. Indexes can have a negative impact on performance, hence, please do the exercise due diligence before deploying an index. If you are in constant need of more indexes, it indicates a problem with your queries and overall database design. Index design is a science of it’s own, which is out of scope of today’s post.

A lot of people have come to me and said how to do we know if SQL Server needs an index. More often than not, the chain of thought begins from the fact that a given query is running slower than expected. SQL Server Management Studio does provide the option of suggesting missing indexes to the user. While most of the content of this post is SQL 2008 specific, I have highlighted how SSMS of SQL Server 2005 provides the Missing Index indication towards the end of the post.

If the SQL optimizer feels that an index would help it generate a better execution plan for any given query, SQL Server Management Studio provides us with an indication of it’s requirement when viewing the graphical execution plan.

If we run a query in SQL Server Management Studio with the graphical plan enabled, the missing index hint would show up in green-text as high-lighted in the screen-shot below.

USE AdventureWorks2008R2
GO

--Number of sales done in April, 2008
SELECT COUNT(*)
FROM Sales.SalesOrderHeader sh
WHERE MONTH(sh.OrderDate) = 4 AND YEAR(sh.OrderDate) = 2008
GO

SELECT COUNT(*)
FROM Sales.SalesOrderHeader sh
WHERE sh.OrderDate >= '2008-04-01' AND sh.OrderDate <= '2008-04-30'

image

From the screen-shot we see that the optimizer determines that the absence of the index has a 96.11% impact on the overall performance of the query. Since this is a huge impact, let’s right-click on the green-line and choose “Missing Index details”.

image

Notice that SQL Server opens up a new query editor window with the Missing Index template script ready for our use & modification.

/*
Missing Index Details from SQLQuery1.sql - VPCW2K8DENALI.AdventureWorks2008R2 (VPCW2K8Administrator (53))
The Query Processor estimates that implementing the following index could improve the query cost by 96.1176%.
*/

/*
USE [AdventureWorks2008R2]
GO
CREATE NONCLUSTERED INDEX [ < name sysname, Index, Missing of > ]
ON [Sales].[SalesOrderHeader] ([OrderDate])

GO
*/

Go to Query –> Specify Values for Template Parameters and fill in the template parameter values. Once the template values are filled in, the index may be created. The next time our batch is run, the performance impact should clearly be seen.

image

As you can see, the performance of the queries which used to run at 50% each has changed. The first query now takes up 92% of the processing time, whereas the 2nd query is able to utilize the indexes correctly (notice how the Index scans have turned to Index seeks) and therefore completes in 8% of the processing time.

In this case, clearly indexes suggested by Microsoft SQL Server were useful. If found that they do not impact any other area of the application, these indexes should most definitely be rolled out into production.

Missing Indexes via DMVs

DMVs (Dynamic Management Views) are very powerful, and therefore it is not surprising that the graphical interface of SQL Server Management Studio utilizes DMVs to get missing index information. If you too would like to experiment around with DMVs to identify missing indexes, please refer Books On Line at: http://msdn.microsoft.com/en-us/library/ms345417.aspx. This Books On Line page gives you:

  • An introduction to the missing indexes DMVs
  • Shows how to use this information to write CREATE INDEX statements
  • Highlights the limitations of the Missing Index feature (Very Important to read!!!)
  • Related Query tuning features

As mentioned above, it is my sincere request that you read about the limitations of the Missing Index feature before production use.

SQL 2005 Users

A lot of production systems are still running SQL Server 2005, and hence, I thought it best to highlight how Missing Index information is available in SQL Server 2005. The high-level steps to know about missing indexes in SQL Server 2005 is:

  • I ran the same set of T-SQL code against a SQL Server 2005 instance in a SQL Server 2005 SSMS
  • Once the execution plan is displayed, I clicked on the top-level SELECT statement in the plan
  • Bring up the Properties window by pressing F4 (read more on the properties window here)
  • Notice the section on Missing Indexes

image

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

4 thoughts on “SSMS – Performance Tuning using graphical execution plans – Missing indexes hints

  1. Szymon Wojcik

    Nakul, it seems we thought about similar stuff at the same time 🙂 my post is below yours at the blogs list and it’s also about missing indexes.
    Nice stuff abour your approach to performance problems – so many people forget about application performance blaming database for all evil.

    Like

    Reply
  2. Hardik Doshi

    Good article. Generally people think that SQL code is performing poorly when they are facing performance issues with application, without checking application code.

    One of my friend asked me about his code is performing poorly. We have checked the TSQL code (procedure) due to he was sure about his .net code was perfect. There were no issue with the TSQL code – it was properly used indexes, queries etc. When he sure that TSQL code was working fine then he checked the .net code. He found major issues with the logic and once he fix, application was working great.

    Like

    Reply
  3. Lempster

    Just a note of caution: the index recommendations suggested in the graphical execution plan (and in the Database Engine Tuning Advisor) do not take into account the size of a potential index nor the performance hit that may be suffered by UPDATES and INSERTS, so please don’t blindly follow every recommendation when it comes to indexes!

    Like

    Reply
  4. Nakul Vachhrajani

    Thank-you all for your feedback. I really appreciate it.

    @phobosq: Yes, it does look like we thought about missing indexes at the same time. It is good to know that you liked reading about my approach to performance issues.
    @Hardik: There’s the best proof that SQL is almost never the root cause of performance problems!
    @Lempster: Your point is very valid. The Graphical plan & the DTA do not take into account the index size and also might end up suggesting conflicting indexes. So, I second your suggestion about not blindly following every index-related recommendation.

    Once again, thank-you all for taking the time out and reading my post. I look forward to your feedback on all my past posts and also on the posts to come.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s