May 22, 2017 at 11:56 am
Hi,
We have a 3rd party monitoring tool from Idera and it sends me an alert that deadlocks are occurring on a database.
Once I look up and rebuild the indexes, though they are not that fragmented ( frag around 25%) , I see the alerts go away.
Do fragmented indexes also cause deadlocks?
Thanks in advance
May 22, 2017 at 12:47 pm
sqlguy80 - Monday, May 22, 2017 11:56 AMHi,We have a 3rd party monitoring tool from Idera and it sends me an alert that deadlocks are occurring on a database.
Once I look up and rebuild the indexes, though they are not that fragmented ( frag around 25%) , I see the alerts go away.
Do fragmented indexes also cause deadlocks?
Thanks in advance
25% fragmentation can be nothing or massive, depending on how much data there is in the table. Anything that slows down a query has the potential to allow for deadlocks.
One of the best DL troubleshooting (and explaining) articles I've ever seen:
https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
You'll need to capture the DL info in order to determine what type you have and how best to resolve it.
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
May 22, 2017 at 12:50 pm
sqlguy80 - Monday, May 22, 2017 11:56 AMDo fragmented indexes also cause deadlocks?
No.
Fragmentation affects large range scans from disk. Emphasis large and disk.
What likely happened is that the rebuild updated statistics, and the more accurate statistics let the optimiser find a better plan that was faster and hence reduced the likelyhood of a deadlock occuring.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2017 at 1:41 am
sqlguy80 - Monday, May 22, 2017 11:56 AMHi,We have a 3rd party monitoring tool from Idera and it sends me an alert that deadlocks are occurring on a database.
Once I look up and rebuild the indexes, though they are not that fragmented ( frag around 25%) , I see the alerts go away.
Do fragmented indexes also cause deadlocks?
Thanks in advance
In addition to what Gail mentioned
You sure the same transactions happened after rebuild ?
Did you check the queries involved in Deadlock?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply