Проблемы фрагментации SQL Server

Question

У меня есть несколько таблиц (User & UserRecord) в моей базе данных, которые сильно фрагментированы (например, на 99%) и приводят к остановке всей базы данных и, следовательно, веб-сайта.

UserRecord является своего рода снимком этого пользователя в определенный момент времени. Пользователь похож на основную запись для этого пользователя. Пользователь имеет 0 для многих UserRecords. У пользователя около миллиона строк, у UserRecord - около 2,5 миллиона. Эти таблицы пишутся много. Их также много разыскивают. Они оба станут намного больше. Основные индексы, которые плохо фрагментированы, - это первичные ключи таблиц User и UserRecord.

БД - SQL Server 2012, я использую Entity Framework и не использую хранимые процедуры.

Таблицы выглядят примерно так:

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. В списке от 0 до многих UserRecords)

Мы разработали план обслуживания SQL для ежедневного перестроения индексов, что помогает, но иногда этого недостаточно.

Друг предложил нам использовать две базы данных: одну для чтения, другую для записи, и мы синхронизируем базу данных чтения из базы данных записи. Не то чтобы я что-то знал об этом, но первая проблема, которую я вижу с этим решением, заключается в том, что нам нужны обновленные данные при просмотре сайта. Например, если мы обновляем данные пользователя или UserRecord, мы хотим сразу увидеть эти изменения.

Кто-нибудь есть какие-либо предложения о том, как я могу решить эту проблему, прежде чем она выходит из-под контроля?

Answer 1

Кластерные индексы управляют порядком данных на диске. Это одна из основных причин, по которой обычно рекомендуется устанавливать постоянно растущий целочисленный ключ в качестве кластеризованного индекса. Таким образом, когда в таблицу добавляется больше данных, они добавляются в конец существующих в настоящее время данных.

Если это не число с автоматическим увеличением, и новые строки могут содержать значения, которые будут упорядочены где-то между существующими значениями, то SQL Server будет в основном помещать данные на диск, к которому они принадлежат (чтобы сохранить порядок значений ключа кластеризованного индекса), вызывая фрагментацию и потенциально серьезные накладные расходы, так как ввод-вывод еще больше замедляет работу базы данных.

Я подозреваю, что у вас та же проблема с вашими значениями UserRecord.

Поэтому я бы добавил отдельный кластерный автоинкрементный первичный ключ к каждой таблице и, при необходимости, переработал ваши ссылки и запросы FK.