Sunday, April 10, 2011

Performance issues on SQL 2005: Deadlocks, Index Fragmentation

Encountered performance issues on an SQL 2005 x64 server hosting the databases for OCS Server 2007.  Using perfmon, I quickly saw that memory, CPU, network, and IO resources were not constrained.  Then looked at waits, deadlocks, and excessive block times.  Deadlocks are an issue, and long blocking times experienced for most client connections on the OCS databases (rtc, rtcdyn).

Looking at index fragmentation, I saw problems (>80% fragmentation) for several of the larger tables.  Perfomed defrag on those tables which provided immediate relief.   Relief was short-lived as indexes were quickly fragmented again - within 10minutes.   Next looked at page splits to see if that was causing fragmentaiton.  At an average of 5 page splits per second, it looked likely.  I wanted to see which indexes were experiencing page splits.   There was no obvious DMV or other method to see this information.

No comments:

Post a Comment