February 25, 2008 at 8:37 am
Hi, I am monitoring a server thats running very slow. The server is a subscriber in transactional replication. Product information on the server is updated thru replication and its used by web users for browsing product catalog. I have a stored procedure that shows all blocker processes. I am seeing a process that runs 'create procedure GetProductOption......' while I am not recreating the stored procedure, nor are the developers. This is blocking web user processes that try to run something like 'Execute GetProductOptions 'LX1234''. May I know why the stored procdure is being created?
February 25, 2008 at 9:12 am
If I were you I would run a short trace on the server to see where the "create procedure" statement is comming from.
It may be that when the application code was written they put the "create procedure" statement before the execute. So every time they call the procedure they are trying to re-create it.
Its also possible it was created with a "recompile" option but I'm not sure you would see the results you are seeing if that is the case.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 25, 2008 at 9:30 am
I have seeen something similar to this happen. In this case, it was trying to grant access to a user right after the stored proc executes. What was happening was in the create script that we ran on production, it was missing a GO at the end of the stored proc. So the grant became a part of the Stored proc. It compiles fine and executes the stored proc, But after the select was done on the stored proc, it tried to grant to itself because iot became a part of the SP.
Maybe it is omething like that. Just a wild guess
-Roy
February 25, 2008 at 9:38 am
Another option in 2005 is to enable DDL triggers and create one for Create Procedure and insert the datain to a table you can query to see who or what is running the create.
You could also run sp_who2 @spid with the spid of the blocking process to see who is running the command.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2008 at 10:19 am
Are you using a DM to look at the blocking? I wrote a similar query a few weeks back and noticed that it would return the create proc instead of the statement within the proc.
February 25, 2008 at 10:21 pm
What method are you usind to see what's been excuted?
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
February 27, 2008 at 9:59 am
Use profiler to find out when the create procedure is called. You can then easily trace out what is happening and clear the rootcause.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 10:10 am
I tried tracing it yesterday, but I started too late. And today, I did not see any blocking. Its weird though that in the sysobjects table, the crdate shows a while back ago, so if the 'create procedure' had run the last couple of days, wouldn't the crdate be updated?
February 27, 2008 at 10:15 am
plus information_schema.routines also shows the modifieddate as a few months back ago too.
February 27, 2008 at 10:16 am
The only thing I can think of is that the create procedure is failing. Unless the code is doing a drop ahead of time the SP already exists so you shouldn't be able to use a CREATE on it anyway.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 27, 2008 at 10:17 am
Yes the create data would be updated. But we need to find out when this is firing out to find the root cause. there might be a procedure that might be wrongly calling this name.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 3:16 pm
I was able to capture it this morning. It was just trying to execute the proc instead of 'create proc', somehow a proc I created to monitor blocking shows 'create proc' on the blocking side and 'execute' on the blocker side. I am not sure why. But another interesting thing I found was why the 'exec' was running, it runs into blocking with all sorts of sps and queries, even though they are running against totally different tables and with (nolock) are used everywhere. The stored procedure select some data from a table called tblproductoption with (nolock) into a temp table, then use a cursor to loop through the temp table and return some result. Other processes that it blocks or being blocked by select some other tables. Not sure why they run into blocking with each other. My trace files just got deleted by our system engineer and I have to rerun it tomorrow to find out what resouces are being used.
February 28, 2008 at 4:43 pm
Do you meant to say that everytime the procedure is called its created and executed.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 9:12 pm
KATHLEEN Y ZHANG (2/28/2008)
The stored procedure select some data from a table called tblproductoption with (nolock) into a temp table, then use a cursor to loop through the temp table and return some result. Other processes that it blocks or being blocked by select some other tables. Not sure why they run into blocking with each other.
I think your problem is here. You are probably locking resources in tempdb that the other processes are needing. I have seen this happen when you have a lot of temp tables. It would be interesting to see if you could clean this up by changing your temp table to a table variable, or if there is a set-based solution to eliminate the cursor. You would be surprised, I know I have been, at the set-based solutions people come up with to eliminate cursors.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 28, 2008 at 10:38 pm
KATHLEEN Y ZHANG (2/28/2008)
somehow a proc I created to monitor blocking shows 'create proc' on the blocking side and 'execute' on the blocker side. I am not sure why.
How does your monitoring proc check what is running?
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 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply