we have a sql server 2005 database(Detailed version: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)). When we run a delete query of the following kind, it goes hanging for almost 20hrs. Now, we can not make out why this should be so. also, how do we go about tracing the query execution within the sql db? Please advise in detail.
Hanging Query :
If you run "select count(*) from maportal_r where a_webc_url='JANUA/a-b-c/x.pdf'" you'll get the amount of rows being deleted. If this is also running for a long time, check your indexes (or the lack of it)
answered Jan 11 '12 at 02:13 AM
Wilfred van Dijk
To add to @Wilfred van dijk answer to be able to help here we need to know...
answered Jan 11 '12 at 03:36 AM
Kev Riley ♦♦
First thing, run this query:
See if you can see the statement that has been running for 20 hours. See if it shows a blocking processing. If not, you're just seeing a very big and long running delete statement. From there you need to do all the things that @Kev Riley is recommending because it sounds like you might have indexing issues, but I'd start with blocking.
answered Jan 11 '12 at 04:02 AM
Grant Fritchey ♦♦
In addition to Wilfred's answer, there could be many more reasons for this as it really depends upon your environment like
How big your table is? Either searching 100 rows without a proper index in a billion rows table could result in a table scan? Would need a huge memory for that. Moreover, Deleting million of rows could be resource intensive and with full recovery model and low log file growth rate it would become worse
Are there CONSTRAINTS OR Foreign keys with CASCADE DELETE OPTION? Missing indexes on referencing tables would hinder the performance (Same as above key point) and there would be locks to be held on those tables as well. This could trigger a chain of DELETES and LOCKS.
Are there too many indexes? Deleting high number of indexes values could be an issue as well
So try to delete the records in small and short batches to avoid blocking and keep your log file in check. For e.g
If you do not have an index on your a_webc_url column, try to add one if the table is huge. Since you know your environment and DB structure better, you are in a better position to sort it out.
answered Jan 11 '12 at 04:16 AM