查看: 2722|回复: 5
|
DBCC reIndex & DBCC indexdefrag
[复制链接]
|
|
我想问各位大大关于DBCC reIndex & DBCC indexdefrag的区别。。。。
请问它们的分别和用途??? |
|
|
|
|
|
|
|
发表于 3-3-2011 02:32 PM
|
显示全部楼层
DBREINDEX (This feature will be removed in a future version of Microsoft SQL Server)
- rebuilds an index for a table or all indexes defined for a table
- offline operation, cause blocking where rebuild operation
- When Non-clustered index rebuild, data can't modify, when clustered index rebuild, table cant access.
you will see table been blocked.
- Do during off peak hours or production downtime
DBCC Indexdefrag
- defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance.
- online operation, use still can access the table
- advice perform during off peak but it won't affect user
For SQL 2005 and above
You can use index rebuild/reorganized function as well
use this function sys.dm_db_index_physical_stats to identify what tables/indexes need to be rebuilt/reorganized
Rebuild
equivalent to DBCC DBREINDEX
Cause Blocking except with Online=on (which available only in SQL Server Enterprise, Developer, and Evaluation editions)
Reorganized
equivalent to DBCC INDEXDEFRAG
Online
Check whether we should use alter index with rebuild or reorganized
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'tablename'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
if avg_fragmentation_in_percent>30%, rebuilding
if avg_fragmentation_in_percent >5% and <=30% , reorganizing , must update statistics afterwards
if avg_fragmentation_in_percent< 5% can be ignore
For Rebuilding,
ALTER INDEX PK_tablename ON table
REBUILD;
For reorganizing
ALTER INDEX PK_tablename ON DECIPHER_DATA REORGANIZE;
REference :
DBCC index Defrag : http://msdn.microsoft.com/en-us/library/aa258286(v=sql.80).aspx
DBREINDEX http://msdn.microsoft.com/en-us/library/ms181671.aspx
Alter index http://technet.microsoft.com/en-us/library/ms188388.aspx |
|
|
|
|
|
|
|
楼主 |
发表于 7-3-2011 10:05 AM
|
显示全部楼层
Thank for you reference, another thing is how to do the update statistics??
beside that you give the query is use in sql 2005 rights? then how about sql 2000? |
|
|
|
|
|
|
|
发表于 8-3-2011 11:30 PM
|
显示全部楼层
回复 3# 奋斗1314
SQL 2000 就用 DBCC Indexdefrag 及DBREINDEX 吧
我通常都用这个
UPDATE STATISTICS tablename;
tablename = tables which have heavy update, delete, insert, select.
或用
sp_updatestats
update all table in the database |
|
|
|
|
|
|
|
楼主 |
发表于 9-3-2011 08:29 AM
|
显示全部楼层
thanks for your reply....哪你知道 UPDATE STATISTICS 的用途吗?? |
|
|
|
|
|
|
|
发表于 10-3-2011 12:53 PM
|
显示全部楼层
回复 5# 奋斗1314
Statistics 协助SQLServer 决定使用那个index.
We can make sqlserver auto update statistics and we should.
SQL Server will only update statistics if update,insert/delete more than 20% of total records in the table.
If number record too huge for a table, the statistics may not updated
If statistics not updated, SQL may used wrong index to select query and sometime user may complain application slow
If application slow, we can check on execution plan with click on "Include Actual Execution plan" before execute the query. After execute the query, look for Execution tab.
Look for this value "Actual number of value" and "estimated number of rows"
"Estimated number of row" - sql estimate row return from statistic before query execute and decide which index or not index should be using.
Actual number of value - actual row return
If this 2 value different lot.
Example Actual number of value=50 and estimated number of rows=5000
Your statistics maybe out, and sql may choose wrong index.
You can manual update statistics with
UPDATE STATISTICS tablename
or
UPDATE STATISTICS tablename indexname
所以
"我通常都用这个
UPDATE STATISTICS tablename;
"Test on test server before test on production server"
tablename = tables which have heavy update, delete, insert, select." |
|
|
|
|
|
|
| |
本周最热论坛帖子
|