Stored Procedure Failure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Failure

Author

Topic

woodz93
Starting Member

9 Posts

Posted - 2009-02-06 : 23:27:21

I'm new to this site, sorry. My dilemma is that after planning / building a database and populating it with data, we are starting now to invoke queries in .asp pages. It is a SQL 2000 relational database, and some tables run fine (mainly INSERT) using stored procedures invoked by the page. One procedure is really irking my brain, it is an UPDATE on a table that has NO foreign key relationships, it is just a table with a primary key (cust_id).

CREATE PROCEDURE inv_UpdateCustodian
(
@cust_fname varchar(25),
@cust_lname varchar(25),
@cust_email varchar(50),
@cust_address varchar(200),
@cust_phone varchar(15),
@cust_island varchar(15),
@cust_2email varchar(50),
@cust_id int
)
AS
UPDATE inventory_custodian
SET cust_fname = @cust_fname,
cust_lname = @cust_lname,
cust_email = @cust_email,
cust_address = @cust_address,
cust_phone = @cust_phone,
cust_island = @cust_island,
cust_2email = NULLIF (@cust_2email, '')
WHERE cust_id = @cust_id
GO

I have tried everything from raising timeout via asp command.timeout to inserting NOCONSTRAINT in stored procedure and nothing works - this stored procedure will time out no matter what - unless i remove the primary key declaration. If there is something i missed / did not try, help me out here. Again, apologies if this is in a wrong forum or if it's so easy that i can't figure it out. Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-07 : 00:33:28

If you execute the stored procedure inside Query Analyzer with appropriate parameter values, how long does it take to complete? When it times out, have you looked for blocking and also viewed what's going on inside SQL Profiler?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 07:47:54

is cust_id your primary key? ALso as Tara suggested, when you run the query in query analyser, analyse the execution plan and see what ere bottleneck steps if its taking large amount of time

woodz93
Starting Member

9 Posts

Posted - 2009-02-07 : 15:13:18

When the update is run in Query Analyzer, it runs < 1 second. I've done checks on blocking, however one thing that i can't put a finger on is when i use Aqua Data Studio and run the Session Manager. I run the query on the asp page and then look at the session manager. I see the process sleeping, and I also find that the query has a 'WAIT RESOURCE' value 'KEY:' followed by some sort of number. The blocked by and blocking values are zero. Hope that helps, thank you all again for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-07 : 15:54:14

If it runs in less than a second in Query Analyzer and there is no blocking, then you've got an issue in your application. I don't have any idea what Aqua Data Studio or Session Manager are.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

woodz93
Starting Member

9 Posts

Posted - 2009-02-08 : 16:06:03

Aqua Data Studio is another database management program similar to Enterprise Manager. If we try diagnosing via Enterprise manager, I do find that under the Management tab, spids under Locks / Process ID correspond to the processes ids that are listed in the Current Activity shot taken upon execution of the queries in question. So you have a lock there? If so, how can i address it? I don't know if it is the application that has issues, it seems simple enough. The application uses an SQLOLEDB connection to connect, the update procedure is invoked by passing the stored procedure name followed by parameters (i.e. "sp_call" x, y, z)? Insert stored procedures work fine too. Thanks again, i think we're on to something here.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-08 : 17:23:41

Run sp_who2 while the stored procedure is running via the application and before it times out. Does it show any blocking in there?

Locks are normal. SQL Server has to lock the data when you are modifying something, such as with INSERT/UPDATE/DELETE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

woodz93
Starting Member

9 Posts

Posted - 2009-02-08 : 17:34:52

the spid appears in the BlkBy column! as a precaution, other invocations of stored procedures are commented out on the page to isolate this single stored procedure. so i made sure that at that moment in time, only that stored procedure runs. where do we go from here? I don't know what else to do to restructure that query to run more efficiently.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-08 : 19:31:23

Check what spid is blocking it and then run this:

DBCC INPUTBUFFER(spidNo) --put the blocking spid here

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

woodz93
Starting Member

9 Posts

Posted - 2009-02-09 : 13:07:59

Ok, so i ran it, and it returned a 'Language Event' and an Update Query. Quite puzzled about that query - maybe it is on an included file that runs with the ASP page. You're really a big help, tkizer. Are we on to something?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-09 : 14:17:14

