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.User 大约有 100 万行，UserRecord 大约有 250 万行.这些表被写入了很多.他们也被搜查了很多.他们俩都会变得更大.严重碎片化的主要索引是 User 和 UserRecord 表的主键.

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

(不确定 List 这是否重要，但我想我会包含它，因为它与 User_Record 相关.List 有 0 到多个 UserRecord)

(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 同步读取 DB.并不是说我对执行此操作一无所知，但是我在此解决方案中看到的第一个问题是我们在查看站点时需要最新数据.例如，如果我们更新用户详细信息或用户记录，我们希望立即看到这些更改.

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屋！