Page Splits - What tables?

Hi everyone,
I've been looking at page splits today for the first time, after noticing
our work server's count was quite high. The number is increasing every
minute, so I thought some action needed taking.
I've done quite a bit of reading up on the subject this morning and
understand much of the theory, but I haven't found a way to track down which
of my tables are actually page splitting so regularly.
Does anyone know a method of finding this out?
Thanks in advance,
LloydUse DBCC showcontig... Tables with a high % full and inserts will be
splitting... Pages with lower may have already split, or do not need
splitting... Rebuild the index to set the fill factor to allow space for
new inserts.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lloyd" <Lloyd@.discussions.microsoft.com> wrote in message
news:E3C26251-CFAA-40AD-86DF-44E9298F68D6@.microsoft.com...
> Hi everyone,
> I've been looking at page splits today for the first time, after noticing
> our work server's count was quite high. The number is increasing every
> minute, so I thought some action needed taking.
> I've done quite a bit of reading up on the subject this morning and
> understand much of the theory, but I haven't found a way to track down
> which
> of my tables are actually page splitting so regularly.
> Does anyone know a method of finding this out?
> Thanks in advance,
> Lloyd|||Cheers Wayne :)
Lloyd
"Wayne Snyder" wrote:
> Use DBCC showcontig... Tables with a high % full and inserts will be
> splitting... Pages with lower may have already split, or do not need
> splitting... Rebuild the index to set the fill factor to allow space for
> new inserts.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Lloyd" <Lloyd@.discussions.microsoft.com> wrote in message
> news:E3C26251-CFAA-40AD-86DF-44E9298F68D6@.microsoft.com...
> > Hi everyone,
> >
> > I've been looking at page splits today for the first time, after noticing
> > our work server's count was quite high. The number is increasing every
> > minute, so I thought some action needed taking.
> >
> > I've done quite a bit of reading up on the subject this morning and
> > understand much of the theory, but I haven't found a way to track down
> > which
> > of my tables are actually page splitting so regularly.
> >
> > Does anyone know a method of finding this out?
> >
> > Thanks in advance,
> >
> > Lloyd
>
>|||if a table has a clustered index on a "Monotonically Increasing" value
(identity) it will probaly not be a major culprit.
Most likely caused by tables where the clustered index is on some other
column (GUIDS for example are great fun).
If you run a showcontig as Wayne pointed out you'll see which tables are
highly fragmented (Scan Density below 80% is a sign of issues).
Fragmentation is greatly caused by Page Splits. So those are your culprits.
Note: If a table is highly fragmented, but it has less than 1,000 pages of
data, dont focus on it. Focus on the guys with thousands and thousands of
data pages.
You will see performance improve leaps and bounds when you get this fixed.
If you have questions, feel free to email me directly
cheers
Greg Jackson
PDX, Oregon