指数臃肿？ [英] index bloat?

问题描述

我有一个包含大约3.05亿行的表，以及一个复合主键

，它包含一个升序int和一个递增的varchar （18），其中
的长度通常为13.即使所有的密钥都使用了varchar的全部18个字符，我觉得每个密钥应该是22字节，

所以索引应该大约是6.4GB。但是，EM中显示的索引大小约为24GB，这大大减慢了所有内容的价值。有没有人认为这个索引大小有点过分，或者知道为什么它应该这么大？


谢谢，

Seth

解决方案

sql_server_2000_user（se*********@comcast.net）写道：

我有一个包含大约3.05亿行的表，以及一个复合主键
，它由升序int和升序varchar（18）组成，其长度通常为13。即使所有的键都使用了varchar的全部18个字符，在我看来每个键应该是22个字节，所以索引应该大概是6.4GB。但是，EM中显示的索引大小约为24GB，这大大减慢了所有内容。有没有其他人认为这个索引大小有点过分，或者知道为什么它应该这么大？

那是聚簇索引还是非索引clustered iodex？

聚簇索引在索引节点的叶子中有数据页，所以

索引的大小基本上是数据的大小。 />
-

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


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

只是一个猜测，但你的主键是聚簇索引？聚集的

索引表示数据库中每一行的实际内容，因此它将有意义，它将大于仅索引字段。


如果是这种情况，您可能希望将该索引更改为不是
clustered并选择更合适的字段来生成聚簇索引。


谢谢，

Tony


" sql_server_2000_user" < SE ********* @ comcast.net>在消息中写道

news：11 ********************** @ g49g2000cwa.googlegr psps.com：

我有一个大约有3.05亿行的表，还有一个复合主键，它由升序int和升序varchar（18）组成，
通常长度为13.即使所有键都使用了varchar的全部18个字符，在我看来每个键应该是22个字节，所以索引应该大致为6.4 GB。但是，EM中显示的索引大小约为24GB，这大大减慢了所有内容。有没有人认为这个索引大小有点过分，或者知道为什么它应该这么大？

谢谢，
Seth

是！它故意是一个聚集索引。但是如果db在报告聚集索引的大小时包含了

行数据页，为什么

索引的大小不仅等于桌子（105GB？）


谢谢，

塞思

Hi,

I have a table with about 305 million rows, and a composite primary key
that consists of an ascending int and an ascending varchar(18), which
is typically of length 13. Even if all the keys used the full 18
characters of the varchar, it seems to me each key should be 22 bytes,
so the index should be roughly 6.4GB. However, the size of the index as
shown in EM is about 24GB, and this is slowing everything down
considerably. Does anyone else think this index size is a little
excessive, or know why it should be so large?

Thanks,
Seth

解决方案

sql_server_2000_user (se*********@comcast.net) writes:

I have a table with about 305 million rows, and a composite primary key
that consists of an ascending int and an ascending varchar(18), which
is typically of length 13. Even if all the keys used the full 18
characters of the varchar, it seems to me each key should be 22 bytes,
so the index should be roughly 6.4GB. However, the size of the index as
shown in EM is about 24GB, and this is slowing everything down
considerably. Does anyone else think this index size is a little
excessive, or know why it should be so large?

Is that a clustered index or a non-clustered iodex?

A clustered index has the data pages in the leafs of the index node, so
size of index is basically size of data.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

Just a guess, but is your primary key a clustered index? The clustered
index represents the actual contents of each row in your database, so it
would make sense that it would be larger than only the indexed fields.

If this is the case, you might want to change that index to not be
clustered and choose a more appropriate field to make a clustered index.

Thanks,
Tony

"sql_server_2000_user" <se*********@comcast.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com:

Hi,

I have a table with about 305 million rows, and a composite primary key
that consists of an ascending int and an ascending varchar(18), which
is typically of length 13. Even if all the keys used the full 18
characters of the varchar, it seems to me each key should be 22 bytes,
so the index should be roughly 6.4GB. However, the size of the index as
shown in EM is about 24GB, and this is slowing everything down
considerably. Does anyone else think this index size is a little
excessive, or know why it should be so large?

Thanks,
Seth

Yes! It is intentionally a clustered index. But if the db includes the
row data pages when it reports the size of a clustered index, why is
the size of the index not just equal to the size of the table (105GB?)

Thanks,
Seth

这篇关于指数臃肿？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！