Actual plan

Question

Actual plan

PradeepVallabh

SSC Eights!

Points: 987
More actions
June 15, 2010 at 9:24 pm

#222200

I noticed some of the queries fired on the database doesn't have Actual plan....Is it okay to have queries without any Actual plan???
Regards,
Pradeep

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You're probably looking at the plan cache. All plans in the plan cache are estimated plans. Every query you execute gets an actual execution plan, they have to. Not all plans are stored in the cache though. For example, if you have a stored procedure or a query with the hint WITH RECOMPILE, then no plan is stored in cache for that procedure/query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sandyinfowave Default port Points: 1490 More actions · Answer 2

sandyinfowave

Default port

Points: 1490

June 16, 2010 at 7:59 am

#1180981

.........

PradeepVallabh SSC Eights! Points: 987 More actions · Answer 3

Thanks for the info.....So in short the queries with estimated execution plans in the cache run faster

PradeepVallabh SSC Eights! Points: 987 More actions · Answer 4

One more question...I have a query with high number of statement executions but i believe the query is not stored in cache....How do i make sure that query is stored in the cache so that it doesn't use too much of CPU resources

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

PradeepVallabh (6/16/2010)
Thanks for the info.....So in short the queries with estimated execution plans in the cache run faster

Well, that really depends on a lot of factors. I'll say this, queries that have an execution plan in cache generally don't have the recompile their execution plans, and therefore achieve a performance gain due to that savings.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

PradeepVallabh (6/16/2010)
One more question...I have a query with high number of statement executions but i believe the query is not stored in cache....How do i make sure that query is stored in the cache so that it doesn't use too much of CPU resources

You can use dynamic management views. Here's the basics to get you started:

SELECT * FROM

sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE dest.text = 'My Query'

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning