SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 why sql takes long time to delete some rows?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

247 Posts

Posted - 02/27/2013 :  16:19:22  Show Profile  Reply with Quote
Hi there,

Someties when I tried to remove something from a table it took just couple of seconds to do the job while other times it just hangs on there, seeming the process is endless. I noticed this happened to the other jobs. Same job with couple of sp, it may take 5 min, in most cases, while other times take 20 minutes. Why is that? Can I do anything to improve that?

Thanks in advance.

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 02/27/2013 :  16:30:01  Show Profile  Reply with Quote
Are you deleting those via a stored procedure, or are you writing an adhoc query (i.e., a delete statement)? In either case, the time taken to delete can depend on a number of factors, for example:
a) whether the row(s) (or pages or table) is locked by another process
b) How much effort SQL Server has to do to access the rows that you want to delete
c) Load on the system i.e., other queries that may be running etc.

If you are deleting through a stored procedure, in addition to all of the above, it can also depend on whether the stored procedure ends up using an ill-suited query plan.

To improve the situation, look at what is causing the delay. You can do couple of things: a) look at the query plan to see what part of the query is taking the time (press control-m before you run the query to enable display of query plan). b) while the long query is running, in another window, run sp_who2 - this will show you if your process is being blocked by another.
Go to Top of Page

allan8964
Posting Yak Master

247 Posts

Posted - 02/27/2013 :  17:26:06  Show Profile  Reply with Quote
Thanks James for quick reply!
1) All rows I delete are not related to any other tables.
2) For Query Plan, how can I see any results from that? I followed your instruction then I did see annything happened.

Thanks.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 02/27/2013 :  18:26:03  Show Profile  Reply with Quote
There should be a new tab in the query results pane labeled Execution Plan. That will show you how SQL Server is carrying out your request, and it will show you relative costs of various steps. If you are simply deleting from a single table, it is likely that it is doing a table scan to find the rows that you are interested in, if that indeed is the problem.

Also, look at if there is anything blocking the process as I had described earlier.
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
649 Posts

Posted - 03/05/2013 :  23:40:19  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Adam machanic has a great script sp_whoisactive to find the current acctivities running on the sql server.That will give you more details.
Go to Top of Page

allan8964
Posting Yak Master

247 Posts

Posted - 03/07/2013 :  15:53:03  Show Profile  Reply with Quote
Thanks guys. I got both, execution plan and sp_WhoIsActive. pretty good script but need some time to play with it.
Thanks again for the help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000