Performance Tuning and Troubleshooting: Missing Index DMVs and the DTA

The past couple of weeks have been interesting at work, instead of the usual steady stream of cluster builds and upgrade/migration projects, I have been working pretty much flat-out on several applications, each of which has been suffering from chronic performance problems, in varying degrees.

In the perfect world, you would go about solving performance problems by examining the code, execution plans, re-writing it if necessary, suggesting helpful indexes, and so on. Of course, the world isn’t like that!

The first application I was working on is written by a subsidiary of the company I work for. It is meant to be “database agnostic”, and supposedly could be deployed on RDBMSs other than Microsoft SQL Server. In reality it is never run on Oracle, but the result of the design choice means that it has no SPs, uses no proprietary syntax or features, and also means it is impossible for a DBA (as opposed to a Developer) to modify application code, as it is all embedded in the application.

To compound its issues, it uses a database design of multiple schemas, one for each customer, each of whom gets their own identical set of 400-odd tables. Hence you end up with a database with 177,000 (no that’s not a typo!) tables. Having a database with that many tables in brings its own challenges when trying to run dbcc checkdb or update statistics!

Performance problems in this system are normally “solved” by throwing hardware at them, this instance was no different – with performance so appallingly bad that customers were threatening to ditch the product in favour of one of its competitors, the spending freeze at work suddenly, magically thawed, and 2 new brand new blade servers were bought in a rush. Management ignored the recommendations of a team of 5 highly experienced DBAs to implement them as a Windows Enterprise Cluster, choosing instead to use Standard Edition (synchronous only) database mirroring, which we already know causes performance problems in 2005.

The second system I was looking at had several interesting, but different, issues. It uses LINQ. In a word.. urgh. If you have ever seen the code that LINQ spits out, you will already know that it sucks the big one! This system is run on an Active/Passive 2-node Standard Edition SQL Server 2005/Windows 2003 cluster. For a start, it was woefully short of RAM (8GB only). 24 GB for each node are on order, badly needed, as we are seeing a Page Life Expectancy as low as 15 seconds sometimes (dire).

The point about these 2 systems is that, because of the use of embedded ad-hoc SQL for queries in one, and the use of LINQ in the other, opportunities for code tuning/re-writing by a DBA are simply not there.

Also, the performance problems and related customer complaints, lead to pressure from management, and there is (correctly) a great deal of urgency attached to quick-wins. This is where I find the Missing Index DMVs and the Database Tuning Advisor (DTA) come in really handy – they can give you quick answers to what indexes are needed very quickly. This script finds which indexes are recommended by the missing index DMVs and generates CREATE INDEX statements from them (assumed all to be nonclustered, you can tailor this if you wish) Run it in the database in question, and modify accordingly to set the database name in Line 21..

+ ‘ on ‘ + schema_name(o.schema_id) + ‘.’ + OBJECT_NAME(c.OBJECT_ID)
+ ‘(‘
+ CASE WHEN c.equality_columns IS NOT NULL AND c.inequality_columns IS NOT NULL THEN c.equality_columns + ‘,’ + c.inequality_columns
WHEN c.equality_columns IS NOT NULL AND c.inequality_columns IS NULL THEN c.equality_columns
WHEN c.inequality_columns IS NOT NULL THEN c.inequality_columns
+ ‘)’ + CHAR(10)
+ CASE WHEN c.included_columns IS NOT NULL THEN ‘Include (‘ + c.included_columns + ‘)’
END AS includes
FROM sys.dm_db_missing_index_group_stats a
INNER JOIN sys.dm_db_missing_index_groups b
ON a.group_handle = b.index_group_handle
INNER JOIN sys.dm_db_missing_index_details c
ON c.index_handle = b.index_handle
INNER JOIN sys.objects o
WHERE DB_NAME(database_id) = ‘MyDB’  –change dbname here
AND equality_columns IS NOT NULL — comment this out if necessary
ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans) DESC

It auto-generates indexes with unique-ish names. These recommendations come with the usual warnings – use with caution and care, and ignore any suggestions you think aren’t sensible. However it my experience they tend to be very good suggestions. SQL Server puts data in the missing index DMVs when it executes a query and realises it could’ve done it better with a specific index.

I use these DMVs in tandem with the DTA. Capture a representative sample of application usage with Profiler (using the ‘TSQL – Replay’ template). Then create a copy of your live database(s) on a test, dev or standby server and use that for tuning (don’t use the Live server – it’s a resource-intensive operation!). These recommendations often tally closely with those from the missing index DMVs, and again, give you quick-win performance gains, and can make you look really good to your bosses!

So these tools can be very useful in situations where tuning code directly is impossible, and you don’t have time to dig into specific code and its execution plan, and be more systematic/exhaustive. Good luck!