Queries, Plans, and Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by ericzbeard » Queries, Plans, and Indexes

Queries, Plans, and Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

ericzbeard

Posted Thursday, July 31, 2008 10:36 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 11:06 AM
Points: 15, Visits: 64

Comments posted to this topic are about the item Queries, Plans, and Indexes

Post #544845

Michael Lynn

Posted Tuesday, November 18, 2008 9:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 04, 2013 12:29 PM
Points: 92, Visits: 266

I am getting the following error messages when executing your query.

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ' '.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ' '.

Post #604532

ericzbeard

Posted Tuesday, November 18, 2008 10:42 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 11:06 AM
Points: 15, Visits: 64

Something weird is happening with hidden characters in the site's text box control. I copy-pasted the script and had to delete a bunch of white space to get it to work (even though it looks the same).

Try this version:

/*

This query gets all queries, their query plans, and all indexes included in those plans.

Authors: Eric Z. Beard, eric@loopfx.com, ericzbeard@yahoo.com
(borrowed some stuff from http://mohammedu.spaces.live.com/Blog/cns!6699CF8ADD3D4F67!325.entry,
Greg Lindwood? Uma Chandran?)
Date of Origin: July, 2008

*/

use master;

;with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as s)
select st.text
, qp.query_plan
, qp_objects.qp_obj_schema
, qp_objects.qp_obj_table
, qp_objects.qp_obj_index
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
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 obj_nodes.value('@Schema', 'nvarchar(130)') as qp_obj_schema , obj_nodes.value('@Table', 'nvarchar(130)') as qp_obj_table, obj_nodes.value('@Index', 'nvarchar(130)') as qp_obj_index
from qp.query_plan.nodes('//s:Object[@Schema != "[sys]" and @Index and local-name(parent::node()) != "Update"]') as qry_plan(obj_nodes)
) as qp_objects
;

Post #604583

Michael Lynn

Posted Tuesday, November 18, 2008 10:48 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 04, 2013 12:29 PM
Points: 92, Visits: 266

That did it. Thanks for the quick response and the fix.

Post #604589

« Prev Topic | Next Topic »

Permissions