November 19, 2009 at 9:25 am
I have updated a procedure to change from MAXDOP 0 to MAXDOP 2. It has been in production for over 1 week now and according to my sql server monitoring software, it is still running the old version that has MAXDOP 0.
I have tried sp_recompile, exec [procname] with recompile, and our dba has tried freeproccache.
What else can we do without recycling the sql server in order for these changes to take affect?
Please help! Production running very poorly right now!
November 19, 2009 at 10:02 am
By the way, SQL Server 2005 running in 2000 compatibility mode (80 I think). Running on Windows Server 2003 sp2
November 20, 2009 at 7:49 am
Wow! A failover of the sql server did NOT fix my stored procedure.
As a side question to this, I'm trying to query for the execution plan for this specific proc and I'm a little stumped with the query. So far, this is what I have:
use master
go
select *
from sys.dm_exec_cached_plans cp
Cross Apply sys.dm_exec_query_plan(cp.plan_handle)
Inner Join sys.objects o On o.objectid =
I'm not exactly sure how the cross apply works, but if you take out the inner join line and run it, it returns all of the cached plans. If I want to see the name of the proc/trigger that is displayed, it looks like the database and object id is returned from the sys.dm_exec_query_plan function, can I throw an alias on that function in the cross apply line so I can do the inner join on the next line to get the real object name?
November 20, 2009 at 9:13 am
Double check the code of the procedure to make sure somehow the old version didn't get re-applied over your changes. When you change a proc, the very next call will use the new code. Only ones that are currently running will keep the old
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2009 at 9:15 am
select <column list>
from sys.dm_exec_cached_plans cp
Cross Apply sys.dm_exec_query_plan(cp.plan_handle) qp
Inner Join sys.objects o On o.objectid = qp.object_id
Run this in the DB that the object is in rather than master, otherwise you'll get no rows because the matching object won't be in sys.objects.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2009 at 9:18 am
Hi Gail, thanks for the responses...I'll work on the query plan stuff in a little bit.
In regards to your first reponse, if I go to Management Studio, navigate to the procedure and right-click and say Modify (or any of the options that will show the sql code in the query window), they all have the new code that is SUPPOSED to be executing. Our DBA at my company is baffled...we're getting ready to open a Microsoft ticket.
November 20, 2009 at 9:22 am
How do you know it's not executing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2009 at 9:25 am
Quest Performance Analysis shows it in it's "Top 25" CPU consuming procedures and when I click on "View SQL Text", it doesn't have my code change, it still says MAXDOP 0.
Edit: Also, that's why I was trying to get that query for the execution plan so I could triple-check that the code is what is being reported by Quest.
November 20, 2009 at 9:32 am
Double (triple) check that you're looking at the same server from the Quest tool and management studio. Double (triple) check that it's the same database. Double (triple) check that it's exactly the same stored procedure name and the same schema name.
This has come up here before (multiple times) and every time it's been that the person's looking at different servers, different databases or different schemas.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2009 at 10:19 am
GilaMonster (11/20/2009)
select <column list>from sys.dm_exec_cached_plans cp
Cross Apply sys.dm_exec_query_plan(cp.plan_handle) qp
Inner Join sys.objects o On o.objectid = qp.object_id
Run this in the DB that the object is in rather than master, otherwise you'll get no rows because the matching object won't be in sys.objects.
Tells me "incorrect syntax near '.' " on the Cross Apply line when I run this in the corresponding database, but when I switch back to master it runs (but I cancel because it seems to take too long before I get any results.
November 20, 2009 at 10:25 am
GilaMonster (11/20/2009)
Double (triple) check that you're looking at the same server from the Quest tool and management studio. Double (triple) check that it's the same database. Double (triple) check that it's exactly the same stored procedure name and the same schema name.This has come up here before (multiple times) and every time it's been that the person's looking at different servers, different databases or different schemas.
As a follow-up to this response...
I have triple-checked and I am going against all of the correct stuff (correct database, we only have one schema (dbo)). The procedure in question is not showing up anymore in quest today. When I said the failover didn't work, I was partly wrong because it is the "other" proc that I changed from MAXDOP 0 to MAXDOP 2 that is showing up now. Here's what I "think" is going on, and my question.
If a proc's plan is cached, and I make changes to the procedure via an "Alter" statement, does SQL Server know that it needs to rebuild the execution plan with the new code? Reason I ask is because last night after COB and the failover, I ran a job that "Dropped the proc", then did "dbcc freeproccache", then "Create Proc". Since then, we aren't seeing that first proc show up, now the other one...
Is it because of the alter statement instead of doing a drop/create statement?
November 20, 2009 at 10:26 am
GilaMonster (11/20/2009)
Double (triple) check that you're looking at the same server from the Quest tool and management studio. Double (triple) check that it's the same database. Double (triple) check that it's exactly the same stored procedure name and the same schema name.This has come up here before (multiple times) and every time it's been that the person's looking at different servers, different databases or different schemas.
As a follow-up to this response...
I have triple-checked and I am going against all of the correct stuff (correct database, we only have one schema (dbo)). The procedure in question is not showing up anymore in quest today. When I said the failover didn't work, I was partly wrong because it is the "other" proc that I changed from MAXDOP 0 to MAXDOP 2 that is showing up now. Here's what I "think" is going on, and my question.
If a proc's plan is cached, and I make changes to the procedure via an "Alter" statement, does SQL Server know that it needs to rebuild the execution plan with the new code? Reason I ask is because last night after COB and the failover, I ran a job that "Dropped the proc", then did "dbcc freeproccache", then "Create Proc". Since then, we aren't seeing that first proc show up, now the other one...
Is it because of the alter statement instead of doing a drop/create statement?
November 20, 2009 at 12:23 pm
gregory.anderson (11/20/2009)
If a proc's plan is cached, and I make changes to the procedure via an "Alter" statement, does SQL Server know that it needs to rebuild the execution plan with the new code?
Yes. Any modification of an object (table, view, stored proc, function) immediately invalidates all plans based on that object. They will recompile on the next execution.
The reason your query's not working is because of the compat mode of the databases (CROSS APPLY needs mode 90). Try running it in master and use 3-part naming to get the correct sys.objects (the one in the DB that you are working in)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply