Solved

SQL SP under two same processing ID takes very slow

Posted on 2013-05-19
24
226 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 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