Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Queries, Plans, and Indexes Expand / Collapse
Author
Message
Posted Thursday, July 31, 2008 10:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 18, 2008 9:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 20, 2014 10:19 AM
Points: 92, Visits: 280
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
Posted Tuesday, November 18, 2008 10:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 18, 2008 10:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 20, 2014 10:19 AM
Points: 92, Visits: 280
That did it. Thanks for the quick response and the fix.
Post #604589
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse