Сегодня в рассылке MVP задали вопрос о том, как SQL Server ведёт учёт изменений по каждому столбцу таблицы.
Начиная с версии 2008, скрытая системная таблица sys.sysrscols отслеживает изменения столбцов таблицы с помощью столбца rcmodified. Скрытые системные таблицы (появившиеся в 2005 году, когда мы полностью переписали систему управления метаданными) доступны только при подключении через соединение администратора (DAC), о чём я уже много раз писал. Это означает подключение с помощью SQLCMD -A или префикса admin: в строке подключения.
Эти данные также доступны во представлении каталога sys.system_internals_partition_columns, для которого DAC не требуется. (Я об этом подзабыл — спасибо моему другу Remus Rusanu за напоминание!)
Имейте в виду: всё ниже — выведенное поведение, основанное на моих знаниях и наблюдениях, и в будущих версиях оно может полностью измениться. Всё это не документировано, поэтому не стоит строить на этом программные решения. Если я этого не скажу, мой хороший друг Conor Cunningham при встрече меня отшлёпает :-)
Вот пример с простой таблицей:
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
GO
Посмотреть поколоночные счётчики изменений можно DAC‑запросом:
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p
ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID('t1');
GO
И получим:
object_id index_id rscolid rcmodified
———– ———– ———– ———–
277576027 0 1 0
277576027 0 2 0
277576027 0 3 0
Можно использовать и представление sys.system_internals_partition_columns:
SELECT
p.[object_id],
p.[index_id],
pc.[partition_column_id],
pc.[modified_count]
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p
ON pc.[partition_id] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID('t1');
GO
Далее я продолжу с DAC‑запросом непосредственно к sysrscols.
Выполним изменение и снова запустим DAC‑запрос:
INSERT INTO t1 VALUES (1, 1, 1);
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 0
277576027 0 2 0
277576027 0 3 0
Что? Пока не отразилось. Это потому, что обновления некоторых системных таблиц не сбрасываются из кэша метаданных в памяти до тех пор, пока не произойдёт контрольная точка (checkpoint).
Сделаем это и снова выполним DAC‑запрос:
CHECKPOINT;
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 1
277576027 0 2 1
277576027 0 3 1
А теперь для наглядности обновлю только c2 дважды, выполню checkpoint и снова запущу DAC‑запрос.
UPDATE t1 SET c2 = 2;
UPDATE t1 SET c2 = 3;
CHECKPOINT;
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 1
277576027 0 2 3
277576027 0 3 1
Здорово, правда?
Что насчёт столбца rowmodctr в sysindexes? Как он ведётся/рассчитывается?
Он вычисляется как приращение (delta) счётчика sysrscols.rcmodified для ведущего столбца индекса с момента последней перестройки (или создания) статистики по столбцам индекса.
Создадим несколько простых индексов на нашей таблице и спросим rowmodctr в sysindexes:
CREATE NONCLUSTERED INDEX t1_c1_c2 ON t1 (c1, c2);
CREATE NONCLUSTERED INDEX t1_c3 ON t1 (c3);
GO
SELECT
[name],
[rowmodctr]
FROM sysindexes
WHERE [id] = OBJECT_ID('t1');
GO
name rowmodctr
————— ———–
NULL 3
t1_c1_c2 0
t1_c3 0
Первая строка относится к куче, так как я не создавал кластерный индекс.
Внесём изменения и посмотрим, как изменились sysindexes.rowmodctr и sysrscols.rcmodified:
UPDATE t1 SET c1 = 4;
UPDATE t1 SET c1 = 5;
UPDATE t1 SET c1 = 6;
UPDATE t1 SET c2 = 2;
UPDATE t1 SET c2 = 3;
UPDATE t1 SET c3 = 2;
CHECKPOINT;
GO
object_id index_id rscolid rcmodified
———– ———– ———– ——————–
277576027 0 1 4
277576027 0 2 5
277576027 0 3 2
277576027 2 1 0
277576027 2 2 0
277576027 2 3 0
277576027 3 1 0
277576027 3 2 0
name rowmodctr
————— ———–
NULL 5
t1_c1_c2 3
t1_c3 1
После создания некластерных индексов я выполнил 3 обновления c1, 2 обновления c2 и 1 обновление c3. Счётчик sysrscols.rcmodified по каждому из столбцов вырос на верное значение, но видно, что для столбцов, входящих в сами некластерные индексы, это не отслеживается. Кстати, последний «столбец» в каждом из некластерных индексов — это скрытый физический RID, указывающий обратно на соответствующую запись данных в куче.
Однако sysindexes.rowmodctr изменился неинтуитивно. Я выполнил 5 отдельных модификаций столбцов, входящих в индекс t1_c1_c2, а rowmodctr для него равен лишь 3. Причина в том, что алгоритм rowmodctr берёт приращение sysrscols.rcmodified по ведущему столбцу индекса с момента последней перестройки (или создания) статистики по столбцам индекса.
Докажу это: обновлю статистику, затем выполню 2 изменения c1, 4 изменения c2 и сделаю checkpoint. Мы должны увидеть, что sysrscols.rcmodified для c1 станет 6, для c2 — 9, а sysindexes.rowmodctr для t1_c1_c2 изменится на 2.
UPDATE STATISTICS t1;
GO
UPDATE t1 SET c1 = 7;
UPDATE t1 SET c1 = 8;
UPDATE t1 SET c2 = 4;
UPDATE t1 SET c2 = 5;
UPDATE t1 SET c2 = 6;
UPDATE t1 SET c2 = 7;
CHECKPOINT;
GO
object_id index_id scaled rcmodified
———– ———– ———– ——————–
277576027 0 1 6
277576027 0 2 9
277576027 0 3 2
277576027 2 1 0
277576027 2 2 0
277576027 2 3 0
277576027 3 1 0
277576027 3 2 0
name rowmodctr
————— ———–
NULL 9
t1_c1_c2 2
t1_c3 0
Именно так. Хотя c2 мы обновили 4 раза, sysindexes.rowmodctr для t1_c1_c2 равен лишь 2 — явно используется приращение sysrscols.rcmodified для c1.
Поиграйтесь с этими примерами, чтобы окончательно убедиться в текущем поведении.
Надеюсь, это помогло развеять часть путаницы. Увы, здесь много нюансов — когда именно и какие счётчики изменяются…
Комментариев нет:
Отправить комментарий