June 22, 2009 at 8:47 am
Running the following query on one of my systems gives me data about the indexes on the table.
2 of the indexes have a high number of seeks and no scans or lookups. However the other index which
is the cluster has no seeks, 4104 scans and 23805 lookups.
select i.Name, i.Type_Desc, i.Is_unique, d.*
from sys.dm_db_index_usage_stats as d
join sys.indexes as i
on i.object_id = d.Object_id and
i.index_id = d.Index_id
where database_id=db_id() and
d.object_Id=object_id('MY_TABLE')
Does anyone have a query that makes use of the missing index DMVs to list out the actual queries that are causing the high number of lookups. I would like to see if possible a list of all the queries against this table with related stats about whether the execution caused a seek, lookup or scan and the indexes it used.
Thanks in advance for any help. By the way our new mail server has blocked this site as spam along with most of our clients so I might not get any notifications about replies to this question if anyone does reply 😀
June 22, 2009 at 8:58 am
This query which I pulled from HERE (I'm pretty sure this is where I got it anyway) will get you most of what you are asking for I believe.
withxmlnamespaces (
default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECTqp.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]
fromsys.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
whereqp.query_plan.exist('//MissingIndex/@Database') = 1 --The use of // means somewhere in the XML
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 22, 2009 at 10:11 am
Thanks for that, it is useful but it looks like I would have to check each plan in turn to find out whether it caused the lookup. I was hoping for something that would return the query from dm_exec_sql_text only if that query had caused the user_lookups counter to increment when the sys.dm_db_index_usage_stats DMV gets updated. I could then just look at those queries in turn and check out the execution plans on them.
June 22, 2009 at 11:12 am
Understood. If you find a way be sure to post it here. 😉
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply