Are you using SQL's Missing Index DMVs?

re: Are you using SQL's Missing Index DMVs?

Pablo Mugica — Fri, 24 Aug 2007 17:42:05 GMT

Agree with the cool & magical of this feature (imagine avoiding errors in a group of people creating indexes @ a large datawarehouse). Thank You for the reminder about this.

re: Are you using SQL's Missing Index DMVs?

Duncan Millard — Wed, 17 Oct 2007 13:35:27 GMT

Hi Bart, thanks for such a great heads-up.

Just a slight note of caution to BizTalk users: I just ran this query on a box here and found lots of recommendations for adding indices in the BizTalk databases.

People must not follow these recommendations on BizTalk! The BizTalk database is very carefully tuned by hand; any changes to these databases will potentially cause serious problems and PSS will most likely insist on step 1 of any troubleshooting being to set BizTalk database installations back to the out of the box install.

As they say at: http://blogs.msdn.com/biztalk_core_engine/archive/2007/01/04/what-you-can-and-can-t-do-with-the-messagebox-database-server.aspx, "(don't add indexes, columns, triggers, ... If you do you will hear silence when you call for help)"

Hope this little bit of info is useful!

Thanks,

Duncan

Dynamic Management Views

SELECT Star FROM Chad.Brain — Wed, 28 Nov 2007 09:28:07 GMT

In my "APPLY Operator" post I used the example query below to illustrate the use of CROSS APPLY. I mentioned

Missing Index DMVs

Troubleshooting and Tips - Cindy Gross — Tue, 22 Jan 2008 19:54:58 GMT

SQL Server 2005 has some DMVs that will help you tune the system. The missing index DMVs track recent queries that could have benefited from an index that didn't exist. ...

Which queries are missing indexes?

My two cents — Fri, 09 May 2008 00:06:52 GMT

One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several

re: Are you using SQL's Missing Index DMVs?

Phil Streiff — Tue, 08 Jul 2008 00:27:39 GMT

I've been using your excellent "missing index" DMV for some time and just recently it has started throwing an error on one of my servers:

"Arithmetic overflow error converting float to data type numeric."

Any idea what might be causing this and how I can possibly work around it by altering the script?

SQL2005 Ent Ed, SP2, Windows 2003 Server

Thanks,

Phil

re: Are you using SQL's Missing Index DMVs?

bartd — Tue, 08 Jul 2008 01:06:19 GMT

I haven't run into that problem myself, but think I see what is causing it. I've made a couple of minor changes to the query that should fix it; can you give it a try and let me know what you find?

re: Are you using SQL's Missing Index DMVs?

Phil Streiff — Tue, 08 Jul 2008 19:58:10 GMT

Hi Bart:

I recopied the script and ran it again but am still getting the same error. Only happens on one of my servers though. <weird>

Thanks,

Phil

re: Are you using SQL's Missing Index DMVs?

bartd — Tue, 08 Jul 2008 21:02:14 GMT

Phil, I've removed the attempted conversion of the improvement measure to decimal (it was just there for formatting, anyway). Can you try once more? You should no longer get the error, but if you do, ping me offline at bartd at micro soft dot com and we'll figure it out. Thanks, Bart

re: Are you using SQL's Missing Index DMVs?

Phil — Thu, 10 Jul 2008 18:02:20 GMT

Hi Bart:

That seemed to fix the problem. The script runs successfully now and returns expected results.

However, the highest 'improvement_measure' is now displaying '9.84239188405347E+36' so, I guess this is where the formatting gets lost. I'm not quite sure how to interpret that number now, but it's obviously a big one.

Thanks again,

Phil

re: Are you using SQL's Missing Index DMVs?

Robert McLaws — Wed, 22 Oct 2008 21:35:44 GMT

Everyone,

If you want to import the results into Excel for analysis, you need to change Line 10 from this:

+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

to this:

+ ISNULL (' INCLUDE (' + mid.included_columns + ')”', '”') AS create_index_statement,

Otherwise, the SQL statement jacks with the columns in the comma-separated file.

HTH

re: Are you using SQL's Missing Index DMVs?

SQLDeveloper — Thu, 04 Dec 2008 10:16:14 GMT

Thanks for the script for missing indexes, I used that a few times. And I found it very useful. Since it gathers all those statistics by using the activity on the database.

Thanks again

re: Are you using SQL's Missing Index DMVs?

Neel — Wed, 11 Mar 2009 18:28:30 GMT

Thank you very much. This has improved my query performance a lot :)

re: Are you using SQL's Missing Index DMVs?

joetig — Thu, 09 Apr 2009 20:37:58 GMT

Is There a way to find the object \ query that caused the generation of the missing index entry in the dmv

re: Are you using SQL's Missing Index DMVs?

Laerte Junior — Wed, 21 Oct 2009 11:45:25 GMT

I´m sorry my english, was by google

I have to disagree on some points. I had a recent experience that the situation could not use DTA and the DMV's were fantastic

Each situation has its application, both DTA and DVM's.

"The missing index DMVs don't take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however. "

I belive they not been made for that. In this case we have the DTA and this type of type of analysis I prefer to do this without automatic recommendations.

"The "improvement_measure" column in this query's output is a rough indicator of the (estimated) improvement that might be seen if the index was created. This is a unitless number, and has meaning only relative the same number for other indexes. (It's a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.) "

The Avg_user Impact is near to close. Very near with the Real

(I did a lot of tests with a 4TB database )

"The missing index DMVs don't make recommendation about whether a proposed index should be clustered or nonclustered. This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries. (DTA can do this, however.)"

Like I said , I belive they not been made for that too. For this we have another DMV´s and DTA like you said.

And particularly for me, the definition of the index cluster is very focused on their specific business rule as discussing when the normalization is good or not. Is good when you answer your business.

Won't recommend partitioning.

Yes, this is a problem really.

"It's possible that the DMVs may not recommend the ideal column order for multi-column indexes."

No, By ton of tests I Did ALWAYS the columns suggested in multi-column indexed was correctly, by your selectivity.

A very interesting point to see is that the DMV's return a run statistics and cost per query

of not benefited from this index as the actual load in your environment. The DTA does not.

If you have a third-party software that makes many queries dynamically by the application,

you have to get the queries by profiler and run in the DTA.

And to know which query is being accessed more (number) is complicated by the profiler.

For DMV use user_seeks.And it is a REAL number to your environment workload

I believe that we can not go out creating indexes with the DMV's, but also by the DTA.

It's all a question of the situation that you will use one or the other.

But surely they were a goal of time sql server

This is my feedback about the DMV´s.

http://www.simple-talk.com/sql/performance/reducing-io-with-the-missing-indexes-dmvs/

re: Are you using SQL's Missing Index DMVs?

bartd — Thu, 03 Dec 2009 22:53:45 GMT

Thanks for the comment, Laerte. You make good points, and for the most part I agree with you. It wasn't my intent to imply that the DMVs are "bad" and DTA is "good". I just wanted to make sure that people knew the relative strengths and weaknesses of each tool so that they can deploy each in the proper circumstances.

re: Are you using SQL's Missing Index DMVs?

bartd — Fri, 04 Dec 2009 00:16:21 GMT

@joetig,

>> Is There a way to find the object \ query that caused the generation of the missing index entry in the dmv <<

Not directly, but there's an approximate technique for doing this that was shared by Leo Pasta at http://sqlblogcasts.com/blogs/leopasta/archive/2008/05/08/which-queries-are-missing-indexes.aspx.

HTH!