Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2013-02-27 : 16:19:22
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 16:30:01
|
| 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 processb) How much effort SQL Server has to do to access the rows that you want to deletec) 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. |
 |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-02-27 : 17:26:06
|
| 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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 18:26:03
|
| 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. |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-05 : 23:40:19
|
| Adam machanic has a great script sp_whoisactive to find the current acctivities running on the sql server.That will give you more details. |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-03-07 : 15:53:03
|
| 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. |
|
|
|
|
|
|
|
|
why sql takes long time to delete some rows?