Solved

SQL SP under two same processing ID takes very slow

Posted on 2013-05-19
24
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
24 Comments
 
LVL 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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
 
LVL 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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 10

Expert Comment

by:Matt Bowler
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 10

Accepted Solution

by:
Matt Bowler 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 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