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

Execution plans Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 11:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:00 PM
Points: 251, Visits: 760
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.
Post #1370685
Posted Wednesday, October 10, 2012 12:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 1,037, Visits: 7,025
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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1370717
Posted Wednesday, October 10, 2012 1:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 368, Visits: 1,314
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
Post #1370722
Posted Wednesday, October 10, 2012 4:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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

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
Post #1370842
Posted Wednesday, October 10, 2012 7:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1370903
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse