?
Solved

MS SQL page split per second is high

Posted on 2016-09-28
19
Medium Priority
?
318 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 14

Assisted Solution

by:Máté Farkas
Máté Farkas earned 332 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 36

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 16

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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 332 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 36

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 36

Assisted Solution

by:ste5an
ste5an earned 332 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
 
LVL 16

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 53

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 53

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 16

Accepted Solution

by:
Megan Brooks earned 1004 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 53

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

807 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