Answer is NO ..but to come to this conclusion , I had to spend some time .One of my colleagues came to me with this question .My instant answer was a clear NO ...But then I asked him with a curiosity the reason for asking this question .As per him the nightly index reorg job that should run for a very long time , finished in just 3 hours .Also we cannot check the index fragmentation since it takes around 2 hours .Our tables are huge ..
I thought of 3 reasons :
1) Since there is a logic in our job to do re-org only if there is certain level of fragmentation , that day there might be no index coming in the rebuild category.This possibility was less but cannot be ruled out .
2) The restore actually reshuffled the pages and in that attempt cleared out some leaf level fragmentation .I remembered that restores take more time than backups .So I started believing this .
3) There might be some Re-org activity happening during the time the backups were happening .The backup might have a copy of well re-orged pages and this might have resulted in a less fragmented database .
I first started off with point 2 and soon realized that I was not correct .This did not take me much time .For point one , we added the log in the job so that as the job finishes , it creates the log which we can read .But this will take time to generate.
Now , I was left with option 3 .I had the table with > 99% fragmentation and 24085822 rows .The table size was around 4GB . DBCC Showcontig output is shared below :
DBCC SHOWCONTIG scanning 'stats_test' table...
Table: 'stats_test' (711673583); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 268007
- Extents Scanned..............................: 33569
- Extent Switches..............................: 268005
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 12.50% [33501:268006]
- Logical Scan Fragmentation ..................: 99.22%
- Extent Scan Fragmentation ...................: 0.01%
- Avg. Bytes Free per Page.....................: 3334.3
- Avg. Page Density (full).....................: 58.81%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I thought of 3 reasons :
1) Since there is a logic in our job to do re-org only if there is certain level of fragmentation , that day there might be no index coming in the rebuild category.This possibility was less but cannot be ruled out .
2) The restore actually reshuffled the pages and in that attempt cleared out some leaf level fragmentation .I remembered that restores take more time than backups .So I started believing this .
3) There might be some Re-org activity happening during the time the backups were happening .The backup might have a copy of well re-orged pages and this might have resulted in a less fragmented database .
I first started off with point 2 and soon realized that I was not correct .This did not take me much time .For point one , we added the log in the job so that as the job finishes , it creates the log which we can read .But this will take time to generate.
Now , I was left with option 3 .I had the table with > 99% fragmentation and 24085822 rows .The table size was around 4GB . DBCC Showcontig output is shared below :
DBCC SHOWCONTIG scanning 'stats_test' table...
Table: 'stats_test' (711673583); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 268007
- Extents Scanned..............................: 33569
- Extent Switches..............................: 268005
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 12.50% [33501:268006]
- Logical Scan Fragmentation ..................: 99.22%
- Extent Scan Fragmentation ...................: 0.01%
- Avg. Bytes Free per Page.....................: 3334.3
- Avg. Page Density (full).....................: 58.81%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I then ran Index Reorg and noticed percent_complete column in sys.dm_exec_requests DMV .When it reached 46% , I kicked off the backup of the database .
Now let me restore the backup .....fingers crossed :) ....
DBCC SHOWCONTIG scanning 'stats_test' table...
Table: 'stats_test' (711673583); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 158633
- Extents Scanned..............................: 19862
- Extent Switches..............................: 19866
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.81% [19830:19867]
- Logical Scan Fragmentation ..................: 0.15%
- Extent Scan Fragmentation ...................: 3.47%
- Avg. Bytes Free per Page.....................: 51.2
- Avg. Page Density (full).....................: 99.37%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Mystery solved ....
Happy learning ..
No comments:
Post a Comment