最近系统查询统计的某些报表速度特别慢
分析了下,这些查询都走一个表'T_TABLE_USER',当初我都是加过索引的,一共40来个索引,当时并不慢,现在怎么如此之慢?
看了下,原来是需要重建索引了.
用DBCC SHOWCONTIG('T_TABLE_USER')查看是否需要重建索引
----重建索引前
DBCC SHOWCONTIG scanning 'T_TABLE_USER' table...
Table: 'T_TABLE_USER' (1517157096); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 75761
- Extents Scanned..............................: 9538
- Extent Switches..............................: 75757
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 12.50% [9471:75758]
- Logical Scan Fragmentation ..................: 98.90%
- Extent Scan Fragmentation ...................: 78.82%
- Avg. Bytes Free per Page.....................: 3161.2
- Avg. Page Density (full).....................: 60.94%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--重建索引 DBCC DBREINDEX('T_TABLE_USER','',90)之后
DBCC SHOWCONTIG scanning 'T_TABLE_USER' table...
Table: 'T_TABLE_USER' (1517157096); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 51824
- Extents Scanned..............................: 6478
- Extent Switches..............................: 6477
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6478:6478]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.73%
- Avg. Bytes Free per Page.....................: 604.7
- Avg. Page Density (full).....................: 92.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
完成后,查询速度明显提升了.
编辑标志
本帖最后由［完美行动］在 2007-12-21 11:46:59 编辑
40 INDEX
