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'

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”.

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.

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

Until we meet next time,

Be courteous. Drive responsibly.