Stored porcedure being blocked

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 porcedure being blocked

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2009-11-01 : 05:28:17

Hi,
i have a stored procedure that update a some table but sometimes the table i update i open it in query analyzer and select records from it.
the stored procedure will take time executing and will timeout.
how to make the stored proc. run and there is another session opened for the table?

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-01 : 21:48:39

Are the tables indexed? Are the indexes defragmented? How often do you run update statistics and what's the sampling ratio?

Can you post the code of the stored procedure?

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

ann06
Posting Yak Master

171 Posts

Posted - 2009-11-02 : 01:09:27

the table doesnt have too much records its only a few, but the problem is the session opened in the QA will block the SP from executing shall i make isolation level of the stored proc to be read uncommitted?
here is the core of the sp

INSERT INTO MAIL_SENT (CONTACT_ID,APP_NAME,[FROM],[TO],CC,BCC,SUBJECT,SUBJECT_AR,BODY,BODY_AR,PRIORITY,DATE_IN,TIME_IN,REQUEST_NO) SELECT CONTACT_ID,APP_NAME,[FROM],[TO],CC,BCC,SUBJECT,SUBJECT_AR,BODY,BODY_AR,PRIORITY,DATE_IN,TIME_IN,REQUEST_NO FROM MAIL_OUT WHERE MO_NO=@mo_no
IF @@ERROR <>0 begin set @Errortxt='Error15' goto ERRBLOCK end

update mail_sent
set [cc]='sent'
where sent_id= (select max(sent_id) from mail_sent)

DELETE FROM MAIL_OUT WHERE MO_NO=@mo_no
IF @@ERROR <>0 begin set @Errortxt='Error15' goto ERRBLOCK end

if iam opening the table mail_sent or Mail_out in QA sometimes blocking happens

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-02 : 10:58:17

Do not use read uncommitted. If you want to switch isolation levels, then use read_committed_snapshot. You need to understand the differences between them before making any changes. Do not just change them to fix one particular stored procedure.

You haven't answered my questions, so it's hard to help.

Also, how are you opening the tables?

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources