Friday, April 15, 2011

How to find Page Splits by Table/Index

This script provided information on which Table/Index was performing the page split.  I'm surprised there's apparrantly no DMV for this information.  Sorry, I do not have a reference from where I originally got this.

select AllocUnitName, count([AllocUnitName]) [Splits]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT' and parsename(AllocUnitName,3) <> 'sys'
group by AllocUnitName

Monday, April 11, 2011

How to View Index Fragmentation

The scripts below (provided by Cory Brown of Microsoft) were used to determine how badly the indexes were fragmented:

-- ==========================================================================
-- Description: The purpose of this script is to look at the fragmentation of

--              existing indexes in the database
--              and determine if the index should be REBUILT, REORGANIZED, or

--              statistics updated.
-- Date:        September 22, 2010
-- Author:      Cory Brown
-- ===========================================================================

--Rebuild Index recommended if fragmentation is above 30%
SELECT    DB_NAME(database_id),*
FROM      sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE     avg_fragmentation_in_percent > 30
AND       page_count > 1000
ORDER BY  avg_fragmentation_in_percent DESC

--Reorganize Index recommended if fragmentation is between 15% and 30%
SELECT    DB_NAME(database_id),*
FROM      sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE     avg_fragmentation_in_percent < 30
AND       avg_fragmentation_in_percent > 15
AND       page_count > 1000
ORDER BY  avg_fragmentation_in_percent DESC

--UpdateStats recommended if fragmentation is below 15%
SELECT    DB_NAME(database_id),*
FROM      sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE     avg_fragmentation_in_percent < 15
AND       page_count > 1000
ORDER BY  avg_fragmentation_in_percent DESC
 

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.