InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 13:29:55 GMT20http://www.sqlservercentral.com/Forums/Topic1370685-266-1.aspxSubqueries 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 fixWed, 10 Oct 2012 07:02:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1370685-266-1.aspxAs was stated all queries have an Execution Plan. Generating an [i]Estimated Execution Plan[/i] for a query is one of the steps the Database Engine must do for [i]all[/i] queries. In addition to [i]Estimated[/i] plans there is also the concept of an [i]Actual Execution Plan[/i]. You can start here to see a graphical representation of each type of Execution Plan:[u][url=http://msdn.microsoft.com/en-us/library/ms178071(v=sql.90).aspx]Displaying Graphical Execution Plans (SQL Server Management Studio) - SQL Server 2005[/url][/u]Begin here to start learning about what execution plans are and how to read them:[u][url=http://www.simple-talk.com/sql/performance/execution-plan-basics/]Execution Plan Basics (11 May 2008) by Grant Fritchey[/url][/u]View the execution plans (estimated and actual) for one of your queries. Post a new thread if you have further questions.Wed, 10 Oct 2012 04:57:55 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1370685-266-1.aspxHi,To find the plan, you could try something like thisSELECT 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_planFROM 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 qpwhere DB_NAME(qt.dbid) = 'MyDB' and last_execution_time > '2012-10-01 07:15:00'ORDER BY [Average CPU used] DESCWed, 10 Oct 2012 01:10:08 GMTF. van Ruyvenhttp://www.sqlservercentral.com/Forums/Topic1370685-266-1.aspx[quote][b]ravisamigo (10/9/2012)[/b][hr]Hi All,[b]If query is not using execution plans[/b], 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.[/quote]Even the simplest query has an execution plan. Action required for what?There are no tuning techniques which are specific to subqueries.Wed, 10 Oct 2012 00:56:22 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1370685-266-1.aspxHi 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.Tue, 09 Oct 2012 23:35:48 GMTravisamigo