I'd suggest running SQL Profiler to see exactly what queries are also running. Start the trace just before you run the stored procedure and stop it after you receive the timeout message. Make sure to check sp_who2 for the blocking spid number, so that you can see exactly all queries that the particular spid was running. Post it here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

woodz93
Starting Member

9 Posts

Posted - 2009-02-09 : 14:31:09

declare @P1 int
set @P1=180150019
declare @P2 int
set @P2=1
declare @P3 int
set @P3=2
declare @P4 int
set @P4=1
exec sp_cursoropen @P1 output, N'SELECT cust_id from inventory_custodian where id = 108', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150019, 16, 1, 1
go
declare @P1 int
set @P1=1
declare @P2 int
set @P2=1
exec sp_cursorfetch 180150019, 256, @P1 output, @P2 output
select @P1, @P2
go
declare @P1 int
set @P1=180150020
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=1
exec sp_cursoropen @P1 output, N'SELECT inventory_id FROM users WHERE id = ''194''', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150020, 16, 1, 1
go
declare @P1 int
set @P1=1
declare @P2 int
set @P2=1
exec sp_cursorfetch 180150020, 256, @P1 output, @P2 output
select @P1, @P2
go
UPDATE users SET inventory_id = 3 where id = 194
go
declare @P1 int
set @P1=180150000
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=9
exec sp_ddopen @P1 output, N'sp_sproc_columns', @P2 output, @P3 output, @P4 output, N'pdateCustodian', NULL, NULL, NULL
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorfetch 180150000, 2, 1, 1
go
exec sp_cursorclose 180150000
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go
exec pdateCustodian 'test32', 'test22', 'test', 'test', '111111111', 'test', 'test', 108
go
exec sp_cursorclose 180150019
go
exec sp_cursorclose 180150020
go
SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go

The spid in question is 53, and EventType is RPC Event. Hope that helps.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-09 : 14:57:23

I can't tell from that output what is causing it. I have a feeling it's this part though: "SELECT cust_id from inventory_custodian where id = 108'". My guess is that the application is locking the data on that SELECT query and then the UPDATE gets blocked.

You could add lock information to the trace, although that might produce a lot of rows and therefore it would be hard to troubleshoot over the Internet on this. Instead, I'd recommend running sp_lock BlockingSpidGoesHere while the update stored procedure is running. You'll need to first get the spidNo from sp_who2 again as it can change each time you do this. Make sure you are viewing the blocking culprit spid and not the spid for the update. You'd first locate the spid for the update and then look at the BlkBy column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

woodz93
Starting Member

9 Posts

Posted - 2009-02-09 : 15:26:49

Ok, so this time i scrutinized EVERY include directive on the web page, and have found that one of the includes basically open a connection, fetches data, and then a separate directive closes the I/O stream at the very end of the page. So, based on what you are saying, the I/O requests are 'hanging' and as a result blocking others from committing? Just to experiment, i retooled the page so that the connection opens/closes after EACH query as opposed to opening upon loading the page and closing when the page has been loaded. Guess what - it worked! There could be performance issues to this though, i'm sure...? Let know what you think.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-09 : 15:31:56

Yes that could cause a performance issue, just depends on how fast it can connect each time. Typically you reuse the connections though. I'd fix the locking issue rather than connecting/disconnecting each time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

woodz93
Starting Member

9 Posts

Posted - 2009-02-09 : 16:06:45

Ok...sorry to change the subject on you. Agreed, not a good idea.

So i run sp_lock(SPID) and i get four columns with information:
spid dbid ObjId IndId Type Resource Mode Status
53 8 0 0 DB S GRANT
53 8 878626173 0 TAB IX GRANT
53 8 878626173 0 PAG 1:372 IU GRANT
53 8 878626173 0 RID 1:372:11 U GRANT

Thanks again for all your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-09 : 16:23:11

SELECT OBJECT_NAME(878626173)

You'll need to figure out why the application is locking that object. It's all in the application code at this point.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

woodz93
Starting Member

9 Posts

Posted - 2009-02-09 : 16:30:48

Brilliant job, tkizer! I spent a week being a sleuth and only got halfway here (i think). I'll get to work on the application. You are indeed the SQL goddess! I'll ask away again should i need more insight.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-09 : 20:00:53

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources