Post reply

Execution plans

  • 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.

  • 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]

  • 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

  • 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

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply