February 14, 2019 at 12:49 pm
I found that the DMV sys.dm_exec_procedure_stats has multiple entries for the same object_id within the same database_id; sql_handle looks identical across all entries but the plan_handle is different, and they all have different metrics like execution count and so forth.
That means that SQL Server keep multiple plans for same S.P.? In this case how it decides which one to pick for the next execution?
The version is 2008.
Thanks
February 14, 2019 at 1:12 pm
SQL Guy 1 - Thursday, February 14, 2019 12:49 PMI found that the DMV sys.dm_exec_procedure_stats has multiple entries for the same object_id within the same database_id; sql_handle looks identical across all entries but the plan_handle is different, and they all have different metrics like execution count and so forth.That means that SQL Server keep multiple plans for same S.P.? In this case how it decides which one to pick for the next execution?
The version is 2008.
Thanks
Yes there can be multiple plans for a stored procedure. Everything needs to be exactly the same for the cached plan to be used which includes all set options. Set option differences is what I've usually found to be different. Those you can find by querying sys.dm_exec_plan_attributes and passing in the plan handle. This article explains things in more detail:
Multiple Plans for an "Identical" Query
Sue
February 14, 2019 at 1:43 pm
You can also get that if you don't include the schema name when referencing tables. For example:
SELECT ...
FROM table1
SQL will often generate a separate plan because theoretically "Keith" would get a different table than "Danny" when he ran the same code. SQL can't assume there's not a table specifically for that user.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply