DBCC SHOWCONTIG不一致???? [英] DBCC SHOWCONTIG inconsistency????

问题描述

我有一张桌子，我在那里存放了大约100万行，为期7天。

我每周运行一次DBCC SHOWCONTIG，我注意到

行的数量和平均自由字节数是稳定的，但是范围数量
和平均记录大小不断增加。

任何想法如何解释这个问题？以及如何阻止它？请问DBCC

DBREINDEX会有帮助吗？


这是我的数据：

行：1166273（与上周相同）

范围：147099（自上周以来增加10％）

最大记录大小：7050

平均记录大小：7688（自上周以来增加10％）

AverageFreeBytes：372

平均页面密度：95
扫描密度：30

解决方案

Guillaume（lo*****@hotmail.com）写道：

我有一个表，我存储了大约100万行，为期7天。<我每周都会运行一个DBCC SHOWCONTIG，我注意到
行数和平均自由字节数是稳定的，但是Extents
和平均记录大小的数量一直在增加。
知道如何解释这个问题吗？以及如何阻止它？请问DBCC
DBREINDEX会有所帮助吗？

这是我的数据：
行：1166273（与上周相同）
范围：147099（自上周以来增加10％） ）
Max RecordSize：7050
平均记录大小：7688（自上周以来增加10％）
AverageFreeBytes：372
平均页面密度：95
扫描密度：30


如果行大小增加，则表明较新的数据

的行大小较高。但它不能长期增加10％，因为8060字节是最大行大小。 （除非该行大小包括

文本/图像数据。）

每天插入大约150,000个新行和150,000行（比7天更长）每天删除。

这肯定会造成碎片化，你应该每周至少运行一次DBCC DBREINDEX

。


-

Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se


SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland，

平均记录大小已经达到7984字节，而且还在增加

。

我第一次遇到问题时我的文件组已满，而且我没有时间调查这个问题所以我只是截断了表格。现在，我在文件组中有足够的空间，我想在运行任何DBCC DBREINDEX之前了解问题

。

表的统计数据可能是错误的吗？或者没有更新？


***通过开发人员指南 http： //www.developersdex.com ***

Guillaume（an*******@devdex.com）写道：

平均记录大小已经达到了7984字节而且还在增加。
我第一次遇到问题我的文件组已经满了而且我没有
是时候调查这个问题所以我只是截断了表格。现在，我在文件组中有足够的空间，我想在运行任何DBCC DBREINDEX之前了解问题。
表的统计数据是否有误？或者没有更新？

如果平均记录大小不断增加，那可能是因为你的数据是

。如果这是不正常的，你需要查看你b $ b b加载的数据。或者你有理由认为这个数字是不正确的吗？

你的最大记录大小是多少？

-

Erland Sommarskog， SQL Server MVP， es****@sommarskog.se


联机丛书SQL Server SP3
http：// www.microsoft.com/sql/techinf...2000/books.asp

I have a table where I store around 1 million rows for 7 days.
I run a DBCC SHOWCONTIG every week and I noticed that the number of
Rows and the Average Free Bytes are stable but the number of Extents
and the Average Record Size keeps increasing.
Any idea how to explain this incrase? and how to stop it? Will DBCC
DBREINDEX will help?

Here is my data:
Rows: 1166273 (same as last week)
Extents: 147099 (+10% since last week)
Max RecordSize: 7050
Average Record Size: 7688 (+10% since last week)
AverageFreeBytes: 372
Average Page Density: 95
Scan density: 30

解决方案

Guillaume (lo*****@hotmail.com) writes:

I have a table where I store around 1 million rows for 7 days.
I run a DBCC SHOWCONTIG every week and I noticed that the number of
Rows and the Average Free Bytes are stable but the number of Extents
and the Average Record Size keeps increasing.
Any idea how to explain this incrase? and how to stop it? Will DBCC
DBREINDEX will help?

Here is my data:
Rows: 1166273 (same as last week)
Extents: 147099 (+10% since last week)
Max RecordSize: 7050
Average Record Size: 7688 (+10% since last week)
AverageFreeBytes: 372
Average Page Density: 95
Scan density: 30
If he row size is growing that indicates that the newer data
has higher row size. But it cannot increase with 10% for long,
since 8060 bytes is the max row size. (Unless that row size includes
text/image data.)
Around 150,000 new rows are inserted every day and 150,000 rows (older
than 7 days) are deleted every day.

That can certainly do a fragmentation, and you should run DBCC DBREINDEX
at least once a week.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland,
The Average Record Size has reached 7984 bytes and it''s still
increasing.
The first time I had the problem my filegroup was full and I didn''t have
time to investigate the issue so I just truncated the table. Now, I have
plenty of space in the filegroup and I''d like to understand the problem
before running any DBCC DBREINDEX.
Could the statistics for the table be wrong? or not updated?

*** Sent via Developersdex http://www.developersdex.com ***

Guillaume (an*******@devdex.com) writes:

The Average Record Size has reached 7984 bytes and it''s still
increasing.
The first time I had the problem my filegroup was full and I didn''t have
time to investigate the issue so I just truncated the table. Now, I have
plenty of space in the filegroup and I''d like to understand the problem
before running any DBCC DBREINDEX.
Could the statistics for the table be wrong? or not updated?

If the average record size keeps increaseing, it''s probably because of
your data. If that is abnormal, you will need to look at the data you
load. Or do you have any reason to assume that the number is incorrect?
What is your maximum record size?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

这篇关于DBCC SHOWCONTIG不一致????的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！