Execution plans

• ravisamigo

  SSCertifiable

  Points: 7440

  More actions

  October 9, 2012 at 11:35 pm

  #282818

  Hi All,

  If query is not using execution plans, how we can find it and what is the action required from DBA side?

  How we can reduce the query cost for select queries with subqueries[ Ex : select * from table1 where empid in ( select * from table2) ]?

  Please help me out on this.

  Thanks and Regards,

  Ravichandra.

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  October 10, 2012 at 12:56 am

  #1547481

  ravisamigo (10/9/2012)

  ---

  Hi All,

  If query is not using execution plans, how we can find it and what is the action required from DBA side?

  How we can reduce the query cost for select queries with subqueries[ Ex : select * from table1 where empid in ( select * from table2) ]?

  Please help me out on this.

  Thanks and Regards,

  Ravichandra.

  Even the simplest query has an execution plan.

  Action required for what?

  There are no tuning techniques which are specific to subqueries.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• F. van Ruyven

  SSCommitted

  Points: 1507

  More actions

  October 10, 2012 at 1:10 am

  #1547486

  Hi,

  To find the plan, you could try something like this

  SELECT TOP 100 [Average CPU used] = total_worker_time / qs.execution_count,[Total CPU used] = total_worker_time,[Execution count] = qs.execution_count, qs.total_logical_reads TotalPageReads, qs.total_logical_writes TotalPageWrites,last_execution_time,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid), qp.query_plan

  FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

  where

  DB_NAME(qt.dbid) = 'MyDB'

  and

  last_execution_time > '2012-10-01 07:15:00'

  ORDER BY [Average CPU used] DESC

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  October 10, 2012 at 4:57 am

  #1547586

  As was stated all queries have an Execution Plan. Generating an Estimated Execution Plan for a query is one of the steps the Database Engine must do for all queries. In addition to Estimated plans there is also the concept of an Actual Execution Plan. You can start here to see a graphical representation of each type of Execution Plan:

  Displaying Graphical Execution Plans (SQL Server Management Studio) - SQL Server 2005

  Begin here to start learning about what execution plans are and how to read them:

  Execution Plan Basics (11 May 2008) by Grant Fritchey[/url]

  View the execution plans (estimated and actual) for one of your queries. Post a new thread if you have further questions.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 10, 2012 at 7:02 am

  #1547636

  Subqueries are seldom a problem themselves. It's a widespread myth that subqueries are slow, they are generally not. When there is a performance problem, the solution requires investigating the cause (not assuming it's the subquery) and an appropriate fix

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass