13 August 2007 21:36 leo.pasta

Page splits

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.

Filed under:

Comments

# re: Page splits

14 August 2007 09:35 by Colin Leversuch-Roberts

I've done some "non scientific" testing to see if and how I could slow down inserts, I was trying to prove that adding indexes could slow performance by a measurable amount. So far I've not really had much success, inserting several >8k text columns will slow things , however in the main my inserts to the table were occuring at one end ( like a heap except it wasn't ) the secondary indexes split because I set the data to do that. It's interesting to extract that information and I wish the actual counter had more granularity - as you've suggested it probably has little to do with the issue and I'd sort of agree.