February 14, 2013 at 10:03 pm
Hi,
Actually I have one issue regarding my one table. Before fragmentation it's scan value dramatically change and it's performance go down.
Everyday manually i am fragment this table to get good scan density . I don't know why lot of variation between scan density before fragmentation and after fragmentation.
I don't know why Avg. Bytes Free per Page is not going below 10 when i rebuild the index.
Can someone help.........is this right to EVERY DAY rebuild the index for this table to get better percentage of scan density or also other factor.
Before Fragmentation
/*------------------------
DBCC SHOWCONTIG (SKUMasterSwift) WITH ALL_INDEXES
------------------------*/
DBCC SHOWCONTIG scanning 'SKUMASTERSWIFT' table...
Table: 'SKUMASTERSWIFT' (316632271); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 9870
- Extents Scanned..............................: 1255
- Extent Switches..............................: 4512
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 27.34% [1234:4513]
- Logical Scan Fragmentation ..................: 73.72%
- Extent Scan Fragmentation ...................: 1.43%
- Avg. Bytes Free per Page.....................: 2516.5
- Avg. Page Density (full).....................: 68.91%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After fragmentation
/*------------------------
DBCC SHOWCONTIG (SKUMasterSwift) WITH ALL_INDEXES
------------------------*/
DBCC SHOWCONTIG scanning 'SKUMASTERSWIFT' table...
Table: 'SKUMASTERSWIFT' (316632271); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 6833
- Extents Scanned..............................: 855
- Extent Switches..............................: 854
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [855:855]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.94%
- Avg. Bytes Free per Page.....................: 36.7
- Avg. Page Density (full).....................: 99.55%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Regards,
Sachin.
February 15, 2013 at 6:47 am
still waiting for response....
February 15, 2013 at 7:16 am
dsachu (2/15/2013)
still waiting for response....
You get what you pay for.
This is a free website and the people who volunteer to answer questions here do so on there own time. Perhaps no one who has read this post as an answer for you at this time.
February 15, 2013 at 7:23 am
dsachu (2/15/2013)
still waiting for response....
I read the post this morning, didn't have time to reply. Was going to reply now, but have paying work to do instead.
We're volunteers. We post in our spare time. We don't get paid for this. Want immediate response, hire a consultant.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply