Какой тип данных оптимален для кластеризованного индекса таблицы, опубликованной с использованием репликации транзакций?

У нас есть приложение, которое хранит данные в базе данных SQL-сервера. (В настоящее время мы поддерживаем SQL Server 2005 и выше). В нашей БД более 400 таблиц. Структура базы данных не идеальна. Самая большая проблема заключается в том, что у нас есть много таблиц с GUID (NEWID()) в качестве первичных CLUSTERED ключей. Когда я спросил нашего главного архитектора базы данных «почему?», он ответил: «это из-за репликации». Наша БД должна поддерживать репликацию транзакций. Изначально все первичные ключи были INT IDENTITY(1,1) CLUSTERED. Но позже, когда дело дошло до поддержки репликации, эти поля были заменены на UNIQUEIDENTIFIER DEFAULT NEWID(). Он сказал, что «иначе было бы кошмарно иметь дело с репликацией». NEWSEQUENTIALID() в то время не поддерживался SQL 7/2000. Итак, теперь у нас есть таблицы со следующей структурой:

CREATE TABLE Table1(
        Table1_PID uniqueidentifier DEFAULT NEWID() NOT NULL,
        Field1 varchar(50) NULL,
        FieldN varchar(50) NULL,
        CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Table1_PID)
    )
    GO

CREATE TABLE Table2(
    Table2_PID uniqueidentifier DEFAULT NEWID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED (Table2_PID),
    CONSTRAINT FK_Table2_Table1 FOREIGN KEY (Table1_PID) REFERENCES Table1 (Table1_PID)
)
GO

Все таблицы на самом деле имеют очень много полей (до 35) и до 15 некластеризованных индексов.

Я знаю, что GUID, который не является последовательным, например тот, значения которого сгенерированы в клиенте (с использованием .NET) ИЛИ сгенерированы функцией NEWID() SQL (как в нашем случае), является ужасно плохим выбором для кластерного индекса для две причины:

1. фрагментация
2. размер

Я также знаю, что ХОРОШИЙ ключ кластеризации заключается в том, что он:

1. уникальный,
2. узкий,
3. статический,
4. постоянно увеличивающийся,
5. необнуляемый,
6. и фиксированная ширина

Дополнительные сведения о причинах этого см. в следующем замечательном видео: http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx.

Итак, INT IDENTITY действительно лучший выбор. BIGINT IDENTITY также хорош, но обычно INT с 2+ миллиардами строк должно быть достаточно для подавляющего большинства таблиц.

Когда наши клиенты начали страдать от фрагментации, было принято решение сделать первичные ключи НЕкластерными. В результате эти таблицы остались без кластеризованного индекса. Другими словами, эти таблицы были превращены в HEAPS. Лично мне это решение не нравится, потому что я уверен, что таблицы кучи не являются частью хорошей структуры базы данных. Пожалуйста, ознакомьтесь с этой статьей с рекомендациями по использованию SQL Server: http://technet.microsoft.com/en-us/library/cc917672.aspx.

В настоящее время мы рассматриваем два варианта улучшения структуры базы данных:

Первый вариант — заменить DEFAULT NEWID() на DEFAULT NEWSEQUENTIALID() для первичного кластерного ключа:

CREATE TABLE Table1_GUID (
  Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
  Field1 varchar(50) NULL,
  FieldN varchar(50) NULL,
  CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Table1_PID)
)
GO

Второй вариант — добавить столбец INT IDENTITY в каждую таблицу и сделать его индексом CLUSTERED UNIQUE, оставив первичный ключ НЕ кластеризованным. Таким образом, Table1 будет выглядеть так:

CREATE TABLE Table1_INT (
  Table1_ID int IDENTITY(1,1) NOT NULL,
  Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
  Field1 varchar(50) NULL,
  FieldN varchar(50) NULL,
  CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (Table1_PID),
  CONSTRAINT UK_Table1 UNIQUE CLUSTERED (Table1_ID)
)
GO

Table1_PID будет использоваться для репликации (поэтому мы оставили его как PK), а Table1_ID вообще не будет реплицироваться.

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

Первый подход (Table1_GUID) выявил следующие недостатки: хотя последовательные GUID определенно намного лучше, чем обычные случайные GUID, они все же в четыре раза больше, чем INT (16 против 4 байтов), и это фактор в нашем случае, потому что у нас много строк в наших таблицах (до 60 миллионов) и много некластеризованных индексов в этих таблицах (до 15). Ключ кластеризации добавляется к каждому некластеризованному индексу, что значительно увеличивает негативный эффект от размера 16 байтов вместо 4. Больше байтов означает больше страниц на диске и в оперативной памяти SQL Server и, следовательно, больше дискового ввода-вывода и больше работы для SQL Server.

Точнее, после того, как я вставил 25 миллионов строк реальных данных в каждую таблицу, а затем создал 15 некластеризованных индексов для каждой таблицы, я увидел большую разницу в пространстве, используемом таблицами:

EXEC sp_spaceused 'Table1_GUID' -- 14.85 GB
EXEC sp_spaceused 'Table1_INT' -- 11.68 GB

Кроме того, тест показал, что операции INSERT в Table1_GUID выполняются немного медленнее, чем в Table1_INT.

Второй подход (Table1_INT) показал, что в большинстве запросов (SELECT) соединение двух таблиц по плану выполнения Table1_INT.Table1_PID = Table2_INT.Table1_PID стало хуже из-за появления дополнительного оператора Key Lookup.

Теперь вопрос: Я считаю, что для нашей проблемы должно быть лучшее решение. Если бы вы могли порекомендовать мне что-то или указать мне на хороший ресурс, я был бы очень признателен. Заранее спасибо.

Обновлено:

Позвольте мне привести вам пример оператора SELECT, в котором появляется дополнительный оператор Key Lookup:

--Create 2 tables with int IDENTITY(1,1) as CLUSTERED KEY.
--These tables have one-to-many relationship.
CREATE TABLE Table1_INT (
    Table1_ID int IDENTITY(1,1) NOT NULL,
    Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table1_INT PRIMARY KEY NONCLUSTERED (Table1_PID),
    CONSTRAINT UK_Table1_INT UNIQUE CLUSTERED (Table1_ID)
)
GO

CREATE TABLE Table2_INT(
    Table2_ID int IDENTITY(1,1) NOT NULL,
    Table2_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2_INT PRIMARY KEY NONCLUSTERED (Table2_PID),
    CONSTRAINT UK_Table2_INT UNIQUE CLUSTERED (Table2_ID),
    CONSTRAINT FK_Table2_Table1_INT FOREIGN KEY (Table1_PID) REFERENCES Table1_INT (Table1_PID)
)
GO

И создайте две другие таблицы для сравнения:

--Create the same 2 tables, BUT with uniqueidentifier NEWSEQUENTIALID() as CLUSTERED KEY.
CREATE TABLE Table1_GUID (
    Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table1_GUID PRIMARY KEY CLUSTERED (Table1_PID),
)
GO

CREATE TABLE Table2_GUID(
    Table2_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2_GUID PRIMARY KEY CLUSTERED (Table2_PID),
    CONSTRAINT FK_Table2_Table1_GUID FOREIGN KEY (Table1_PID) REFERENCES Table1_GUID (Table1_PID)
)
GO

Теперь запустите следующие операторы select и посмотрите на план выполнения для сравнения:

SELECT T1.Field1, T2.FieldN
FROM Table1_INT T1 
    INNER JOIN Table2_INT T2 
        ON T1.Table1_PID = T2.Table1_PID;

SELECT T1.Field1, T2.FieldN
FROM Table1_GUID T1 
    INNER JOIN Table2_GUID T2 
        ON T1.Table1_PID = T2.Table1_PID;