Missing Indexes DMV Report, 3 billion Impact!
- by Tara Kizer
We’ve been having some major performance issues with one of the applications that I support. The database is on SQL Server 2005 and is about 150GB in size. We’ve identified a couple of issues already on the database side. The first issue is that some query (or maybe several queries) is getting a bad execution plan at some point in time during the day. When it occurs, database performance comes to a grinding halt. We know it’s a bad execution plan as running DBCC FREEPROCCACHE immediately resolves the problem system-wide. As we have not yet identified the problematic query, we’ve put a temporary solution in place that frees the procedure cache on an hourly basis via a SQL Agent job. This is not ideal, but it is getting us through the day without a major problem. We are actively working on identifying the problematic query and hope to disable the SQL Agent job soon. Earlier this week, we had a major slowdown for one of the processes of this application. I was unable to find any database performance issues, but I continued to investigate it. One of things that I typically do when investigating database performance issues is run the “Missing Indexes DMV Report” (that’s what I call it at least). When analyzing the output of that report, I immediately dismiss anything under 1 million “Impact” as I want to target the “low-hanging fruit” initially. When I ran the report earlier this week, I was shocked to find a suggested index with an impact of over 3 billion! Do I win a prize for the highest impact? Has anyone seen a value higher than mine? My exact value was 3154284120.67765. The performance issue from earlier this week ended up being an application problem, but it also brought to light a much needed index. I had previously seen this index come up in that report but always with a much lower impact. I had never considered it as the index’s selectivity is very low. It’s a composite index with three columns. The first column is not selective, the first two columns are not selective, and the three columns together are not selective. In fact, no matter how I order it, the index will not be selective at all. I briefly discussed this with Kimberly Tripp, and she said that this was okay for covering indexes. Selectivity is irrelevant for a covering index. She indicated that she’s even created indexes with gender as the first column in the index. I’ve got lots to learn still!
