Solved

MS SQL page split per second is high

Posted on 2016-09-28
19
65 Views
Last Modified: 2016-10-26
hi all,

right now we can see from monitoring tools that our MS SQL page split per second is high, 1271.62, I forget how to duel with it already, please suggsest.

build index with lower fill factor?

data growth is too fast, too much index so page split high as well ?
0
Comment
Question by:marrowyung
  • 8
  • 4
  • 3
  • +2
19 Comments
 
LVL 11

Assisted Solution

by:Máté Farkas
Máté Farkas earned 83 total points
ID: 41821228
The following things can help:
  • Creating/Rebuilding indexes with lower FILLFACTOR (50%)
  • Create clustered indexes to represent the physical order of the rows they are created
  • Do this 2 things index by index and during continously monitor the page splits/sec counter
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41821239
"Creating/Rebuilding indexes with lower FILLFACTOR (50%)"

with fillfactor = 50 parameter?

"Create clustered indexes to represent the physical order of the rows they are created"

don't know why it is related, fill factor is the number of bytes filled per SQL data page, right?
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41821296
It depends on the structure of data gathering process which does the inserts and the physical implementation of your table (the row size and used data types, number and kind of indices).

Normally in an high transaction volume table like yours, you should have a clustered index using an highly selective natural key. Depending on that key a fill factor of 0.8 or 0.9 is often sufficient.

And depending on amount of overall data: proper partitioning.
1
 
LVL 1

Author Comment

by:marrowyung
ID: 41821362
"Normally in an high transaction volume table like yours, you should have a clustered index using an highly selective natural key. Depending on that key a fill factor of 0.8 or 0.9 is often sufficient."

so you means this bring the high page split ? very good business then ...
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41821373
Is this your own application where you can directly address design issues, or a 3rd party app where you have to live with what they provide?
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 83 total points
ID: 41821445
Can you share with us the table schema?
Also high page splitting means a table is being heavily updated (insert and updates statements). What's the reason for that? Can you do something about the actual process to reduce the page splits?
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41821543
Page splits happen when data needs to be inserted between existing pages.

Thus using a natural key as clustered index can avoid this, when this natural key leads to new records appended at the end of the table.

E.g.
( ID UNIQUEIDENTIFIER, Payload INT, CreatedDateTime DATE ) with a primary clustered key in ID where ID is prefilled with NEWID() and CreatedDateTime with GETDATE() as default.

This will lead to random new GUIDs. Thus appended rows will be inserted all over existing pages.

Solution is simple: make the primary key non-clustered and create a clustered index over CreatedDateTime. Then new records are always added at the end.

Caveat: You need to test such things carefully on a separate system. Causes it can have severe impact on other queries.
1
 
LVL 1

Author Comment

by:marrowyung
ID: 41826071
Megan Brooks,

"Is this your own application where you can directly address design issues, or a 3rd party app where you have to live with what they provide?"

our own application, but can't directly address design issue as no one interested to tell me.... :):)

Victor,

"Can you share with us the table schema? "

I just read from tools that it detect so many page splits but it don't say which table.

and I'd like to see other than rebuild index with lower fill factor, what else can be done.

"Also high page splitting means a table is being heavily updated (insert and updates statements). "

yes, as the page can't hold record and need to split to 2 x or more data page to store data.

ste5an,

"olution is simple: make the primary key non-clustered and create a clustered index over CreatedDateTime. Then new records are always added at the end."

schema redesign then...
0
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 83 total points
ID: 41826170
Not really. Redesign is imho a modelling task done by a developer. But changing clustered index is an implementation detail and it's done by the DBA.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41826744
Megan Brooks,

"Is this your own application where you can directly address design issues, or a 3rd party app where you have to live with what they provide?"

our own application, but can't directly address design issue as no one interested to tell me.... :):)


It sounds familiar. :-)

As a database developer, I would rather figure out what is going on and fix it, but a company won't necessarily want to invest the resources to do that. Sometimes they would rather spend money on processors, memory, and storage, at least until the system finally fails for one reason or another or the costs become unbearable. With in-house applications, it is possible that none of the developers understands SQL Server well.

I am reluctant to make recommendations without knowing something about the code. Some fixes can be simple, but when there is heavy page splitting, that may not be the case. Fill factor adjustments can help in situations where inserts are unavoidable, but they can also create more problems than they solve if not done right.

If the application is appending batches of data to the end of an index and then updating those batches in subsequent steps, causing splits, fill factor won't stop that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41829629
"it is possible that none of the developers understands SQL Server well."

very possible here, culture here is, once they fix sth, they push the rest of TUNING task to DBA,

in ALL company I work with, developer fix their own, this make a lot of sense !

"but they can also create more problems than they solve if not done right. "

e.g. ?

"If the application is appending batches of data to the end of an index"

at the end of the B-tress index ? you are referring to a lot of insert anyway, right ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41829692
very possible here, culture here is, once they fix sth, they push the rest of TUNING task to DBA,
Well, that's why the DBA role exists. Otherwise we will be jobless ;)

in ALL company I work with, developer fix their own, this make a lot of sense !
There's also a role called "Developer DBA" that is different from "Production DBA". When you have one Developer DBA things turns to be more easy to deal with.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831457
"Well, that's why the DBA role exists. Otherwise we will be jobless ;)"

AHAHA, not true all the time. Here, a lot of large company here need developers to fix their own shit before SUBMIT to someone else and keep asking!

but the problem always is, student only have time to be coach on how to write thing but not tune code.

in real world, if developer don't fix their shit, most of the case is they get fired...

" When you have one Developer DBA things turns to be more easy to deal with.

we call it SQL developer ... but nowadays, more and more case to combine SQL developer and DBA together, finally still called dBA.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831501
SQL Developer for me is a person with strong T-SQL knowledge plus expert in creating SSIS packages and SSRS reports.
Developer DBA for me is a person that design the data model and responsible for the database security policies and also performance and tuning.
Production DBA for me is a person that manage all database infrastructure and monitor and solve infrastructure issues that may happen. Can also work with Developer DBA if he sees that something with the database model or performance is not ok.

And then in an higher level you can also have DB Engineer and DB Architect. In most cases these two roles are performed by the same person.

Of course in smaller companies it's only a person that's having all these roles.
0
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 251 total points
ID: 41832500
RE: "but they can also create more problems than they solve if not done right. " (with reference to fill factor adjustments)

There are many posts about the issues with FILL FACTOR. Here are a few.

5 Things About Fillfactor
One of those things I hate finding is bad fillfactor settings. Fillfactor can be a useful tool to help performance, but it’s often a performance killer if you use it incorrectly.

How is Fill Factor Impacting My Indexes?
Setting the fill factor to anything other than the default decreases the rows per page for that index, thereby increasing the number of pages that must be read. According to Books Online, the read performance penalty is twice the chosen fill factor. This means that setting the fill factor to 50% will lead to twice as many reads to get the same data. Even a more reasonable number like 90% would have a 20% performance penalty on all reads.

The FILL FACTOR
From the above results, it is clear that a high FF will improve SELECT but will penalize DML due to more page splits, while a low FF will improve DML statements but will penalize SELECT statements.

That's just a few examples. You can find many more, here in EE and on the Web.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41833380
Vitor Montalvão,

"Production DBA for me is a person that manage all database infrastructure and monitor and solve infrastructure issues that may happen. Can also work with Developer DBA if he sees that something with the database model or performance is not ok."

this is me.... AHA

this kind of person is lack here.

"Developer DBA for me is a person that design the data model and responsible for the database "

web developer/ application developer own this here.

very little SQL developer here except they are BI developer.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41853559
marrowyung, is your question answered?
If so please close it otherwise tell us what you still need from us.
Cheers
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41861160
I am sorry that I am traveling and terribly jetlag, so I can't provide more input.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41861164
tks all
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 72
SQL Help - 12 42
Sql query to Stored Procedure 6 21
Table create permissions on SQL Server 2005 9 15
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now