August 26, 2020 at 6:25 pm
Hi All
One interesting scenario here. We have a partitioned table which is partitioned based on a date column. The clustered Primary key is a composite key with ID+ that Date column.
The table also has some 25 non clustered indexes. Now the value of partitioned key (i.e. the date column) might change. So that simply means that particular row will physically move to some other location as it is part of PK. This results in update of all 25 non clustered indexes as well because they all need to point to a new physical location now.
This makes our update queries slow and they starts breaching their SLA of 5 sec. Is there a way around it? Has anyone faced this type of situation? Appreciate any help....
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
August 26, 2020 at 9:53 pm
One solution may be to reduce the number of NCI's.
But, before looking at that, it may not hurt to grab an execution plan and see what is happening when you run that update. Generally, I do not remove indexes as someone created those for a reason. It might not be a good reason, or it may be that it turns a 20 minute process into a 20 second process.
Can you reproduce this on a test system? if so, then you can have more fun testing it without breaking production. My first step after reviewing the execution plan would be to rule out the non-clustered indexes. Disable all NCI's on that table and try the update that takes 5 seconds. Did this make it substantially faster or is it only a minor improvement?
If you can't reproduce it on test, check your execution plan on live. You should also check for blocking. It might be that it isn't the UPDATE that is slow, but multiple concurrent UPDATES are causing blocking on the row, page, or table so UPDATE 1 needs to finish before UPDATE 2 can start. And if you have a long list of these updates, that could be what is causing your bottlenecks.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 26, 2020 at 10:23 pm
How many rows are actually in this table? And, if you have that indexes, it could help if you posted the CREATE TABLE statement along with the CREATE INDEX statements. Also, please identify which column the partitioning is actually done on.
The real problem here, though (and as you're already aware of), is the fact that they're violating the primary rule for partitioned tables and that is having an immutable partitioning column. My recommendation would be that you find a better partitioning column even if you have to make one.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply