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 |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2008-03-16 : 10:54:27
|
| Hi, hope someone can help me out here. A simple delete statement is maxing out the CPU, not sure why. The table has a primary clustered index, 5 other indexes of 2 non-alpha fields each. There are 4 varchar fields of ~200 chars each (none are indexed). There are no text, binary, image or guid fields. Table contains ~20k records.It has a many-to-one r/ship to a parent table. The foreign key is defined in the schema, and "ParentRecordID" has an index.The delete statement is of the kind "DELETE FROM <table> where ParentRecordId IN(SELECT RecordID FROM <parent_table> WHERE <criteria>)".It will delete about 100 records, and is being performed from within a .NET windows application. When the statement is run, CPU hits 100%. Several other DELETE statements, some of similar kind, run before and after it, and work fine.Would appreciate any insights as to why CPU is maxing out when deleting from this one table.. what could it be? It finishes eventually, but takes about 5 minutes to delete the 100 records. Thanks! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-03-16 : 11:27:57
|
| Check for locked or blocked processes.e4 d5 xd5 Nf6 |
 |
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2008-03-16 : 11:29:41
|
| It's the only process accessing the db at the time. It's running on my local machine, and I've stopped IIS just in case that was it, but no luck. |
|
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2008-03-16 : 11:48:37
|
| Thanks, I looked into the r/ships and spotted the culprit.One r/ship makes it the parent for a large table, containing ~400k records. Even though I delete all dependent records prior to deleting these, it's still checking the 400k long table for r/ship violations, hence the smoke pouring out of my laptop. When I delete the r/ship, things speed up mightily.So the question is, should I delete that constraint before deleting these records, then restore it again afterwards, or is there another way around this? Or are such burdensome relationships not recommended in the first place? (Sounds like I'm writing to "Ask Aunt Amy") |
|
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2008-03-16 : 11:58:57
|
| My bad. The relationship in question simply didn't have a proper index set up for it. The child table (400k long one) was lacking an index with the parent's recordid as the first field. Works great now. :) |
|
|
|
|
|
|
|
|
Deleting ~100 records is taking 5 minutes.