Post reply

Actual plan

  • 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

  • June 16, 2010 at 6:30 am

    #1180913

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 16, 2010 at 7:59 am

    #1180981

    .........

  • June 16, 2010 at 8:01 am

    #1180985

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

  • June 16, 2010 at 8:14 am

    #1181000

    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

  • June 16, 2010 at 9:07 am

    #1181084

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 16, 2010 at 9:14 am

    #1181089

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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