August 26, 2009 at 8:20 am
I have a large table with poor performance. I ran a DBCC SHOWCONTIG on it, then reindexed, then re ran the DBCC SHOWCONTIG. The before and after are below.
DBCC SHOWCONTIG scanning 'PAYSLIP_ELEM_HISTORY' table...
Table: 'PAYSLIP_ELEM_HISTORY' (864945135); index ID: 1, database ID: 18
TABLE level scan performed.
- Pages Scanned................................: 153050
- Extents Scanned..............................: 19268
- Extent Switches..............................: 19268
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.29% [19132:19269]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 20.94%
- Avg. Bytes Free per Page.....................: 88.0
- Avg. Page Density (full).....................: 98.91%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
---------------------------------------------------------------------------------------------
DBCC SHOWCONTIG scanning 'PAYSLIP_ELEM_HISTORY' table...
Table: 'PAYSLIP_ELEM_HISTORY' (864945135); index ID: 1, database ID: 18
TABLE level scan performed.
- Pages Scanned................................: 173013
- Extents Scanned..............................: 21778
- Extent Switches..............................: 21777
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.31% [21627:21778]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.15%
- Avg. Bytes Free per Page.....................: 1012.0
- Avg. Page Density (full).....................: 87.50%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now the question I have is as follows. Clearly there is a massive benefit on the scan fragmentation which should give obvious benefits. However I have altered the Fill Factor meaning that we have more pages / extents now. Will the additional extent switches mitigate the performance benefit seen elsewhere ?
August 26, 2009 at 8:27 am
No. Additional pages usually just means it won't fragment as rapidly next time. Should have just about zero impact on selects and deletes, and might improve the speed of inserts and updates (since they won't have to perform as many page splits). And the lack of fragmentation will mean that selects will probably slow down less over time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 8:31 am
Which works fine for me as the vast majority of issues on the table are inserts. So should be cooking on gas.
August 26, 2009 at 8:35 am
I'm assuming the clustered index isn't something that just sequentially increments, or you wouldn't get that level of fragmentation. Is changing to something that increments possible?
Ideas for that would be an identity column, a datetime stamp, or a NewSequentialID GUID. Any of those as the leading edge of the clustered index will reduce fragmentation from inserts to close to zero.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 8:42 am
Sadly its a database from a third party so I am limited in terms of what I can break ! The Clustered index is accross 5 fields, 3 chars, a smallint and a datetime !
August 26, 2009 at 8:47 am
I know what you mean.
With that, it sounds like setting the fill factor low and rebuilding indexes frequently is your best bet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 9:26 am
Ouch, have to agree with GSquared. That clustered index scheme should be re-evaluated by the vendor. Setting your fillfactor low and defragging will help this scenario.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply