Home
  Home
Home
Search
Articles
Page Tag-Cloud
  Software
Software Tag-Cloud
Submit Patch
Building from Source
Open Source Definition
  Popular Tags
C++
Source Code
Class
Legacy
Console
  Members
Login
Web-Email
Notable Members
  Official
Our Company
Copyright Information
Software EULA
GPL EULA
LGPL Eula
Pre-Release EULA
Privacy Policy
  Support
Make Contact
 
 
Page Splits per-Second Over Time
Page splits occur when data is expanded to a size which will no longer fit into the 8KB page. Page splits are a normal part of SQL Server's operation, but page splits are I/O and lock intensive - so too many page splits can be indicitave of a design flaw or misconfiguration. For this reason, taking a baseline of your SQL Server's page splits is important - especially before making any changes which could increase or decrease this measure.

DECLARE @BeforeCount Decimal(16, 2)
DECLARE @AfterCount Decimal(16, 2)
DECLARE @BeforeTime DateTime
DECLARE @AfterTime DateTime

SELECT @BeforeCount = cntr_value, @BeforeTime = GetDate()
FROM master.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access Methods%'

--Change this to as many hours:minutes:seconds you want to average out.
WAITFOR DELAY '00:10:00'

SELECT @AfterCount = cntr_value, @AfterTime = GetDate()
FROM master.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access Methods%'

PRINT 'Total splits: ' + Convert(VarChar(50), Convert(Decimal(18, 0), (@AfterCount - @BeforeCount)))
	+ ' over: ' + Convert(VarChar(50), datediff(s, @BeforeTime, @AfterTime)) + ' seconds = '
	+ Convert(VarChar(50), Convert(Decimal(16, 2),
	((@AfterCount - @BeforeCount) / datediff(s, @BeforeTime, @AfterTime)))) + '/s'


Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.



Tags:
 Baselining    Index    Pages    Performance    Snippet    SQL Server    Tuning  

Created by Josh Patterson on 2/9/2013, last modified by Josh Patterson on 2/17/2013

No comments currently exists for this page. Why don't you add one?
First Previous Next Last 

Login or signup to leave a comment.
 
Copyright © 2014 NetworkDLS.
All rights reserved.
 
Privacy Policy | Our Company | Contact