April 16, 2009 at 12:45 pm
I'm trying to ALTER a stored procedure. About half way down in the stored procedure's code, a reference is made to a database. At the time I was attempting to ALTER the stored procedure, the referenced database was having a lot of data inserted into it. The ALTER stored procedure statement wouldn't complete - it would hang. When I tried ALTERing the stored procedure without the code that accessed the database that was being heavily used, the stored procedure was ALTERed in a second. When I re-added the query that accessed the database that was being heavily used, the ALTER statement hung again.
Here's my question - when a stored procedure is being CREATEd or ALTERed, does part of that process involve access the databases that are being referred to in the stored procedure code? Is there anything else that could cause the ALTER to hang like that? That's the only thing I can think of but I don't understand how ALTERing a stored procedure attempts to access the databases that are referred to inside it.
Any ideas?? This is happening on a 32 bit, Standard Edition box with 4 procs and 4 Gb of memory, running on Window Server 2003 R2. It's pretty lightly used.
April 16, 2009 at 12:51 pm
Willem Tilstra (4/16/2009)
I'm trying to ALTER a stored procedure. About half way down in the stored procedure's code, a reference is made to a database. At the time I was attempting to ALTER the stored procedure, the referenced database was having a lot of data inserted into it. The ALTER stored procedure statement wouldn't complete - it would hang. When I tried ALTERing the stored procedure without the code that accessed the database that was being heavily used, the stored procedure was ALTERed in a second. When I re-added the query that accessed the database that was being heavily used, the ALTER statement hung again.Here's my question - when a stored procedure is being CREATEd or ALTERed, does part of that process involve access the databases that are being referred to in the stored procedure code? Is there anything else that could cause the ALTER to hang like that? That's the only thing I can think of but I don't understand how ALTERing a stored procedure attempts to access the databases that are referred to inside it.
Any ideas?? This is happening on a 32 bit, Standard Edition box with 4 procs and 4 Gb of memory, running on Window Server 2003 R2. It's pretty lightly used.
Hi,
Yes, Altering a stored procedure needs to access any objects you have within this procedure. It needs to compile the procedure, so it needs to check if tables, functions, column names or views exist. It's like parsing a sql statement within a query window.
Cheers,
J-F
April 16, 2009 at 1:08 pm
So it makes sense to you that I would see an ALTER stored procedure hang up (and I mean hang up - I let it go for 4 minutes at one point) if one of the referenced databases had a table that was being very heavily used?
April 16, 2009 at 4:35 pm
I would simply run sp_who2 and see if it's being blocked by something and if so, what. If there's a long running transaction or lots of transactions blocking the resources that the procedure needs to look at, that could be the cause. It's not exactly normal, but it's understandable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2009 at 4:50 pm
That is a good clue for me. The SQL code that was inserting data into the databas table was one long series of INSERT.... SELECT FROM with no GO separating the individual statements - in essence, one long running transaction. I noticed it and mentioned it to the developer but didn't put the two together.
Thanks for your comments, they've helped me understand SQL Server a little bit better.
April 16, 2009 at 8:05 pm
Excellent. Glad I could help. And thank you for posting the solution. I hate wondering what worked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply