|
|
|
SSC Journeyman
 
Group: General Forum Members
Last Login: Thursday, February 11, 2010 10:20 AM
Points: 87,
Visits: 128
|
|
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?
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, February 11, 2010 10:20 AM
Points: 87,
Visits: 128
|
|
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?
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 17,110,
Visits: 12,214
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
