Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831

    Unanswered: Is it possible to have multiple query execution plans for a stored procedure?

    I think not. Microsoft says it is possible: one for parallel and one for serial execution. Don't believe that's possible for a stored procedure to change execution plans on the fly. Have an on-going problem with timeout occurring with an application and narrowed the culprit to a stored procedure. I couldn't find any obvious issues database wise, no locks, etc. so I recompiled (altered) the sproc without making any changes and the issue cleared for a couple days.

    It happened again to day, and so I recompiled (altered) the sproc and it went away again. No code changes to both application (so they say) and stored procedure. I ran the below code snippet to check for sprocs with multiple cached plans and the offending one came up on a short list. So, my question is, has anyone else encountered this before? Is it one sproc per query plan or can there be more than one. I understand the connection issues.

    Code:
    SELECT db_name(st.dbid) DBName,
    object_schema_name(st.objectid, dbid) SchemaName,
    object_name(st.objectid, dbid) StoredProcedure,
    MAX(cp.usecounts) Execution_count,
    st.text [Plan_Text]
    INTO #TMP
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    WHERE db_name(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
    GROUP BY cp.plan_handle,
    db_name(st.dbid),
    object_schema_name(objectid, st.dbid),
    object_name(objectid, st.dbid),
    st.text
    ORDER BY object_name(st.objectid, dbid)
    
    SELECT StoredProcedure
    FROM #TMP
    GROUP BY StoredProcedure
    HAVING COUNT(*) > 1
    
    DROP TABLE #TMP

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    Thanks, I read Adam's post yesterday. But it's contrary to what is said here linky and Microsoft's explanation here.

    Each plan is stored once, unless the cost of the plan lets the optimizer know that a parallel execution might result in better performance (more on parallelism in Chapter 8). If the optimizer sees parallelism as an option, then a second plan is created and stored with a different set of operations to support parallelism. In this instance, one query gets two plans.
    What I believe is happening in my situation is two plans were created by different connections and are being used. One plan is causing timeouts because it is using an outdated plan. Still researching this as a possible issue across our servers.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Each plan is stored once" is not the same as "Each stored procedure has only one plan".
    I'm not a guru on internal server mechanics, but its my understanding that SQL Server now even stores partial query plans (change made a few versions ago).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    "Each plan is stored once" is not the same as "Each stored procedure has only one plan".
    I'm not a guru on internal server mechanics, but its my understanding that SQL Server now even stores partial query plans (change made a few versions ago).
    What I discovered was two plans were created because the set options were different for each connection that executed the stored procedure.

    I executed the (below) code and the set_options count was 4345 for the offending stored procedure. Now what this means is that to identify every option used in the stored procedure you have to subtract (link with table) to arrive at the set options below.

    • ARITH_ABORT
    • ANSI_NULL_DFLT_ON
    • QUOTED_IDENTIFIER
    • ANSI_NULLS
    • ANSI_WARNINGS
    • CONCAT_NULL_YIELDS_NULL
    • ANSI_PADDING


    Code:
    SELECT *   
    FROM sys.dm_exec_plan_attributes(0x050005003E375509404383C5030000000000000000000000)
    The second cached plan was using these options. So two different query execution plans were created. Now I have to figure out the impact and if the first one is used by the application. I can't flush the procedure cache on an individual plan in 2005.

    • ANSI_NULL_DFLT_ON
    • QUOTED_IDENTIFIER
    • ANSI_NULLS
    • ANSI_WARNINGS
    • CONCAT_NULL_YIELDS_NULL
    • ANSI_PADDING

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    With SQL 2005, they went from full batch plan caching to statement caching. They may have gone further than that recently, but I can't cite an article. i think I have read that when a plan is in use, it can only be used by that one connection for the duration of the execution. This means if you have the same procedure run many times by many connections, you would have several (usually identical) copies of that plan in the plan cache. This was easy to show in the SQL 2000 days, when we had the syscacheobjects view, as that included an object_id to tie back to a procedure, but I don't see an easy way of tieing a plan to a procedure in the sys.dm_exec_cached_plans view. At least in the 5 minutes I spent on it, anyway.

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MCrowley View Post
    ...I don't see an easy way of tieing a plan to a procedure in the sys.dm_exec_cached_plans view. At least in the 5 minutes I spent on it, anyway.
    Try:

    Code:
    USE master
    GO
    SELECT plan_handle,UseCounts,RefCounts, Cacheobjtype, Objtype, 
    ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, substring(TEXT,1,75) AS SQL 
    FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
    WHERE ISNULL(DB_NAME(dbid),'ResourceDB') = 'DATABASE_NAME'
    AND TEXT LIKE '%PROCEDURE_NAME%'
    ORDER BY dbid,usecounts DESC;
    GO

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    See. That would have taken me like .... 6 minutes to come up with ;-)

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MCrowley View Post
    See. That would have taken me like .... 6 minutes to come up with ;-)
    Lol! The problem still exist and I've kind of ruled out the query plans for now. I checked the set options in above post and nothing conflicts with each other. The sproc is really quite simple but it causes timeouts with the application and the only resolve is to recompile (alter) it. Seems to clear the connections and everything works for a couple days. Applications is blaming the database and I still suspect it is the gui causing the issues. Profiled it, pulled the query and ran it in analyzer without any notable problems. The execution plan is split between 41 and 59% cost base. Not locking or deadlocking. Just hangs. Hmmmm.....

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Any indications in the wait_type, or wait_resource columns of sys.dm_exec_request? Also, you should be able to extract the plan that the connection is actively using a combination of sys.dm_exec_requests and sys.dm_exec_query_plan. That would prove out the different query plan idea.

    Is the data varied in density? is there a particular case in the data that works better with a table scan vs an index seek?

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MCrowley View Post
    Any indications in the wait_type, or wait_resource columns of sys.dm_exec_request? Also, you should be able to extract the plan that the connection is actively using a combination of sys.dm_exec_requests and sys.dm_exec_query_plan. That would prove out the different query plan idea.

    Is the data varied in density? is there a particular case in the data that works better with a table scan vs an index seek?
    Code:
    wait_type	wait_time	last_wait_type	wait_resource
    PAGEIOLATCH_SH	203	PAGEIOLATCH_SH	5:1:34746157
    The orders table is too big for a scan, and it's indexed correctly. The wait stats are nominal. CXPacket waits are nill. There are covering indexes which are fine.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What are the index statistics like on the table?
    Code:
    select stats_date(object_id, index_id), index_id, name
    from sys.indexes
    where object_id = object_id('orders')
    It could be the table is large enough that the automatic stats updates are getting too far apart.

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MCrowley View Post
    What are the index statistics like on the table?
    Code:
    select stats_date(object_id, index_id), index_id, name
    from sys.indexes
    where object_id = object_id('orders')
    It could be the table is large enough that the automatic stats updates are getting too far apart.
    I run update stats nightly. I also reorg or rebuild indexes nightly. So stats were updated last night. Oh, and I have another nightly job that defrags the physical disks and it runs before the other jobs. Trying to keep everything in sync.

  14. #14
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    DB2 amd maybe other "greater SQL" products (T-SQL is what we call a "lesser SQL"), keep up to 16 execution plans for stored procedures. Based on their parameter list by looking for nulls, they pick one at invocation. There is also a "what are the stats like?" consideration.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •