Execution plans

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » Execution plans

Execution plans

Rate Topic

Display Mode

Topic Options

Author

Message

ravisamigo

Posted Tuesday, October 09, 2012 11:35 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 22, 2013 11:11 PM
Points: 250, Visits: 706

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

ChrisM@home

Posted Wednesday, October 10, 2012 12:56 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 921, Visits: 3,748

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

F. van Ruyven

Posted Wednesday, October 10, 2012 1:10 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 3:54 AM
Points: 277, Visits: 886

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

opc.three

Posted Wednesday, October 10, 2012 4:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1370842

GilaMonster

Posted Wednesday, October 10, 2012 7:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 37,741, Visits: 30,020

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 »

Permissions