Solved

MS SQL page split per second is high

Posted on 2016-09-28
19
71 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 33

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 14

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 46

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 33

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 33

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 14

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 46

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 46

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 14

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

932 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

20 Experts available now in Live!

Get 1:1 Help Now