March 8, 2017 at 10:02 pm
Hi,
I noticed that for some critical production databases sized around 600 GB or so, Indexes get fragmented often.
Do fragmented indexes cause deadlocks? As we are seeing deadlock erros in the App log and once I defrag indexes, the errors seem to vanish for few days and then they come back.
I created manual scripts as we cannot have the table locked while indexes are rebuilt, and we cannot use online index rebuilds for all columns as most of them are BLOB ( varchar (max) and var binary (max).
Thanks,
March 25, 2017 at 8:14 pm
I've found that fragmentation has nothing to do with deadlocks. Neither does the optimizer even consider fragmentation when determining what the execution plan should look like.
More likely, it's suffering from a combination of the bad form of parameter sniffing and a touch of stats being out of date, both of which are solved by an index rebuild because it causes the index stats to be rebuilt and that causes a recompile.
My recommendation would be to simply update stats more often.
As a bit of a sidebar, I've not done any index maintenance on any of the databases on my big production box since the 17 of January 2016 (more than a year ago) and performance has only gotten better. The key is to keep the stats updated especially on large tables that suffer a lot of inserts, updates, or deletes. Yeah..,. a little space is "wasted" thanks to the "natural fill factors" formed by the original page splits but those splits are mostly filled over time.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply