Solved

SQL SP under two same processing ID takes very slow

Posted on 2013-05-19
24
221 Views
Last Modified: 2013-05-22
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?
0
Comment
Question by:WTsuk
  • 12
  • 12
24 Comments
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179779
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?...
0
 

Author Comment

by:WTsuk
ID: 39179800
What is the sproc be lighter weight and takes less locks, execute faster?
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179804
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?
0
 

Author Comment

by:WTsuk
ID: 39179809
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.
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179810
That just means it's using two threads/workers.
0
 

Author Comment

by:WTsuk
ID: 39179813
I was the only user id 51 at that time.  It shows one id 51 is runnable and the other id 51 is sleeping.
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179826
Are you using Mulitple Active Result Sets?
0
 

Author Comment

by:WTsuk
ID: 39179839
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.
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179846
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?
0
 

Author Comment

by:WTsuk
ID: 39179849
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.
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179852
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?
0
 

Author Comment

by:WTsuk
ID: 39179866
yes now but it only took 5 to 10 min earler this morning.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Expert Comment

by:MattSQL
ID: 39179873
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 = ????
0
 

Author Comment

by:WTsuk
ID: 39179879
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.
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179892
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....
0
 

Author Comment

by:WTsuk
ID: 39179962
this got error.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysprocesses'.
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179967
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 = ????
0
 

Author Comment

by:WTsuk
ID: 39179992
I got two results.  What would like to know?
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39179994
Can you paste the results?
0
 

Author Comment

by:WTsuk
ID: 39179997
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
0
 
LVL 9

Expert Comment

by:MattSQL
ID: 39180001
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/
0
 

Author Comment

by:WTsuk
ID: 39180027
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.
0
 
LVL 9

Accepted Solution

by:
MattSQL earned 500 total points
ID: 39180029
SQL Server has parallelised the query and chosen to run a single execution of your sproc using two threads. Don't get distracted by this - the root cause of your issue looks to be IO based. Check the blog link above for some suggestions to progress...
0
 

Author Closing Comment

by:WTsuk
ID: 39187307
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.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now