Find Top N costly query plans in adhoc batches or modules...

re: Find Top N costly query plans in adhoc batches or modules...

Cristian Lefter, SQL Server MVP — Fri, 07 Oct 2005 23:01:44 GMT

That's another great example. Thank you Umachandar! I love the new Summary Reports and of course the sources for it (the default trace and DMVs and DMFs). I use Profiler to see the use of them :)

re: Find Top N costly query plans in adhoc batches or modules...

Veeral — Sun, 16 Oct 2005 19:22:46 GMT

Hi,

I have two stored procedures one written with SQL and other in CLR language.I m able to find CPU time takenby stored proc written in SQL with dm_exec_query_stat.But I m unable to find CPU time for CLR written stored procedure with dm_exec_query_stat.Can u plz tell me how can this be done.

Veeral
veeralmpatel@rediffmail.com

re: Find Top N costly query plans in adhoc batches or modules...

SQL Server Engine Team — Sun, 16 Oct 2005 20:55:47 GMT

Sorry, I didn't include the CLR time counters in my query before. That is why you were seeing zero for the elapsed time. See the topic on sys.dm_exec_query_stats DMV. I modified the query to include the CLR counters too.

Thank you

Great blog — Thu, 23 Feb 2006 07:20:42 GMT

Your site is realy very interesting.

re: Find Top N costly query plans in adhoc batches or modules...

Jerry — Tue, 11 Apr 2006 23:24:38 GMT

This stuff is all well and good, but you sure do have to be careful. I attempted to run the final query above on our development box - it still had not completed after 3 minutes. It pegged CPU at 100% for the duration and had taken 16 million page reads by the time I cancelled it. Can't even imagine what it would do to our production box that has 3k trans/sec.

Note - I am not trying to be overly critical, I *love* 2005 and the dmv's, and judicious use of the query stats has helped us tremendously. But often things are quite different out here in the wild; I just wanted to caution anyone from running the code on a production box w/o testing it first.

Jerry
jfos@plex.com

re: Performance issue with the DMVs in query

SQL Server Engine Team — Wed, 31 May 2006 01:53:35 GMT

I just wanted to update that we found and fixed a performance problem with the sys.dm_exec_query_plan DMV. This DMV can take a long time to generate plans for certain statements and this will reduce the overall performance of the query. This bug fix is in SQL Server 2005 SP1 so I would encourage you to try & use the DMV to see if you get noticeable improvements.

--
Umachandar

re: Find Top N costly query plans in adhoc batches or modules...

sangeethashekar — Thu, 18 Jan 2007 02:58:36 GMT

Please check out http://blogs.msdn.com/sqlprogrammability/ for a series of articles on the Plan Cache and improvements made to the plan cache in SQL Server 2005 RTM. The articles also have a number of examples and queries on plan cache related DMVs that you may find useful.

re: Find Top N costly query plans in adhoc batches or modules...

ALZDBA — Thu, 29 Mar 2007 11:16:40 GMT

I'm not into this xquery stuff, but can someone please give me a query to select only the queryplans that have implicit conversions ?

re: Query to determine CONVERT_IMPLICIT operations from query plans...

SQL Server Engine Team — Fri, 30 Mar 2007 02:33:38 GMT

Hi,

You can get the SELECT statements that have an expression with CONVERT_IMPLICIT in many ways. Below is a query that uses the sys.dm_exec_query_plan and XQuery to get the information:

with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)

select qs.sql_handle, qs.plan_handle, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time

, db_name(qp.dbid) as database_name

, quotename(object_schema_name(qp.objectid, qp.dbid)) + N'.' + quotename(object_name(qp.objectid, qp.dbid)) as obj_name

, qp.query_plan.value( N'(/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"])[1]/@StatementText', 'nvarchar(max)' ) as plan_stmt_text

, qp.query_plan.value( N'(/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"]/sql:QueryPlan/sql:RelOp/descendant::*/sql:ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")])[1]/@ScalarString', 'nvarchar(4000)' ) as scalar_string

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp

where qp.query_plan.exist( N'/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"]/sql:QueryPlan/sql:RelOp/descendant::*/sql:ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]' ) = 1;

You can also use the new DMV sys.dm_exec_text_query_plan and do a text-based search which is less accurate.

select qs.sql_handle, qs.plan_handle, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time

, db_name(tqp.dbid) as database_name

, quotename(object_schema_name(tqp.objectid, tqp.dbid)) + N'.' + quotename(object_name(tqp.objectid, tqp.dbid)) as obj_name

, tqp.query_plan

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_text_query_plan( qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset ) as tqp

where tqp.query_plan like '%"%CONVERT_IMPLICIT%"%';

Note that you need to change the query (OBJECT_NAME and OBJECT_SCHEMA_NAME usage) if you are not running on SQL Server 2005 SP2. I will be discussing this particular question in a new BLOG article to explain further.

Umachandar

re: Find Top N costly query plans in adhoc batches or modules...

gqnelson — Fri, 21 Aug 2009 19:30:15 GMT

How do I see the database name that the query is executing against?

re: Find Top N costly query plans in adhoc batches or modules...

gqnelson — Fri, 21 Aug 2009 19:51:45 GMT

This returns the top 25 SELECT statements. How would I change it to return all types of statements?

re: Find Top N costly query plans in adhoc batches or modules...

gqnelson — Fri, 21 Aug 2009 20:55:06 GMT

I found if you change, you get all queries instead of just SELECT:

N'//sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"]'

N'//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp[1]'