May 5, 2009 at 6:31 pm
Hi,
I found an article on this matter:
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2008/04/27/1229.aspx
I've tried to run the below query from the article on few databases, replacing "[DBNameHere]", "[TableNameHere]", "[IndexNameHere]" with database, table and index names, but all I got was:
(0 row(s) affected)
Am I missing something? The URL in the query points to some XML files. Do I need to download and somehow attach those files before I run the query?
Thanks.
with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as s)
select top 50 st.text, qp.query_plan, qs.ec as exec_count, qs.tlr as total_reads
from (
select s.sql_handle, s.plan_handle, max(s.execution_count) as ec, max(s.total_logical_reads) as tlr
from sys.dm_exec_query_stats as s
where s.max_logical_reads > 100
group by s.sql_handle, s.plan_handle) as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply (select distinct relop.value('@Index','nvarchar(130)') as IndexRef
from qp.query_plan.nodes(
N'//s:Batch/s:Statements/s:StmtSimple/s:QueryPlan[1]//
s:RelOp[@PhysicalOp = ("Index Scan")]/*[local-name() = ("IndexScan")]/
s:Object[@Database = ("[DBNameHere]")
and @Table = ("[TableNameHere]")
and @index = ("[IndexNameHere]")]'
) as ro(relop)
) as r
May 6, 2009 at 7:23 am
I'm not an xquery guru, but it could be possible that there are no cached plans that include an index scan on the table/index you are searching. I ran the query on one of my databases and got no rows, but when I eliminated the XQuery filters for index and table I got a bunch of rows back.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 7:26 am
Jack is right. I played around with this a little last night. The xquery is just parsing the xml plan looking for an index scan. You probably just dont have an index scan in the cache for your index.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply