UrbanPro
true

Learn MS SQL Development from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

How To Minimize The Page Splits In Sqlserver To Improve The Performane Of Database?

Amitava Majumder
16/06/2017 0 0

How to minimize the page splits in sqlserver to improve the performane of database?

Page Splits:

A page is 8Kbytes of data which can be index related, data related, large object binary (lob’s) etc...

When you insert rows into a table they go on a page, into ‘slots’, your row will have a row length and you can get only so many rows on the 8Kbyte page. What happens when that row’s length increases because you entered a bigger product name in your varchar column for instance,well,SQL Server needs to move the other rows along in order to make room for your modification, if the combined new length of all the rows on the page will no longer fit on that page then SQL Server grabs a new page and moves rows to the right or left of your modification onto it – that is called a ‘page split’.

Page splits arise when records from one memory page are moved to another page during changes to your table. Suppose a new record (Martin) being inserted, in sequence, between Adam and Rony. Since there’s no room in this memory page, some records will need to shift around. The page split occurs when Irene’s record moves to the second page.

This creates page fragmentation and is very bad for performance and is also reported as page split.

Page splits are considered very bad for performance, and there are a number of techniques to reduce, or even eliminate, the risk of page splits.

 Example code for tracking Page Splits :

We can find the bad page splits using the event sql_server.transaction_log. This event monitors all the activities in the transaction log, because that we need to use with caution. We can filter the ‘operation’ field looking for the value 11, which means LOP_DELETE_SPLIT. This is the deletion of rows that happens when SQL Server is moving rows from one page to another in a page split, a bad page split.

Extended Events for SQL Server provides a generic tracing and troubleshooting framework which allows deeper and more granular level control of tracing which was not possible using earlier methods like DBCC, SQL Trace, Profiler, etc... These earlier methods still exist and Extended Events is not a replacement.

 For this We need to create the session by t-sql. The code to create the session will be this:

 IF EXISTS (SELECT 1

            FROM sys.server_event_sessions

            WHERE name = 'PageSplits_Tracker')

    DROP EVENT SESSION [PageSplits_Tracker] ON SERVER

 CREATE EVENT SESSION PageSplits_Tracker

ON    SERVER

ADD EVENT sqlserver.transaction_log(

    WHERE operation = 11  -- LOP_DELETE_SPLIT

)

--Description for transaction_log event is: “Occurs when a record is added to the SQL Server transaction log.

--This is a very high volume event that will affect the performance of the server. Therefore, you should use

--appropriate filtering to reduce the number of events, and only use this event for targeted troubleshooting

--during a short time period.”

 -- LOP_DELETE_SPLIT : A page split has occurred. Rows have moved physically.

ADD TARGET package0.histogram(

    SET filtering_event_name = 'sqlserver.transaction_log',

        source_type = 0,source = 'database_id');

GO

--package0.histogram : You can use the histogram target to troubleshoot performance issues.      

--filtering_event_name : Any event present in the Extended Events session.

--source_type : The type of object that the bucket is based on.

--0 for an event

--1 for an action

--source : The event column or action name that is used as the data source.

-- Start the Event Session

ALTER EVENT SESSION PageSplits_Tracker

ON SERVER

STATE=START;

GO

-- Create the database

CREATE DATABASE Performance_Tracker

GO

USE [Performance_Tracker]

GO

-- Create a bad splitting clustered index table

CREATE TABLE PageSplits

( ROWID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,

  Data INT NOT NULL DEFAULT (RAND()*1000),

  Change_Date DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP);

GO

--  This index should mid-split based on the DEFAULT column value

CREATE INDEX IX_PageSplitsPk_Data ON PageSplits (Data);

GO

--  This index should end-split based on the DEFAULT column value

CREATE INDEX IX_PageSplitsPk_ChangeDate ON PageSplits (Change_Date);

GO

-- Create a table with an increasing clustered index

CREATE TABLE PageSplits_Index

( ROWID INT IDENTITY NOT NULL PRIMARY KEY,

Data INT NOT NULL DEFAULT (RAND()*1000),

Change_Date DATETIME2 NOT NULL DEFAULT DATEADD(mi, RAND()*-1000, CURRENT_TIMESTAMP))

GO

-- This index should mid-split based on the DEFAULT column value

CREATE INDEX IX_PageSplits_Index_ChangeDate ON PageSplits_Index (Change_Date);

GO

-- Insert the default values repeatedly into the tables

WHILE 1=1

BEGIN

    INSERT INTO PageSplits DEFAULT VALUES;

    INSERT INTO PageSplits_Index DEFAULT VALUES;

    WAITFOR DELAY '00:00:00.005';

END

GO

--If we startup this workload and allow it to run for a couple of minutes, we can then query the histogram target

--for our session to find the database that has the mid-page splits occurring.

-- Query the target data to identify the worst splitting database_id

with cte as

(

SELECT

    n.value('(value)[1]', 'int') AS database_id,

    DB_NAME(n.value('(value)[1]', 'int')) AS database_name,

    n.value('(@count)[1]', 'bigint') AS split_count

FROM

(SELECT CAST(target_data as XML) target_data

 FROM sys.dm_xe_sessions AS s

 JOIN sys.dm_xe_session_targets t

     ON s.address = t.event_session_address

 WHERE s.name = 'PageSplits_Tracker'

  AND t.target_name = 'histogram' ) as tab

CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)

)

select * from cte

database_id

database_name

split_count

16

Performance_Tracker

123

--With the database_id of the worst splitting database, we can then change our event session configuration

--to only look at this database, and then change our histogram target configuration to bucket on the alloc_unit_id

--so that we can then track down the worst splitting indexes in the database experiencing the worst mid-page splits

-- Drop the Event Session so we can recreate it

-- to focus on the highest splitting database

DROP EVENT SESSION [PageSplits_Tracker]

ON SERVER

-- Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server

CREATE EVENT SESSION [PageSplits_Tracker]

ON    SERVER

ADD EVENT sqlserver.transaction_log(

    WHERE operation = 11  -- LOP_DELETE_SPLIT

      AND database_id = 16 -- CHANGE THIS BASED ON TOP SPLITTING DATABASE!

)

ADD TARGET package0.histogram(

    SET filtering_event_name = 'sqlserver.transaction_log',

        source_type = 0, -- Event Column

        source = 'alloc_unit_id');

GO

-- Start the Event Session Again

ALTER EVENT SESSION [PageSplits_Tracker]

ON SERVER

STATE=START;

GO

--With the new event session definition, we can now rerun our problematic workload for more than 10 minutes period

-- and look at the worst splitting indexes based on the alloc_unit_id’s that are in the histogram target:

WHILE 1=1

BEGIN

    INSERT INTO PageSplits DEFAULT VALUES;

    INSERT INTO PageSplits_Index DEFAULT VALUES;

    WAITFOR DELAY '00:00:00.005';

END

GO

-- Query Target Data to get the top splitting objects in the database:

SELECT

    o.name AS table_name,

    i.name AS index_name,

    tab.split_count,indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent,

    i.fill_factor

FROM (    SELECT

            n.value('(value)[1]', 'bigint') AS alloc_unit_id,

            n.value('(@count)[1]', 'bigint') AS split_count

        FROM

        (SELECT CAST(target_data as XML) target_data

         FROM sys.dm_xe_sessions AS s

         JOIN sys.dm_xe_session_targets t

             ON s.address = t.event_session_address

         WHERE s.name = 'PageSplits_Tracker'

          AND t.target_name = 'histogram' ) as tab

        CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)

) AS tab

JOIN sys.allocation_units AS au

    ON tab.alloc_unit_id = au.allocation_unit_id

JOIN sys.partitions AS p

    ON au.container_id = p.partition_id

JOIN sys.indexes AS i

    ON p.object_id = i.object_id

        AND p.index_id = i.index_id

JOIN sys.objects AS o

    ON p.object_id = o.object_id

JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

ON i.object_id = indexstats.object_id

AND i.index_id = indexstats.index_id

WHERE o.is_ms_shipped = 0

ORDER BY indexstats.avg_fragmentation_in_percent DESC

table_name

index_name

split_count

IndexType

avg_fragmentation_in_percent

fill_factor

PageSplits_Index

IX_PageSplits_Index_ChangeDate

286

NONCLUSTERED INDEX

99.57894737

0

PageSplits

PK__PageSpli__97BD02EBEA21A6BC

566

CLUSTERED INDEX

99.37238494

0

PageSplits

IX_PageSplitsPk_Data

341

NONCLUSTERED INDEX

98.98989899

0

PageSplits

IX_PageSplitsPk_ChangeDate

3

NONCLUSTERED INDEX

1.747572816

0

--With this information we can now go back and change our FillFactor specifications and retest/monitor the impact

-- to determine whether we’ve had the appropriate reduction in mid-page splits to accommodate the time between

-- our index rebuild operations:

-- Change FillFactor based on split occurences to minimize page splits

Using Fill Factor we can minimize the page splits :

Fill Factor :When an index is created with a fill factor percentage, this leaves a percentage of the index pages free after the index is created, rebuilt or reorganized. This free space is used to hold additional pages as page splits occur, reducing the change of a page split in the data page causing a page split in the index structure as well, but even with your Fill Factor set to 10% to 20%, index pages eventually fill up and are split the same way that a data page is split.

 A page is the basic unit of data storage in SQL server. Its size is 8KB(8192 bytes). Data is stored in the leaf-level pages of Index.  The percentage of space to be filled with data in a leaf level page is decided by fill factor. The remaining space left is used for future growth of data in the page. Fill factor is a number from 1 to 100. Its default value is 0, which is same as 100. So when we say fill factor is 70 means, 70% of space is filled with data and remaining 30% is vacant for future use. So higher the fill factor, more data is stored in the page. Fill factor setting is applied when we create/rebuild index.

ALTER INDEX PK__PageSpli__97BD02EBEA21A6BC ON PageSplits REBUILD WITH (FILLFACTOR=70)

ALTER INDEX IX_PageSplitsPk_Data ON PageSplits REBUILD WITH (FILLFACTOR=70)

ALTER INDEX IX_PageSplits_Index_ChangeDate ON PageSplits_Index REBUILD WITH (FILLFACTOR=80)

GO

-- Stop the Event Session to clear the target

ALTER EVENT SESSION [PageSplits_Tracker]

ON SERVER

STATE=STOP;

GO

-- Start the Event Session Again

ALTER EVENT SESSION [PageSplits_Tracker]

ON SERVER

STATE=START;                           

GO

--Do the workload once again

WHILE 1=1

BEGIN

    INSERT INTO PageSplits DEFAULT VALUES;

    INSERT INTO PageSplits_Index DEFAULT VALUES;

    WAITFOR DELAY '00:00:00.005';

END

GO

--With the reset performed we can again start up our workload generation and

--begin monitoring the effect of the FillFactor specifications on the indexes with our code.

--After another 2 minute period, the following splits were noted.

--Once again Query Target Data to get the top splitting objects in the database:

--At present there is no page splits are found in indexes IX_PageSplitsPk_ChangeDate, PK__PageSpli__97BD02EBEA21A6BC,   IX_PageSplitsPk_Data

0 Dislike
Follow 1

Please Enter a comment

Submit

Other Lessons for You

SQL
Structured query language-It is a language to interact with the database. Database-It is a collection of data's in the form of tables. Tables-Collection of rows and columns Rows are also called as tuples...


What is a Dashboard?
Introduction There are many different ideas of what a dashboard is. This article will clearly define it along with other presentation tools. In article, What is BI? - A Business Intelligence Primer, it...

Defect Management
Defect Management Terms: Application Life Cycle (ALM) Development Phase Testing Phase Production Phase ------------------------------------------------------------------ Error ...

What's the best way to learn SQL Server?
Just read the book, type and run the code, and work the chapter examples. If you work the book, you will be ahead of many SQL Server programmers. You’ll understand declarative set-based interaction...

Looking for MS SQL Development Training?

Learn from Best Tutors on UrbanPro.

Are you a Tutor or Training Institute?

Join UrbanPro Today to find students near you

table_name

index_name

split_count

IndexType

Please enter your full name.

Please enter institute name.

Please enter your email address.

Please enter a valid phone number.

Please enter a pincode or area name.

Please enter category.

Please select your gender.

Please enter either mobile no. or email.

Please enter OTP

Please enter Password

By signing up, you agree to our Terms of Use and Privacy Policy.

Already a member?

X

Looking for MS SQL Development Classes?

The best tutors for MS SQL Development Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn MS SQL Development with the Best Tutors

The best Tutors for MS SQL Development Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more