?
Solved

queries behind missing index

Posted on 2010-01-12
12
Medium Priority
?
250 Views
Last Modified: 2012-05-08
When the DMVs suggest an missing index, is it possible to retrieve the query behind this, that the missing index suggestion is based on, through DMV caching .

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 800 total points
ID: 26294350
You can't tell the queries that are missing the indexes.  you can tell the objects, but not the queries.  So, you could find the objects, and then would need to go find the queries/statements that use those objects and  go from there.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 26294422
this feature is not so smart as the database tuning advisor, i dont really think it is possible to retrieve the queries for which it created these indexes
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26296309
so profiler is the only choice to get that info, right?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 6

Author Comment

by:anushahanna
ID: 26296481
if profiler only, can you recommend the events that will help narrow the query.

thanks
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 800 total points
ID: 26397634
Use profiler to catch the queries that takes high CPU time or Reads and Writes..
You can easily correlate these set of queries with the missing index detail in sys.dm_db_missing_index_details DMV.

And you can also correlate those queries using the approach mentioned below to create indexes on your own..

http://msdn.microsoft.com/en-us/library/ms345405.aspx

Hope this answers..

Note: while running Profiler, make sure that you run your application under heavy load so that you can identify the potential queries that needs to be tuned.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26399748
would you recommend just using filters for the index recommended columns, to restrict just them? and see which of them have high CPU, Reads etc.

Thanks for the idea of writing "CREATE INDEX Statements"
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26399766
>>would you recommend just using filters for the index recommended columns, to restrict just them?

not sure what you mean?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26399883
Jegan was recommending catching queries with high cost. I already have an idea of what I want to look at through the profiler - the index columns the DMVs are recommending. I was asking him if it would suffice to look for transactions with those specific columns, for my purpose.

thanks Tim
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 800 total points
ID: 26400550
>> would you recommend just using filters for the index recommended columns, to restrict just them? and see which of them have high CPU, Reads etc.

Let me explain more in detail

* Run profiler and catch the queries with High CPU, Reads and Writes
* Execute those queries in SSMS now after running

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

* Now run the sys.dm_db_missing_index_details DMV to find out missing index info

Hope this is more clear.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26400974
what is the purpose of running the DBCC commands? Does it clear out the DMV values?

thanks
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 800 total points
ID: 26400999
they clear the procedure cache...the purpose is so that you're running the queries with the same conditions...that one doesn't run faster than the other because the first is cached.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26442417
Thanks very much, Experts.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question