Which is the table beeing split?

Question

Which is the table beeing split?

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

tricky!! I've usually taken a backup to another server to run diagnostics in these types of events. You can do a dbcc showcontig on a table basis - one way to start is to log the table growth - inserts with inappropriate keys may well casue splits - profiler for updates only may help as it's possible this is the issue.

You have to establish if it's a serious performance issue or just a counter value - the stock answer will be to add a fill factor - this is technically correct but applied across a database increases the size it takes up and increases i/o for scans so could actually be counterproductive.

Secondary indexes are often prime offenders so adding a fill factor to integer secondary indexes may help.

At the end of the day only if you think this is a performance issue should you do anything.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

Colin,

Thanks for reply. But my problem is that my DB is very large and the number of tables are very high. Therefore running a dbcc showcontig for that purpose is not an option.

We really have a performanve issue because for some big load in the middle of the load the treatment becomes incredibly slow. So we have to reindex some tables.

So what I'm looking for is a kind of trace where I can see the tables when tey are split (when the counter page splits/sec is incremented). Just as an info: in less then on day this counter has been increased by over 2 million.

Bye
Gabor

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

drat - fire alarm timed out my reply!!! As far as I know the page splits counter is server wide so you can't even pin it to a database - this was a request I put to microsoft at a meeting about sql2007(?) - many of the counters would be so much more suseful if they were more granular, however, just in case you're running profiler - this causes page splits.

So again why do you think page splits are a problem - I know it indicates fragmentation but as far as I know new entries on a heap produce page split entries ( e.g. each new page allocated on a heap fires this counter )

If you know which indexes are fragmented then applying a fill factor may help - however a fill factor assumes that data inserts are distributed across the table/index for it to be effective.

I figure you'll need to take a copy of your database to a test server and analyse your tables there. You can runa dbcc showcontig on an individual table, use the all indexes and table results options to view the entire structure.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/