Link to home
Create AccountLog in
Avatar of WTsuk
WTsuk

asked on

SQL SP under two same processing ID takes very slow

I have one SP (store prodecure) in my sql 2000 that will not respond thru vb.net application. The SP was under two of same Processing ID in Last TSQL Command Batch.  I killed both and restart Application then worked.  How to avoid this problem?
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

If the stored procedure is already running, you need to ask is it taking locks on certain objects? The two from the ID that you found were probably blocking any other attempts to run the same sproc.

As to how to avoid this - this is quite a big question. If the sproc is taking locks, and only a single instance can be running - do you need to have code at the application level that checks for this? Or should the sproc be lighter weight? Take less locks, execute faster?...
Avatar of WTsuk
WTsuk

ASKER

What is the sproc be lighter weight and takes less locks, execute faster?
Well, if the first execution of the sproc has blocked the second execution. Then I want to ask, was the first execution running normally? Was it taking too long? If so why?
Avatar of WTsuk

ASKER

It only execute one time but shows two same id under tsql command batch.  sometimes it runs fine some times it rund id 51 and sleeping id 51.
That just means it's using two threads/workers.
Avatar of WTsuk

ASKER

I was the only user id 51 at that time.  It shows one id 51 is runnable and the other id 51 is sleeping.
Are you using Mulitple Active Result Sets?
Avatar of WTsuk

ASKER

What is multiple active result sets?  I do know it started after I ran update sql for two days.  one update took 9 hours and the second update took 29 hours.  One table have 7 million and the other table have 30 million.
Okay - let's get back to your original question. You have a sproc that will not respond to your application - what do you mean by this? Are you getting an error or is it timing out?

You then say you have two occurrences under the same process id - can you be specific here. How are you finding this information?
Avatar of WTsuk

ASKER

There is no message.  It runs but takes 3 hours..  It  should be done within 10 minutes.  I found under db under management - current activity - processing info.
Okay - now we're getting somewhere. So the sproc is taking too long to run and we need to find out why.

Is the sproc still running?
Avatar of WTsuk

ASKER

yes now but it only took 5 to 10 min earler this morning.
Ok can you find the session_id and then run.

select blocked, waittime, lastwaittype, cpu, physical_io, memusage, login_time, last_batch, open_tran, [status],cmd from sys.sysprocesses
where spid = ????

select start_time, [status], command, blocking_session_id, wait_type, last_wait_type, cpu_time, total_elapsed_time, reads, writes, percent_complete from sys.dm_exec_requests
where session_id = ????
Avatar of WTsuk

ASKER

I am a new learner and do not know what are those.  I run same application test server.  It run fine on test server.  Jutst have problem on production server.
Please run those queries against the SQL server that is currently running your stored procedure so we can try and find out why it's taking so long....
Avatar of WTsuk

ASKER

this got error.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysprocesses'.
Ah okay. Sorry - should have checked your server version. Can you try:

select blocked, waittime, lastwaittype, cpu, physical_io, memusage, login_time, last_batch, open_tran, [status],cmd from master.dbo.sysprocesses
where spid = ????
Avatar of WTsuk

ASKER

I got two results.  What would like to know?
Can you paste the results?
Avatar of WTsuk

ASKER

Could not copy header


0      272828      CXPACKET      70843      15458      663      2013-05-19 23:39:01.613      2013-05-19 23:41:39.490      2      sleeping                            DELETE          
0      0      IO_COMPLETION      272625      15458      663      2013-05-19 23:39:01.613      2013-05-19 23:41:39.490      2      runnable                            DELETE
No problems.

Your sproc is running as two threads. The first thread is sleeping and waiting for the other thread to complete. The second thread is waiting for an IO operation to complete. There's some good pointers to investigating this issue here:

http://blog.sqlauthority.com/2011/02/10/sql-server-io_completion-wait-type-day-10-of-28/
Avatar of WTsuk

ASKER

The Application only execute the SP once.  I do not understand why two threads are running.  It is getting dont but takes a very long time.
ASKER CERTIFIED SOLUTION
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of WTsuk

ASKER

I appreciate your help.  Your comment gave me a some idea where to look at the problem.  I took off one of logic DELETE  and INSERT the TABLE in SP then the application run nomal.  I am still investigating the paticular TABLE what caused this problem.  Thank you.