Missing Indexes

Question

Missing Indexes

UnionAll

SSCrazy

Points: 2102
More actions
December 21, 2008 at 7:06 pm

#126668

Hi,
I found a great article here on this site which explains how to find missing indexes. http://www.sqlservercentral.com/articles/Indexing/64134/
When the Sql Server sees a query like:
select Column1,Column2 from tablename where columnC = 'ABC'
an entry will be made into the DMV's with EqualtyColumns = ColumnC, if there is no index on ColumnC.
Now i am want to know what query did sql server recieve to find that there is a missing index.
Is it possible to acheive this without using Profiler.
I am using sys.dm_exec_requests but....
Can someone help me on this one?
Thanks.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

One way of doing it is to run a query on all query plans that are in the cache. Here is one version that can be used. Of course it will show it to you only if the query plan exists in the cache. You can run it few times or make a job that inserts that data that it brings back into a table. Notice that I would run it on a test server or dev server. Not sure that I would run it on a production server.

;with xmlnamespaces (

default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT qp.query_plan, total_worker_time/execution_count as AvgCPU,

total_elapsed_time/execution_count as AvgDuration,

total_logical_reads/execution_count as AvgLogicalReads,

total_physical_reads/execution_count as AvgPhisicalReads,

execution_count,

substring(st.text, (qs.statement_start_offset/2)+1 , ((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt,

qp.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') AS TotalImpact,

qp.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [Database],

qp.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [Table]

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) st

cross apply sys.dm_exec_query_plan(plan_handle) qp

where qp.query_plan.exist('//MissingIndex/@Database') = 1 --The use of // means somewhere in the XML

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Be really careful implementing the recommended missing indexes from the DMV's. In some cases they will recommend a duplicate index with a different list of included columns. You need to pay attention to what already exists and what is being created when using those DMV's. You can read more about this on Greg Linwoods blog:

http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2008/04/29/1233.aspx

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

samsqlserver (12/21/2008)
Now i am want to know what query did sql server recieve to find that there is a missing index.

the query optimiser will add an entry if it finds a query with insufficient indexes to satisfy the plan. Its internal functionality

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 4

SELECT object_name(object_id), d.*, s.*

FROM sys.dm_db_missing_index_details d

INNER JOIN sys.dm_db_missing_index_groups g

ON d.index_handle = g.index_handle

INNER JOIN sys.dm_db_missing_index_group_stats s

ON g.index_group_handle = s.group_handle

WHERE database_id = db_id()

ORDER BY object_id

This script help u to find missing indexes.

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Paresh Prajapati (12/22/2008)
SELECT object_name(object_id), d.*, s.*
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
WHERE database_id = db_id()
ORDER BY object_id
This script help u to find missing indexes.

He didn't ask to see the missing indexes. He already knows how to do it. He wanted to see the SQL Statement that has missing indexes. I don't think that it can be done with your query.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

What Adi posted originally is about as close as you are going to get.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

what ADI posted is not what the OP required

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

Perry Whittle (12/23/2008)
what ADI posted is not what the OP required

How about some explanation why it's not a way to solve the problem? Searching the plan cache for entries that contain the missing index XML Nodes is certainly one way to identify what query would have triggered an entry into the DMV's. If you are going to knock someones solution, you might consider providing one of your own and why you think it is superior.

The question is seeking to look back in time to determine something that isn't necessarily logged by SQL Server in a manner that is completely reproducible.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 9

Perry Whittle (12/23/2008)
what ADI posted is not what the OP required

This might be true, but can you at least explain why it isn't what the OP required? Who knows, maybe you'll be able to convince me.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

UnionAll SSCrazy Points: 2102 More actions · Answer 10

Thankyou Jonathan!

May be i got close on this one.

I executed this query multiple times select * from Person.Address where ModifiedDate = '01/01/2008' (there is no index on ModifiedDate).

Then i queried the missing indexes DMV, the result is like:

statement equality_columns User_seeks

[AdventureWorks].[Person].[Address] ModifiedDate 10

Then i queried sys.dm_exec_cached_plans and sys.dm_exec_sql_text. The result is:

Usecounts SQLQuery

10 select * from Person.Address where ModifiedDate = '01/01/2008'

With this i can atleast guess the query that is responsible for an entry in missing indexes DMV.

Please guide me if i am wrong.

Thanks.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

why act like a couple of little girls. The solution you provided is correct if the question were different. As far as i can see he wanted to know what query sql server uses to put the info into the dmv in the first place. Try reading the post and stop bitching at other forum members

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

Perry Whittle (12/24/2008)
why act like a couple of little girls. The solution you provided is correct if the question were different. As far as i can see he wanted to know what query sql server uses to put the info into the dmv in the first place. Try reading the post and stop bitching at other forum members

So you read the post one way, and the rest of us read it another and you are correct? You should check your ego at the sign in page. All that was said is if you want to knock another post, then provide some substantiation as to why you are doing so, and offer an alternative. Since you obviously can't do that, you'd have done more for the community just keeping your comments to yourself. Using obscenities doesn't help you look smarter either.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 13

sorry god but i'm not the one with the ego problem am i. Also i havent made any obscene comments, i just told you to quit crying like a pair of school girls and whining at other forum members. Maybe i'm not correct at the way i read the post but i'm not the one gave a snotty reply either and i didnt once say the reply was incorrect or knock it. It's people like you who make forums unenjoyable

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 14

Perry Whittle (12/24/2008)
why act like a couple of little girls. The solution you provided is correct if the question were different. As far as i can see he wanted to know what query sql server uses to put the info into the dmv in the first place. Try reading the post and stop bitching at other forum members

Am I supposed to understand that only little girls would ask to know why they are wrong? If this is the case, then yes I do act like a little girl, but last time I checked it was something that normal little girls, little kids and grown ups of both sex do.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/