没有密钥的表上的索引损坏：可能是什么原因？ [英] Index corruption on table without key: what could be the reasons ?

问题描述

亲爱的小组，


我们正在运行一个大约30 GB的SQL-Server数据库。这个数据库的目的是为了包含来自自动

设备的定期数据，这些数据会将值插入到某些表格中。


不幸的是这些表中没有密钥（当应用程序员更改了他们的

软件时，一个密钥只能引入
）。表格具有这种结构


deviceno时间戳数据

我们期望每个设备和时间戳一行数据。


在正在进行的操作中，这个大表的索引会被损坏，并且这个表中的select会为某些

设备产生2行。


实际上选择SELECT DEVICENO，TIMESTAMP，COUNT（*）FROM TABLE GROUP

BY DEVICENO，TIMESTAMP HAVING COUNT（*）> 1 QUOT;返回大量数据。


重建索引后，表格为干净。再次。


什么可能导致索引损坏？


缺少密钥？

错误的应用程序？

a两者的组合？

如果没有更新的数据库/应用程序，我怎样才能防止这种情况再次发生？


我会感激任何有用的评论


问候


Uli

Dear group,

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don''t have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

How can i prevent this from happening again, as long as there is no
updated database / application ?

I''d be grateful for any useful comment

Regards

Uli

推荐答案

只是为了澄清一个常见的误解：关键约束用于

数据完整性，索引是用来提高性能。

虽然主键在创建时也引入了一个索引，但它们并不是相同的东西。您可以在没有主键的情况下创建索引

all（这是一种可以在数据仓库中使用的技术，其中

规范化规则有点宽松） ）。索引碎片是否与密钥的存在与否无关;但是，它可以是与底层聚簇索引相关的
（默认情况下）

与主键相关联。


几个问题：你在这张桌子上有聚集索引吗？

它位于什么栏目？


你知道怎么用吗？ DBCC命令SHOWCONTIG？它会帮助你

确定它是碎片，以及它发生的地方。

http：//www.sql-server-performance.co...showcontig.asp


HTH，

Stu

Just to clarify a common misconception: key constraints are used to
enfore data integrity, and indexes are used to enhance performance.
While primary keys also introduce an index upon creation, they are not
the same thing; you can create indexes without having primary keys at
all (this is a technique that can be used in data warehouses, where the
normalization rules are a bit "looser"). Index fragmentation is
unrelated to the presence or absence of keys; it can be, however,
related to the underlying clustered index that is (by default)
associated with the primary key.

A couple of questions: do you have a clustered index on this table?
On what column is it located?

Do you know how to use the DBCC command SHOWCONTIG? It will help you
identify it is fragmentation, and where it is occurring.

http://www.sql-server-performance.co...showcontig.asp

HTH,
Stu

嗨


你没说什么索引打开，是否是一个独特的索引。


您可能需要阅读：
http://www.aspfaq.com/show.asp?id=2081
http://www.aspfaq.com/etiquette.asp?id=5006


John



< ul ********* @ lycos.at>在消息中写道

news：11 ********************** @ g44g2000cwa.googlegr oups.com ...

Hi

You do not say what the index is on, and whether it is a unique index.

You may want to read:
http://www.aspfaq.com/show.asp?id=2081
http://www.aspfaq.com/etiquette.asp?id=5006

John


<ul*********@lycos.at> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

亲爱的小组，

我们正在运行一个大约30 GB的SQL-Server数据库。这个数据库的目的是包含来自自动设备的定期数据，这些设备将值插入到某些表中。

不幸的是，大多数这些表都没有密钥（和只有当应用程序员更改了他们的软件时才能引入密钥。表具有这种结构

deviceno时间戳数据

我们期望每个设备和时间戳一行数据。

在正在进行的操作中它发生这个大表的索引被破坏了，这个表中的一个选择为一些
设备产生了2行。

实际上选择了SELECT DEVICENO，TIMESTAMP，COUNT （*）FROM TABLE GROUP
BY DEVICENO，TIMESTAMP有COUNT（*）> 1 QUOT;返回大量数据。

重建索引后，表格干净。再次。

什么可能导致索引损坏？

缺少密钥？
错误的应用程序？
两者结合？
<如果没有
更新的数据库/应用程序，我怎么能防止这种情况再次发生呢？

我会感激任何有用的评论
<问候

Uli

Dear group,

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don''t have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

How can i prevent this from happening again, as long as there is no
updated database / application ?

I''d be grateful for any useful comment

Regards

Uli

（ul ********* @ lycos.at）写道：

(ul*********@lycos.at) writes:

我们正在运行一个大约30 GB的SQL-Server数据库。这个数据库的目的是包含来自自动设备的定期数据，这些设备将值插入到某些表中。

不幸的是，大多数这些表都没有密钥（和只有当应用程序员更改了他们的软件时才能引入密钥。表具有这种结构

deviceno时间戳数据

我们期望每个设备和时间戳一行数据。

在正在进行的操作中它发生这个大表的索引被破坏了，这个表中的一个选择为一些
设备产生了2行。

实际上选择了SELECT DEVICENO，TIMESTAMP，COUNT （*）FROM TABLE GROUP
BY DEVICENO，TIMESTAMP有COUNT（*）> 1 QUOT;返回大量数据。

重建索引后，表格干净。再次。

什么可能导致索引损坏？

缺少密钥？
错误的应用程序？
两者兼而有之？

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don''t have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

如果重复项在DBCC DBREINDEX之后消失（或者DROP + CREATE，那么

它就是被破坏的索引。


我似乎记得堆表有一个问题可能导致这个问题。（堆表是一个没有聚集索引的表。）

你能把索引定义为集群吗？如果你能把UNIQUE添加到
强制唯一性，那就更好了。再说一遍，听起来好像应用程序能够

到插入重复项？

-

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


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

If the duplicates disappear after a DBCC DBREINDEX (or DROP + CREATE, then
it is the index that is corrupted.

I seem to recall that there is an issue with heap tables that could cause
this. (A heap table is a table that does not have a clustered index.)
Can you define the index as clustered? Even better if you can add UNIQUE to
enforce uniqueness. Then again, it sounds as if the application is able
to insert duplicates?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

这篇关于没有密钥的表上的索引损坏：可能是什么原因？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！