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 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
 Posted - 2011-03-23 : 14:23:38
|
| I have a system where we purge data nightly. The purge job is having some performance issues, so I've been looking into missing indexes and execution plans. I've fixed quite a bit so far, but I am perplexed how to fix one particular table.We are deleting from Table1, but there's a cascade delete on the foreign key to Table2. Table1 is the parent, Table2 is the child. Table2 has the right index for the relationship. The index is clustered and is a single column. But when I view the execution plan for the delete on Table1, it shows a different index is being used on Table2, and it's doing an index scan.Here's an example:Table1 - Column1, Column2, Column3 - Column1 is PK/identity and non-clusteredTable2 - Column1, Column8, Column9 - Column1 is PK and clustered, not an identity as it gets the info from Table2There's a foreign key between the two tables on Column1, and it's set to do cascade deletes.Now we do this:DELETE FROM Table1 WHERE Column3 < 'SomeDateTimeValue'Because of the cascade delete, it also deletes from Table2. But it's using the "wrong" index on Table2! It's using a non-clustered index that has Column1 in the INCLUDE list. I've updated statistics and freed the procedure cache in case it was just a bad plan, but no dice. Any clever way of forcing the "right" index to be used on Table2? An index hint isn't an option as Table2 isn't included in the delete query.I also tweeted this to #sqlhelp, so will post update here if I get an answer.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-23 : 14:39:50
|
| @mrdenny says to put a non-clustered index on Table2.Column1 with no include columns. I'm going to give that a try once the purge completes in about 2 hours. I am doing performance testing with various indexes to see what works best. The whole reason I started working on this is because of severe performance issues that the purge job is causing while it's running. Once it hits the delete on Table1, it causes a ton of blocking. I think I've fixed that by changing the clustered index on Table1, but the execution plan was still not ideal and was showing a problem with Table2. With @mrdenny's approach, I think the plan is going to be ideal and I can focus on other tables that need some index changes. Problem with my testing is that it takes about an hour to do the restore to get back to my baseline. And then to fix the clustered index on Table1, it takes about 4 hours. The database is only 150GB in size, and Table1 is near 200 million rows (Table2 only has 20,000). But the server that I've got has an IO bottleneck, so things are not optimal. I may have to borrow our DR server which is a duplicate of production hardware, just not sure if I have enough disk space out there.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
|
|
Index on foreign key cascade delete