SQL Server碎片问题 [英] SQL Server Fragmentation Problems

问题描述

我的数据库中有几张表（User& UserRecord）被极度分散（如99％），并导致整个数据库，因此网站会停止。

I have a couple of tables (User & UserRecord) in my database that get extremely fragmented (like 99%) and cause the whole database and therefore the website to grind to a halt.

UserRecord类似于某个时间点该用户的快照。用户就像该用户的主记录。用户拥有0到多个UserRecords。用户拥有大约一百万行，UserRecord有大约250万。这些表写得很多。他们也在搜索很多。他们都会变得更大。用户和用户记录表的主要关键点是主要分散的索引。

UserRecord is kind of like a snapshot of that user at a point in time. User is like the master record for that user. User has 0 to many UserRecords. User has around a million rows, UserRecord has around 2.5 million. These tables get written to a lot. They're also being searched a lot. They're both going to get a lot bigger. The main indexes getting badly fragmented are the primary keys of the User and UserRecord tables.

数据库是SQL Server 2012，我正在使用实体框架，我没有使用任何存储过程。

The DB is SQL Server 2012 and I'm using Entity Framework and I'm not using any stored procedures.

表看起来像这样：

USER
UserName string PK ClusteredIndex
FirstName string
LastName string
+SeveralMoreRows

USER_RECORD
UserRecordId int PK ClusteredIndex
ListId int FK(List)
UserName string FK(User) NonClusteredIndex
Community string NonClusteredIndex
DateCreated datetime
+LotsMoreRows

LIST 
ListId int PK & ClusteredIndex
Name string
DateCreated datetime

（不确定如果列表重要或者没有，但以为我会把它包含在与User_Record有关的列表中有0到多个UserRecords）

(not sure if List this is important or not but thought I'd include it as it's related to User_Record. List has 0 to many UserRecords)

我们已经设置了一个SQL维护计划来重建每个索引这有帮助，但有时还不够。

We've set a SQL Maintenance plan to rebuild the indexes daily which does help, but is sometimes not enough.

朋友建议我们使用两个数据库，一个用于读取，一个用于写入，并且我们同时从写入数据库读取DB。不是我知道这样做，但是我看到的第一个问题是这个解决方案，我们需要查看网站时的最新数据。例如，如果我们更新用户详细信息或UserRecord，我们希望立即看到这些更改。

A friend has suggested we use two databases, one for reading, one for writing, and we sync the read DB from the write DB. Not that I know anything about doing this, but the first problem I see with this solutation is that we need up to date data when viewing the site. For example if we update a User details or a UserRecord, we want to see those changes straight away.

有没有人有任何建议，我可以如何解决这个问题之前螺旋失控？

Does anyone have any suggestions on how I can fix this problem before it spirals out of control?

推荐答案

聚簇索引控制DISK上的数据顺序。这是通常建议您设置始终增加的整数键作为聚簇索引的主要原因之一。这样，随着更多数据被添加到表中，它们被添加到当前现有数据的末尾。

Clustered indexes control the order of the data on the DISK. This is one of the main reasons why it's usually recommended that you set up a always increasing integer key to act as the clustered index. This way as more data is added to the table, they're added to the end of the currently existing data.

如果它不是一个自动增加的数字，新行可能包含将在现有值之间订购的值，则SQL Server将基本上将数据推送到其所属的磁盘（以保留聚簇索引键值的顺序），从而产生碎片和潜在的严重开销，因为IO写入进一步减慢数据库。

If it's not an autoincreasing number and new rows may contain values that would be ordered somewhere between existing values, then SQL Server will basically push the data onto the disk where it belongs (to retain the order of the clustered index key values), producing fragmentation and potentially severe overhead as IO writes further slowing down the database.

我怀疑您的UserRecord值有相同的问题。

I suspect you have the same problem with your UserRecord values.

所以我会做什么，为每个表添加一个单独的聚集自动增量主键，并重新设置您的FK引用&有需要时查询。

So what I would do, is add a separate clustered autoincreasing primary key to each table and rework your FK references & queries where necessary.

这篇关于SQL Server碎片问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！