Bobcares

WeSupport

Call Us! 1-800-383-5193
Call Us! 1-800-383-5193
Call Us! 1-800-383-5193

Need Help?

Emergency Response Time custom

Our experts have had an average response time of 11.06 minutes in March 2021 to fix urgent issues.

We will keep your servers stable, secure and fast at all times for one fixed price.

How to avoid Page Splits in SQL Server

by | Dec 4, 2020

Wondering how to avoid page splits in SQL server? Take a peek at this blog.

Here at Bobcares, we have seen several such SQL related queries as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at how to avoid page splits in SQL server.

 

Know more about Page splits

Page split is a resource-intensive operation. It mainly causes fragmentation that leads to poor performance in terms of increased I/O operations.

While updating an existing row with the data that is bigger in size to save on their data page then Page Split operation occurs to make space for this new update.

As a result, the data from the existing data page first move to a new page that adds up during the Page Split operation and make room to accommodate new records.

Also, this only happens when data changes in the database.

 

How we check Page Splits in SQL Server

Generally, page split occurrences are logged in the transaction log file as LOP_DELETE_SPLIT operation. We use the fn_dblog function to read the SQL Server transaction log files. We run the below T-SQL code to see all page splits occurrence using the transaction log file.

SELECT *
FROM
fn_dblog (NULL, NULL)
WHERE
[Operation] = N’LOP_DELETE_SPLIT’

In the below image we can see the LOP_DELETE_SPLIT logs under the operations column. This is a page split operation.

page splits in sql server

 

How we Reduce Page Splits Occurrences in SQL Server

Here are some of the solutions our Support Engineers follow to reduce the page split.

1. Increasing the fill factor on indexes

If we decrease the fill factor in the indexes then it increases the amount of empty space on each data page. The more empty space there is, the fewer page splits we will experience.

In another way, having too much unnecessary empty space can also hurt performance. Because if less data is stored per page, then it takes more disk I/O to read tables, and fewer data can be stored in the buffer cache. So we experiment to find the ideal fill factor.

2. Rebuilding the indexes more often.

Over time, any empty space created with the fill factor is used up, and more and more page splits will occur. Moreover, each time we rebuild an index, the fill factor’s empty space renews. It means that there is more room for data and fewer page splits will occur.

3. Add clustered indexes to monotonically increasing primary keys.

By doing this, we eliminate INSERT-caused page splitting entirely. Because all new records will be added to the end of the table.

4. Get a faster I/O subsystem.

Lastly, we prefer faster hardware. Because the faster hardware, the less of an issue are page splits.

[Need any assistance with SQL queries? – We’ll help you]

 

Conclusion

In short, bad page splits not only can cause performance issues during the actual page split but also upon subsequent queries on that index before it is rebuilt. Today, we saw how our Support Engineers avoid page splits.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF