These days, investigating a fairly simple insert which was taking longer than what I would consider reasonable for that particular piece of code, I explored several possible causes. One thing that raised my attention was that the "Pages split/sec" perfmon counter was constantly above 0, so I began to wonder which index was causing most of it. I ended up reading an interesting blog entry from Greg Linwood which inspired me to write a small stored procedure to monitor it for a longer period:
USE master
IF OBJECT_ID('dbo.sp_dba_CheckPageSplits') IS NOT NULL
DROP PROC dbo.sp_dba_CheckPageSplits
GO
CREATE PROCEDURE dbo.sp_dba_CheckPageSplits (@MaxLSN varchar(255)= NULL OUTPUT)
AS
/*
Author: Leonardo Pasta
Date: 13/08/2007
Summary: List the objects that are suffering most from PAGE SPLITS.
This procedure is compatible with SQL 2000. To use it on SQL2005, replace "[Object Name], [Index Name]" with "[AllocUnitName]"
*/
SET NOCOUNT ON
IF @MaxLSN IS NULL
SET @MaxLSN = ''
RAISERROR('Verifying logs greater than %s',1,1,@MaxLSN) WITH NOWAIT
SELECT TOP 15 [Object Name], [Index Name], Context, count([Current LSN]) [Splits]
FROM ::fn_dblog(default,default)
WHERE Operation = N'LOP_DELETE_SPLIT'
AND [Current LSN] > @MaxLSN
GROUP BY [Object Name], [Index Name], Context
ORDER BY [Splits] DESC
SELECT @MaxLSN=MAX([Current LSN])
FROM ::fn_dblog(default,default)
GO
Then ran something like the infinite loop below to get a quick & dirt report of the worst offender indexes:
DECLARE @MaxLSN varchar(255)
WHILE 1=1
BEGIN
EXEC dbo.sp_dba_CheckPageSplits @MaxLSN OUTPUT
WAITFOR DELAY '00:00:30'
END
To be honest, I don't believe that my IO problems will be explained by page splits, but I think this will be a valuable tool to fine-tuning our indexes strategies in the long run.