Page Splits \ sec

What is a "Bad" Number for Page Splits \sec.
I know ideally, that we would want Zero.
but when she I start being concerned ?
Thanks as usual
Greg Jackson
PDX, ORGreg
I can't find a figure anywhere for what is a bad number. However a number co
uld be missleading anyway. On a large database with lots of tables, you woul
d get a lot more page splits than on a small database with a few tables.
As it just reports how many page splits, they may be mostly on one table or
they could be spread evenly over your database. I think the number of page s
plits just on it's own is not very usefull.
Do you regularly reindex all or some of the tables on your database? If you
are worried about the effects that page splits are having on your database,
usee dbcc showcontig to see how fragmented they are. If some tebles become f
ragmented much quicker than
others ( all tables except static tables will become fragmented to a degree
over time), it might be worth increasing the fill factor. This will increase
the table size but will slow down the page splits and the fragmentation.
Hope this helps
John|||Yeah I totally am with you.
Here is the deal.
I am our Senior DB Architect\Engineer I am NOT the DBA and as such, I dont
have direct access to prod.
However, our prod DB is totally sucking wind. (Sprocs taking in excess of
100,000 ms)
WE NEVER EVER EVER defrag as our DBA insists that it is impossible without
downtime. I have pointed him to a million articles on INDEXDEFRAG, etc. He
says the articles are "Wrong". He SWEARS That he must turn off logging to
run indexdefrag cuz "The log fills up too fast". (NUTS)
ALL Of our tables are fragged bigger then crud. They ALL have 100% fill
factor in a highly transactional system (averaging 44 trnxns \sec).
Most tables are currently 87% fragged. Not sure why, but that seems to be
where fragmentation bottoms out.
I am writing scripts to drop indexes and PKEYS in some cases so we can
recreate them with fill factor settings (Anywhere from 70% to 100% depending
on the table).
Our DBA says I'm nuts and that fill factor will REDUCE performance.
He tells his boss that we have ZERO page splits in production.
I say..."How can that be possible ? Think about what you're saying" We have
100% fill factors, we have 3.8 million transactions per day, Our Database is
growing at a rate of 5GB per month, but we have ZERO Page splits ?
I run perf mon against the server and I am seeing page splits all over the
freaking place.
I need to send numbers to his boss and say "Look, this guy is not right" we
have to implement my scripts or we are going to continue to tank as data and
volume grows.
Here is data from some random samples I obtained.
Date
Average Page Splits\sec
1/27/2004
1.15282921
2/1/2004
0.606016587
2/5/2004
1.344524812
2/6/2004
1.589833663
2/20/2004
0.9
2/21/2004
0.59
2/22/2004
0.78
2/27/2004
1.82
so my question is....
based on these numbers, are these figures high enough to prove my point ?
1.5 page splits per second does not sound like a huge number to me. So It
appears to NOT prove what I "KNOW" to be happening in production.
wouldnt I expect to see thousands and thousands of splits per second if the
problem were as prolific as I know it to be ?
GAJ|||Those figures are well within acceptable limits and will not cause the type
of slowness you are indicating. While you DBA's claims in general are not
true fragmentation in an OLTP system may not be as much an issue as most
people think. If these are random I/O (not range queries) it takes as much
time and effort to read the row from the beginning of the file as the end
etc. There are certainly things the DBA can do to reduce fragmentation it
sounds like your schema / queries just need to be optimized. I can take the
best designed db on the best hardware available and write a poor query that
will bring it to it's knees. If you have a sp that is taking a long time you
should profile it and see what it is doing. The you can see where the
issues lay. Your dba should be doing this without anyone screaming at
him<g>.
Andrew J. Kelly SQL MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:%23A%23wrcDCEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Yeah I totally am with you.
> Here is the deal.
> I am our Senior DB Architect\Engineer I am NOT the DBA and as such, I dont
> have direct access to prod.
> However, our prod DB is totally sucking wind. (Sprocs taking in excess of
> 100,000 ms)
> WE NEVER EVER EVER defrag as our DBA insists that it is impossible without
> downtime. I have pointed him to a million articles on INDEXDEFRAG, etc. He
> says the articles are "Wrong". He SWEARS That he must turn off logging to
> run indexdefrag cuz "The log fills up too fast". (NUTS)
> ALL Of our tables are fragged bigger then crud. They ALL have 100% fill
> factor in a highly transactional system (averaging 44 trnxns \sec).
> Most tables are currently 87% fragged. Not sure why, but that seems to be
> where fragmentation bottoms out.
> I am writing scripts to drop indexes and PKEYS in some cases so we can
> recreate them with fill factor settings (Anywhere from 70% to 100%
depending
> on the table).
> Our DBA says I'm nuts and that fill factor will REDUCE performance.
> He tells his boss that we have ZERO page splits in production.
> I say..."How can that be possible ? Think about what you're saying" We
have
> 100% fill factors, we have 3.8 million transactions per day, Our Database
is
> growing at a rate of 5GB per month, but we have ZERO Page splits ?
> I run perf mon against the server and I am seeing page splits all over the
> freaking place.
> I need to send numbers to his boss and say "Look, this guy is not right"
we
> have to implement my scripts or we are going to continue to tank as data
and
> volume grows.
> Here is data from some random samples I obtained.
> Date
> Average Page Splits\sec
> 1/27/2004
> 1.15282921
> 2/1/2004
> 0.606016587
> 2/5/2004
> 1.344524812
> 2/6/2004
> 1.589833663
> 2/20/2004
> 0.9
> 2/21/2004
> 0.59
> 2/22/2004
> 0.78
> 2/27/2004
> 1.82
>
> so my question is....
> based on these numbers, are these figures high enough to prove my point ?
> 1.5 page splits per second does not sound like a huge number to me. So It
> appears to NOT prove what I "KNOW" to be happening in production.
> wouldnt I expect to see thousands and thousands of splits per second if
the
> problem were as prolific as I know it to be ?
>
> GAJ
>|||Hey Andrew,
I agree with you on one thing:
1. We have sprocs that definately suck rocks....I will be rewriting many of
them soon. But this is a very large task (some will take upwards of 2 weeks
to rewrite they are nasty)
BUT see below:
1. High Frag levels require SQL Server to read more pages than well
maintained DBs so IO is excessive for reads. Is that not a true statement
(Whether sequential or not) ?
2. Also establishing fill factor settings will reduce Page Splits, hence
reducing IO Load. Is that not a true statement ?
I definately am aware that the existince of HEAPS and huge frag levels are
not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy and
quick to fix).
The next topic I have to tackle is that MOST of our FKeys are not indexes.
According to our DBA, that is NOT a problem either Even when we look at the
Query plan and see massive Table Scans (we're talking tables with Millions
of records). Again, he says we have "No Table Scans In Production" and
"Indexing the Fkeys will slow us down and I will not allow them".
Here are the perfmon stats for Full Scans:
SQLServer:Access Methods\Full Scans/sec
(Average over the past few weeks = 21.38)
My plan to improve the performance:
1. Remove Heaps from production by ensuring clustered indexes (placed
wisely) on our Important tables
2. Establish Fill Factor Settings for WRITE Intensive tables
3. Perform periodic defrag maintenance
4. Add Indexes to FKeys that are used in Joins
5. Rewrite problem sprocs (remove temp tables, cursors, etc)
I know you've been around a while and I respect your opinion so light me up
!
Thanks in advance
GAJ|||Hi,
Here are just my 2 cents worth ;-)

> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
High Frag levels doesn't mean that SQL Server will read more pages. However,
when doing table scans SQL Server will be bouncing all over the disk to grab
the right pages and therefore resulting in more IO. Therefore, I consider
your statement to be partially true.

> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
Reducing the fill factor will indeed reduce page splits and therefore
increase write performance. However, because of the pages that are now
filled with air, SQL Server will need more logical IO when reading pages.
However, on my databases I've seen lots of examples where lower fill factors
increased the performance significantly without any effect on read
performance.
With regard to your plan, I would add clustered and non-clsutered indexes as
soon as possible. Therefore, making point 4 on your list priority 2.
Good luck with your DBA ;-)
HTH
Karl Gram
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>|||nicely said Karl.
I agree with your semantics.
#1, 2 and 3 are all being done in one swoop. so really they are all the same
step as far as script building and execution are concerned.
The FKey Index thing is definately killing us. Any argument to the contrary
is really ludicrous. We have about half a dozen very large sprocs that are
called thousands and thousands of times a day that access these tables.
You can see in the Query plan that they are doing scans. I fix this problem
and ALL the sprocs improve dramatically.
I'll keep swingin....
thanks
GAJ|||Jaxon,
Your suggested recommendation sounds wise to me. Just watch out for log size
and available database size when you defrag.
It seems that you are in a "fight" with the DBA, and this will end up in a
political or even prestige issue. Not something I envy, because you will
never win. If you are right (and I bet a beer that you are), the DBA will
not like you as you will make him/her look foolish/incompetent. And if you
are wrong, well... you are wrong. ;-).
A first step can be to implement your suggestions on a test database and see
how much it improves performance. How you then will let the DBA know about
your results, I don't know - you have to think about that for a while.
Ideally, the DBA would think that he/she came up with the solution (possibly
with some assistance from you).
Another note, btw. Having indexes on FK's will also help when you insert and
update the referenced table. SQL Server has to do an "internal" join to
verify the reference constraint.
If you know how SQL Server work, these things aren't really that difficult,
and there is no "magic" involved. Perhaps there's only a communication
problem with the DBA?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>|||yeah Tibor,
you are right.
He really "IS" a nice guy. Just politics.
He has been around for a long time and kinda walks on water around here.
Im the new guy on the block and definately am stirring up a bunch of crud
(Kinda like a rooster in his hen house).
My only goal is to improve performance. We are currently dropping anywhere
from 3% up to 30% of our requests as we cant complete them in time due to DB
Bottlenecks.
Your suggestions are wise. I am trying really hard to be positive with him,
but there comes a point where the rubber hits the road too. We are
increasing volume dramatically in production so if we dont get this fixed
fast, we are literally gonna be outta business.
Thanks again, always good to hear from you.
gaj|||Since I got in late I see you have some good answers but I just wanted to
make some of my comments clear.
Fragmentation does not mean more reads unless you are doing range type
queries. While it can mean more reads it's usually not at thelevel you
would expect when doing index seeks. Fill factors can affect the number of
pages read but should not be confused with fragmentation. Proper fill
factors can also avoid or minimize page splits but a few page splits are to
be expected. As Tibor points out an index on a FK can be a real benefit if
you have RI declared. If so and you don't have an appropriate index you can
get scans related to the RI ad not the actual WHERE clause. In general I
think your approach is solid and worth while. But since time is limited
(for most of us) you should prioritize the tasks by findingthe worst
performing queries that are used most often. That will give you the biggest
bang for the buck.
Andrew J. Kelly SQL MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>