October 26, 2009 at 3:26 pm
I thought of reindexing because lots of users were complaining about application timeouts.
I ran DBCC DBREINDEX('my_table',' ',90) on the following table. But it was taking very long. After 2hrs and 45 minutes I cancelled the above query. Now it is trying to cancel the query for more than 45 minutes.
Env Details:
windows 2003 Ent (32bit) + SP2
SQL Server 2005 (9.00.3080.00SP2 Ent)
Dell PE 2950 Xeon5160 #3.00Ghz X 2
8GB RAM
PERC 5E/ RAID10
Took 6 minutes and 7 seconds.
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 1795825
- Extents Scanned..............................: 224906
- Extent Switches..............................: 1185841
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 18.93% [224479:1185842]
- Logical Scan Fragmentation ..................: 60.74%
- Extent Scan Fragmentation ...................: 17.97%
- Avg. Bytes Free per Page.....................: 1873.3
- Avg. Page Density (full).....................: 76.86%
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 6, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 541910
- Extents Scanned..............................: 68020
- Extent Switches..............................: 541907
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 12.50% [67739:541908]
- Logical Scan Fragmentation ..................: 99.21%
- Extent Scan Fragmentation ...................: 31.81%
- Avg. Bytes Free per Page.....................: 2473.6
- Avg. Page Density (full).....................: 69.44%
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 8, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 396757
- Extents Scanned..............................: 49751
- Extent Switches..............................: 294511
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 16.84% [49595:294512]
- Logical Scan Fragmentation ..................: 70.81%
- Extent Scan Fragmentation ...................: 43.87%
- Avg. Bytes Free per Page.....................: 2592.2
- Avg. Page Density (full).....................: 67.97%
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 9, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 269996
- Extents Scanned..............................: 34025
- Extent Switches..............................: 67725
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 49.83% [33750:67726]
- Logical Scan Fragmentation ..................: 23.89%
- Extent Scan Fragmentation ...................: 44.83%
- Avg. Bytes Free per Page.....................: 1356.1
- Avg. Page Density (full).....................: 83.25%
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 15, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 332542
- Extents Scanned..............................: 41722
- Extent Switches..............................: 292442
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 14.21% [41568:292443]
- Logical Scan Fragmentation ..................: 85.59%
- Extent Scan Fragmentation ...................: 23.80%
- Avg. Bytes Free per Page.....................: 2623.8
- Avg. Page Density (full).....................: 67.58%
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 20, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 476176
- Extents Scanned..............................: 60051
- Extent Switches..............................: 178225
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 33.40% [59522:178226]
- Logical Scan Fragmentation ..................: 42.92%
- Extent Scan Fragmentation ...................: 51.47%
- Avg. Bytes Free per Page.....................: 2491.0
- Avg. Page Density (full).....................: 69.22%
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 22, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 905596
- Extents Scanned..............................: 113925
- Extent Switches..............................: 189870
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 59.62% [113200:189871]
- Logical Scan Fragmentation ..................: 18.35%
- Extent Scan Fragmentation ...................: 31.02%
- Avg. Bytes Free per Page.....................: 578.5
- Avg. Page Density (full).....................: 92.85%
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (21575115); index ID: 23, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 635358
- Extents Scanned..............................: 79885
- Extent Switches..............................: 622853
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 12.75% [79420:622854]
- Logical Scan Fragmentation ..................: 96.98%
- Extent Scan Fragmentation ...................: 36.55%
- Avg. Bytes Free per Page.....................: 2536.7
- Avg. Page Density (full).....................: 68.66%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
October 26, 2009 at 3:44 pm
Sanjiv-193662 (10/26/2009)
After 2hrs and 45 minutes I cancelled the above query. Now it is trying to cancel the query for more than 45 minutes.
SQL has to roll the changes back. Usually that takes as long or longer to do than the operation to that point. You cannot cancel a rollback, even if you restart SQL Server the query has to roll back.
Wait. It's about the best thing to do.
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
October 26, 2009 at 4:07 pm
It came back.It took one hour and 45 minutes.
What is the best way to do reindex on such a large table?
October 26, 2009 at 4:44 pm
You are running Enterprise Edition (at least that's what it looks like). You can probably perform your index rebuild online using ALTER INDEX syntax. Look it up in Books Online for further information.
It will be something along the lines of:
ALTER INDEX {your index here} ON {your table} REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB=ON);
If you have a large enough tempdb - you can use it for the index sorts. If your tempdb is not large enough, remove that option. Using it could improve the speed of the rebuild operation - but it all depends upon how your tempdb is configured.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply