InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 05:42:31 GMT20http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Lynn Pettis (6/12/2008)[/b][hr]Unfortunately, it doesn't look like the OP has been on SSC since yesterday morning. Hope he checks this thread and let's us know what's happening.:cool:[/quote]I think that happens more that we like ;)Thu, 12 Jun 2008 16:08:55 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxUnfortunately, it doesn't look like the OP has been on SSC since yesterday morning. Hope he checks this thread and let's us know what's happening.:cool:Thu, 12 Jun 2008 15:13:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Lynn Pettis (6/12/2008)[/b][hr][quote][b]Matt Miller (6/12/2008)[/b][hr]Makes me wonder if it wouldn't be easier to just to the select...INTO on stuff you want to keep, blow out the original table and reinsert the items to keep.....I don't recall hearing how many rows would be left after this little adventure.....Either way - deleting that much in one chunk is bound to jam things up a bit. Perhaps deleting in smaller chunks would help, too.[/quote]That is why I made the second code suggestion.:cool:[/quote]yup - missed it! :PThu, 12 Jun 2008 15:02:22 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Lynn Pettis (6/12/2008)[/b][hr]Which track is right, the coding of the delete (I offered 2 solutions) or the fragmentation (disk and data)?[/quote]I was talking about the DELETE code... but, yes, I believe both tracks are correct and both need to be considered. It may also be that someone made the log smaller and they got bit by unexpected growth during the delete. That would certainly "Hang" the system until the growth completed and the delete completed.Thu, 12 Jun 2008 14:54:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Matt Miller (6/12/2008)[/b][hr]Makes me wonder if it wouldn't be easier to just to the select...INTO on stuff you want to keep, blow out the original table and reinsert the items to keep.....I don't recall hearing how many rows would be left after this little adventure.....Either way - deleting that much in one chunk is bound to jam things up a bit. Perhaps deleting in smaller chunks would help, too.[/quote]That is why I made the second code suggestion.:cool:Thu, 12 Jun 2008 14:52:55 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxMakes me wonder if it wouldn't be easier to just to the select...INTO on stuff you want to keep, blow out the original table and reinsert the items to keep.....I don't recall hearing how many rows would be left after this little adventure.....Either way - deleting that much in one chunk is bound to jam things up a bit. Perhaps deleting in smaller chunks would help, too.Thu, 12 Jun 2008 14:44:06 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Jeff Moden (6/12/2008)[/b][hr][quote][b]Lynn Pettis (6/12/2008)[/b][hr][quote][b]Jeff Moden (6/12/2008)[/b][hr]Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?[/quote]True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.:cool:[/quote]Correct, WITH (NOLOCK) only affects SELECTs.I just can believe the delete of 11 million rows didn't get caught blocking before. Somethings not quite right here. I think Lynn is on the right track... I've seen it where the undocumented ability to DELETE alias has bitten folks before. Gotta follow the rules and delete from a table name, instead.[/quote]Which track is right, the coding of the delete (I offered 2 solutions) or the fragmentation (disk and data)?:cool:Thu, 12 Jun 2008 14:35:07 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Jeff Moden (6/12/2008)[/b][hr][quote][b]Lynn Pettis (6/12/2008)[/b][hr][quote][b]Jeff Moden (6/12/2008)[/b][hr]Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?[/quote]True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.:cool:[/quote]Correct, WITH (NOLOCK) only affects SELECTs.I just can believe the delete of 11 million rows didn't get caught blocking before. Somethings not quite right here. I think Lynn is on the right track... I've seen it where the undocumented ability to DELETE alias has bitten folks before. Gotta follow the rules and delete from a table name, instead.[/quote]Sometimes there is no tracking in the amount of rows on tables and a sudden increase can be a "surprise" ;)Thu, 12 Jun 2008 14:30:06 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Lynn Pettis (6/12/2008)[/b][hr][quote][b]Jeff Moden (6/12/2008)[/b][hr]Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?[/quote]True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.:cool:[/quote]Correct, WITH (NOLOCK) only affects SELECTs.I just can believe the delete of 11 million rows didn't get caught blocking before. Somethings not quite right here. I think Lynn is on the right track... I've seen it where the undocumented ability to DELETE alias has bitten folks before. Gotta follow the rules and delete from a table name, instead.Thu, 12 Jun 2008 14:14:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxCould it be a case of your disks filling up with the transaction log?have you checked for free space?Thu, 12 Jun 2008 10:28:39 GMTsteveb. http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Jeff Moden (6/12/2008)[/b][hr]Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?[/quote]True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.:cool:Thu, 12 Jun 2008 10:13:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxNot sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?Thu, 12 Jun 2008 08:37:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxTry this...DELETE FROM tbItem l (nolock) Where exists(Select p.POID From tbProductionOrder p (nolock) where l.POID = p.POID AND PODescription LIKE '%00000%')Thu, 12 Jun 2008 05:05:48 GMTSangeeta Satish Jadhav-362431http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxYes, please do. I'd be interested in reading that if you find it.Thanks Lynn!Wed, 11 Jun 2008 15:10:46 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]Matt Miller (6/11/2008)[/b][hr][quote][b]John Rowan (6/11/2008)[/b][hr]Also, your WHERE clause of PO.PODescription LIKE '%00000%' will normally produce a table scan. It's a bit hard to say for sure though as you have not posted an execution plan or your index structure as requested by the other posters.[/quote]Agreed, although I can't think of how that wouldn't cause a table scan, thanks to the leading %. That kind of forces a table scan (or a clustered index scan), since there's no decent way to use an index to seek those out.[/quote]I also agree, but we have to go with what the OP provided. There is one thing, and unfortunately I don't have time to try and find it, I thought I had read in "Inside Microsoft SQL Server 2005 T-SQL Querying" that SQL Server 2005 had some improvements in its statitistics that improved query performance with leading wildcard characters in the LIKE clause. When I have some free time (what ever that is) I will see if I can find it again.:cool:Wed, 11 Jun 2008 15:08:14 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]John Rowan (6/11/2008)[/b][hr]Also, your WHERE clause of PO.PODescription LIKE '%00000%' will normally produce a table scan. It's a bit hard to say for sure though as you have not posted an execution plan or your index structure as requested by the other posters.[/quote]Agreed, although I can't think of how that wouldn't cause a table scan, thanks to the leading %. That kind of forces a table scan (or a clustered index scan), since there's no decent way to use an index to seek those out.Wed, 11 Jun 2008 14:50:11 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxAlso, your WHERE clause of PO.PODescription LIKE '%00000%' will normally produce a table scan. It's a bit hard to say for sure though as you have not posted an execution plan or your index structure as requested by the other posters.Wed, 11 Jun 2008 14:23:31 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxI'd write it more like this:[code]DELETE FROM dbo.tbItemFROM dbo.tbItem I INNER JOIN dbo.tbProductionOrder PO ON (I.POID = PO.POID)WHERE PO.PODescription LIKE '%00000%'[/code]You could also encolse this is a while loop and delete records in smaller batches.[code]declare @recordstodelete int, @recordsdeleted int;set @recordstodelete = 5000;while (@recordsdeleted is null) or (@recordsdeleted <> 0)begin -- while DELETE TOP (@recordstodelete) FROM dbo.tbItem FROM dbo.tbItem I INNER JOIN dbo.tbProductionOrder PO ON (I.POID = PO.POID) WHERE PO.PODescription LIKE '%00000%' set @recordsdeleted = @@rowcount -- BACKUP LOG ... -- a transaction log backup could be coded here to manage t-log sizeend -- while[/code]:cool:Wed, 11 Jun 2008 13:42:18 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxIt would be helpful if you mention the index structure on both the tables involved in delete operation.ManuWed, 11 Jun 2008 13:23:50 GMTMANU-J.http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxThe 11 Million is a standard number so it does not varyI also run DBCC DBREINDEX on the table earlierSo question of defragmentation should not come upWed, 11 Jun 2008 07:31:18 GMTibidapo22http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxFair enough, it was just that I seem to remember some reference to (nolock) dealing with potentially non-commited data and the obvious impacts of that might not be that desirable.Seems like we are both asking for more detail/history from the originator.Delete of 11 million rows doesn't sound like a daily task to me, but I might be wrong!!Wed, 11 Jun 2008 05:18:16 GMTUDBNThttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspx[quote][b]UDBNT (6/11/2008)[/b][hr]Not sure on the validity of (nolock) on a table where you are deleting data?[/quote]The hint will be ignored. Deletes have to lock exclusivly, lock hints or no lock hints.Wed, 11 Jun 2008 05:06:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxIndex definitions?Has the total number of rows in the table changed since this worked properly?Are your indexes fragmented? Are the statistics out of date?I'm not asking just to be a pain. I'm asking because I want as clear a picture of the problem as possible.Wed, 11 Jun 2008 05:00:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxDELETE IFROM tbItem I (nolock) INNER JOIN tbProductionOrder PO (nolock) ON I.POID = PO.POID WHERE PODescription LIKE '%00000%'This will delete about 11000000 recordsOnce again this has been successfully severallyWed, 11 Jun 2008 03:15:16 GMTibidapo22http://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxNot sure on the validity of (nolock) on a table where you are deleting data?That aside, you mentioned this has worked in the past, but not so well now.What you don't state is other contributing factors like* Volume of data involve on both the related tables* Is there an RI link and has that any supportive indexing to help the query.* What sort of number of deletes are you expecting, ie When originally run a few rows to be deleted would not have the same impact as say several thousand.As with a lot of the forum items on this site (I know because I have asked questions as well), it works a lot better if you can give as much relevant details on the issue as possible.Cheers.Wed, 11 Jun 2008 03:06:39 GMTUDBNThttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxhow many rows is that delete likely to affect? What indexes do you have on the 2 tables?Wed, 11 Jun 2008 02:56:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic515014-146-1.aspxI wrote query to delete some records in one of the table in my database. I set up job to run this query. The query is as below:DELETE IFROM tbItem I (nolock) INNER JOIN tbProductionOrder PO (nolock) ON I.POID = PO.POID WHERE PODescription LIKE '%00000%'This job has ran successfully in the past, but now it start hanging my system to extent that they cannot work in the factory, what are the possible causes?Wed, 11 Jun 2008 02:50:54 GMTibidapo22