佳礼资讯网

 找回密码
 注册

ADVERTISEMENT

查看: 2722|回复: 5

DBCC reIndex & DBCC indexdefrag

[复制链接]
发表于 28-2-2011 02:32 PM | 显示全部楼层 |阅读模式
我想问各位大大关于DBCC reIndex & DBCC indexdefrag的区别。。。。
请问它们的分别和用途???
回复

使用道具 举报


ADVERTISEMENT

发表于 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."
回复

使用道具 举报

Follow Us
您需要登录后才可以回帖 登录 | 注册

本版积分规则

 

ADVERTISEMENT



ADVERTISEMENT



ADVERTISEMENT

ADVERTISEMENT


版权所有 © 1996-2023 Cari Internet Sdn Bhd (483575-W)|IPSERVERONE 提供云主机|广告刊登|关于我们|私隐权|免控|投诉|联络|脸书|佳礼资讯网

GMT+8, 29-3-2024 08:44 AM , Processed in 0.056201 second(s), 24 queries , Gzip On.

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表