May 3, 2011 at 11:52 am
I'm getting 76 different cached execution plans for the same stored procedure.
What are the implications for performance on my server and how unusual is this situation?
Any thoughts would be appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 3, 2011 at 11:53 am
Here is the code:
SELECT
P.usecounts
,P.cacheobjtype
,H.query_plan
,LEFT([sql].[text], 1000) as [text]
FROM
sys.dm_exec_cached_plans P
CROSS APPLY
sys.dm_exec_query_plan(plan_handle) H
OUTER APPLY
sys.dm_exec_sql_text (p.plan_handle) [sql]
WHERE objtype ='Proc' AND LEFT([sql].[text], 1000) LIKE @procName;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 3, 2011 at 11:59 am
Makes no sense. Normally you could get a few plans for different connection settings but this is ridiculous.
Are use sure those are called via RPC and not via adhoc?
Do yo have dynamic sql in the proc (might explain it, not sure)?
May 3, 2011 at 12:04 pm
Ninja's_RGR'us (5/3/2011)
Makes no sense. Normally you could get a few plans for different connection settings but this is ridiculous.Are use sure those are called via RPC and not via adhoc?
Do yo have dynamic sql in the proc (might explain it, not sure)?
I'm also shocked with the number of plans.
There is no dynamic sql in the proc and these are definitely plans of the proc, unless there is something wrong with my query.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 3, 2011 at 12:07 pm
Marios Philippopoulos (5/3/2011)
Ninja's_RGR'us (5/3/2011)
Makes no sense. Normally you could get a few plans for different connection settings but this is ridiculous.Are use sure those are called via RPC and not via adhoc?
Do yo have dynamic sql in the proc (might explain it, not sure)?
I'm also shocked with the number of plans.
There is no dynamic sql in the proc and these are definitely plans of the proc, unless there is something wrong with my query.
The query looks fine and seems to return valid results on my side.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 1:03 pm
I found this link reporting similar issue:
http://www.sqlservercentral.com/Forums/Topic979929-360-1.aspx#bm979947
Does anyone have a query that looks at a bunch of exec plans and reports on differences?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 3, 2011 at 2:41 pm
Is this stored procedure called by different users? And, are all objects referenced in the stored procedure at least schema qualified?
This could be a case where the procedure is getting called by different users - and a plan is generated for each user/schema. When a user is created, by default a schema is also created for that user that matches (e.g. userA will have a default schema of userA).
In this scenario, when each user calls that stored procedure - and when the objects are not schema qualified, a plan is generated for that schema. So, if you have the following:
SELECT ...
FROM MyTable
WHERE ...
And, you have 2 users (userA and userB) and each user has a different default schema - two separate plans will be generated for each schema.
To fix this, you can either modify the code so all objects are schema qualified or you can modify the users so they all have the same default schema. When all objects are schema qualified - the plan will be generated for that schema (to be exact, each statement's part of the plan). When each user has the same default schema, the plan will be generated for that default schema and will not be generated again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 3, 2011 at 2:45 pm
Completely forgot about that one... and likely your best shot at this point.
A similar issue is that the server is experiencing a lot (A LOT) of recompiles. Not using schema.spname was one of the cultprits.
The other one was not using the same case in the sp_name. But I doubt it would explain all 76 plans.
May 4, 2011 at 8:01 am
Thank you all for your responses.
There are indeed at least a couple of 'sins' committed by the devs here that are causing recompilations and are likely the culprit.
(Incidentally, the number of plans dropped down to 7 this morning, still more than enough, and likely to fluctuate).
(1) SET statements at the top of the proc body:
...
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
...
I'm not sure why this is in the code, I will need to follow up with the devs on this.
(2) Table names not qualified with the schema name
This one is a definite no-no. I will ask them to change their code to fully qualify the object names.
I appreciate all the feedback!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 4, 2011 at 8:14 am
Ya set options + un-qualified (objects :hehe:) = a buttload of plans and wasted cpu cycles.
I could have understood 2-3 plans if you had vastly different amount of rows in the resultsets depending on input parameter, but 76 is not an valid scenario.
May 4, 2011 at 11:22 am
Hm, I almost NEVER qualify my table names with the schema names, particularly dbo.
This is a bit of an eye-opener for me. Can someone point me in the direction of an article or blog or whatnot that explains what the significant issue here is? Is it only when you have multiple SQLLogins or don't set database user schemas and they use the defaults that this occurs?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 4, 2011 at 11:30 am
I can't find the article I had in mind (really old, like sql 2000).
That one looks decent.
http://www.sqlservercentral.com/articles/Performance+Tuning/ospr/666/
May 4, 2011 at 11:32 am
Looks nice too (still searching) : http://www.sqlservercentral.com/articles/T-SQL/62061/
May 4, 2011 at 11:33 am
May 4, 2011 at 11:43 am
Thanks very much for the google-fu'ing. I'll be over here reading... and getting my jaw back off the floor.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply