August 14, 2006 at 3:28 am
Hi there,
Can somebody tell me how could I easily determine where (for which table) my page splits are occuring?
I'm checking the Page splits/sec counter but I do not see where can I check the tables corresponding to that split.
As I have a fairly big database (I'm running SAP of 27000 tables ant the size is over 775 GB). So running dbcc showcontig is not usable as it is running for a longtime and it is penalizing my users.
I would like to analize the "faulty" indexes to reduse if possible the page splits because we have to reindex some tables several times during some big transactions where we are loading (updateing/inserting) some millions of records.
Bye
Gabor
August 15, 2006 at 10:50 am
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/
August 16, 2006 at 2:08 am
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
August 16, 2006 at 8:40 am
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply