В данном топике хотелось бы поговорить о повышении производительности при работе с таблицами.
Тема не нова, но становится особенно актуальной, когда в базе наблюдается постоянный рост данных – таблицы становятся большими, а поиск и выборка по ним – медленной.
Как правило, это происходит из-за плохо спроектированной схемы – изначально не рассчитанной на оперирование большими объемами данных.
Чтобы рост данных в таблицах не приводил к падению производительности при работе с ними, рекомендуется взять на вооружение несколько правил при проектировании схемы.
Первое и, наверное, самое важное. Типы данных в таблицах должны иметь минимальную избыточность.
Все данные, которыми оперирует SQL Server, хранятся на, так называемых, страницах, которые имеют фиксированный размер в 8 Кб. При записи и чтении сервер оперирует именно страницами, а не отдельными строками.
Поэтому, чем более компактные типы данных используются в таблице, тем меньше страниц требуется для их хранения. Меньшее число страниц – меньшее количество дисковых операций.
Кроме очевидного снижения нагрузки на дисковую подсистему – в данном случае есть еще одно преимущество — при чтении с диска, любая страница вначале помещается в специальную область памяти (Buffer Pool), а потом уже используется по прямому назначению – для считывания или изменения данных.
При использовании компактных типов данных, в Buffer Pool можно поместить больше данных на том же количестве страниц – за счет этого мы не тратим впустую оперативную память и сокращаем количество логических операций.
Теперь рассмотрим небольшой пример – таблицу, в которой хранится информация о рабочих днях каждого сотрудника.
Правильно ли выбраны типы данных в этой таблице? По-видимому – нет.
Например, очень сомнительно что сотрудников на предприятии насколько много (2^63-1), что для покрытия такой ситуации был выбран тип данных BIGINT.
Уберем избыточность и посмотрим, будет ли запрос из такой таблицы более быстрым?
На плане выполнения можно увидеть разницу в стоимости, которое зависит от среднего размера строки и ожидаемого количество строк, которое вернет запрос:
Весьма логично, что чем меньший объем данных требуется прочитать – тем быстрее будет выполняться сам запрос:
(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1919 ms, elapsed time = 33606 ms.
(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1420 ms, elapsed time = 29694 ms.
Как Вы видите, использование менее избыточных типов данных зачастую положительно сказывается на производительности запросов и позволяет существенно снизить размер проблемных таблиц.
К слову, узнать размер таблицы можно посредством следующего запроса:
Для рассматриваемых таблиц, запрос вернет следующие результаты:
Правило второе. Избегайте дублирования и применяйте нормализацию данных.
Собственно, недавно я анализировал базу данных одного бесплатного веб-сервиса для форматирования T-SQL кода. Серверная часть там очень простая и состояла из одной единственной таблицы:
Каждый раз при форматировании сохранялся id текущей сессии, системное время сервера и настройки, с которыми пользователь отформатировал свой SQL код. Затем полученные данные использовались для выявления наиболее популярных стилей форматирования.
С ростом популярности сервиса, количество строк в таблице увеличилось, а обработка профилей форматирования занимала все большее количество времени. Причина заключалась в архитектуре сервиса – при каждой вставке в таблицу сохранялся полный набор настроек.
Настройки имели следующую XML структуру:
Всего 450 опций форматирования – каждая такая строка в таблице занимала примерно 33Кб. А ежедневный прирост данных составлял более 100Мб. С каждым днем база разрасталась, а делать аналитику по ней становилось делать дольше.
Исправить ситуацию оказалось просто – все уникальные профили были вынесены в отдельную таблицу, где для каждого набора опций был получен хеш. Начиная с SQL Server 2008 для этого можно использовать функцию sys.fn_repl_hash_binary.
В результате схема была нормализирована:
И если запрос на вычитку раньше был таким:
То на получение тех же данных в новой схеме потребовалось сделать JOIN:
Если сравнить время выполнения запросов, то мы не увидим явного преимущества от изменения схемы.
(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 4698 ms.
(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 4479 ms.
Но цель в данном случае преследовалась другая – ускорить аналитику. И если раньше приходилось писать очень мудреный запрос для получения списка самых популярных профилей форматирования:
То за счет нормализации данных стало возможным существенно упростить не только сам запрос:
Но и сократить время его выполнения:
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 2684 ms, elapsed time = 2774 ms.
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 379 ms.
Приятным дополнением также стало и снижение размера база данных на диске:
Вернуть размер файла данных для базы можно следующим запросом:
Надеюсь, на этом примере, мне удалось показать важность нормализации данных и минимизации избыточности в базе.
Третье. Осторожно выбирайте столбцы, входящие в индексы.
Индексы позволяют существенно ускорить выборку из таблицы. Также как и данные из таблиц, индексы хранятся на страницах. Соотвественно. чем меньше страниц требуется для хранения индекса – тем быстрее по нему можно провести поиск.
Очень важно правильно выбрать поля, которые будут входить в кластерный индекс. Поскольку все столбцы кластерного индекса автоматически входят в каждый некластерный (по указателю).
Четвертое. Используйте промежуточные и консолидированные таблицы.
Здесь все достаточно просто – зачем каждый раз делать сложный запрос из большой таблицы, если есть возможность сделать простой запрос из маленькой.
Например, в наличии есть запрос по консолидации данных:
Если данные в таблице изменяются не слишком часто, можно создать отдельную таблицу:
И не удивительно, что чтение из консолидированной таблицы будет проходить быстрее:
(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 3448 ms, elapsed time = 3116 ms.
(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 1410 ms, elapsed time = 1202 ms.
Пятое. В каждом правиле есть свои исключения.
Я показал пару примеров, когда изменение типов данных на менее избытоные позволяет сократить время выполнения запроса. Но это бывает не всегда.
Например, у типа данных BIT есть одна особенность – SQL Server оптимизирует хранение группы столбцов этакого типа на диске. Например, если в таблице имеется 8 или меньше столбцов типа BIT, они хранятся на странице как 1 байт, если до 16 столбцов типа BIT, они хранятся как 2 байта и т.д.
Хорошая новость – таблица будет занимать существенно меньше места и сократит количество дисковых операций.
Плохая новость – при выборке данных этого типа будет происходить неявное декодирование, которое очень требовательно к ресурсам процессора.
Покажу это на примере. Есть три идентичные таблицы, которые содержат информацию о календарном графике сотрудников (31 + 2 PK столбца). Все они отличаются только типом данных для консолидированных значений (1 — вышел на работу, 0 — отсутствовал):
При использовании менее избыточных данных размер таблицы заметно уменьшился (особенно последняя таблица):
Но существенного выигрыша в скорости выполнения от использования типа BIT мы не получим:
Хотя план выполнения будет говорить об обратном:
В результате наблюдений было замечено, что негативный эффект от декодирования не будет проявятся если таблица содержит не более 8 BIT столбцов.
Попутно стоит отметить, что в метаданных SQL Server тип данных BIT используется очень редко – чаще применяют тип BINARY и вручную делают сдвиг для получения того или иного значения.
И последнее о чем нужно упомянуть. Удаляйте ненужные данные.
Собственно, зачем это делать?
При выборке данных, SQL Server поддерживает механизм оптимизации производительности, называемый упреждающим чтением, который пытается предугадать, какие именно страницы данных и индексов понадобятся для выполнения запроса, и помещает эти страницы в буферный кэш, прежде чем в них возникнет реальная необходимость.
Соответственно, если таблица содержит много лишних данных – это может привести к ненужным дисковым операциям.
Кроме того, удаление ненужных данных позволяет сократить количество логических операций при чтении данных из Buffer Pool – поиск и выборка данных будет проходить по меньшему объёму данных.
В заключение, что могу еще добавить – внимательно выбирайте типы данных для столбцов в Ваших таблицах и старайтесь учитывать будущие нагрузки на базу данных.
Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:
Practical Tips to Reduce SQL Server Database Table Size
Тема не нова, но становится особенно актуальной, когда в базе наблюдается постоянный рост данных – таблицы становятся большими, а поиск и выборка по ним – медленной.
Как правило, это происходит из-за плохо спроектированной схемы – изначально не рассчитанной на оперирование большими объемами данных.
Чтобы рост данных в таблицах не приводил к падению производительности при работе с ними, рекомендуется взять на вооружение несколько правил при проектировании схемы.
Первое и, наверное, самое важное. Типы данных в таблицах должны иметь минимальную избыточность.
Все данные, которыми оперирует SQL Server, хранятся на, так называемых, страницах, которые имеют фиксированный размер в 8 Кб. При записи и чтении сервер оперирует именно страницами, а не отдельными строками.
Поэтому, чем более компактные типы данных используются в таблице, тем меньше страниц требуется для их хранения. Меньшее число страниц – меньшее количество дисковых операций.
Кроме очевидного снижения нагрузки на дисковую подсистему – в данном случае есть еще одно преимущество — при чтении с диска, любая страница вначале помещается в специальную область памяти (Buffer Pool), а потом уже используется по прямому назначению – для считывания или изменения данных.
При использовании компактных типов данных, в Buffer Pool можно поместить больше данных на том же количестве страниц – за счет этого мы не тратим впустую оперативную память и сокращаем количество логических операций.
Теперь рассмотрим небольшой пример – таблицу, в которой хранится информация о рабочих днях каждого сотрудника.
CREATE TABLE dbo.WorkOut1 (
DateOut DATETIME
, EmployeeID BIGINT
, WorkShiftCD NVARCHAR(10)
, WorkHours DECIMAL(24,2)
, CONSTRAINT PK_WorkOut1 PRIMARY KEY (DateOut, EmployeeID)
)
Правильно ли выбраны типы данных в этой таблице? По-видимому – нет.
Например, очень сомнительно что сотрудников на предприятии насколько много (2^63-1), что для покрытия такой ситуации был выбран тип данных BIGINT.
Уберем избыточность и посмотрим, будет ли запрос из такой таблицы более быстрым?
CREATE TABLE dbo.WorkOut2 (
DateOut SMALLDATETIME
, EmployeeID INT
, WorkShiftCD VARCHAR(10)
, WorkHours DECIMAL(8,2)
, CONSTRAINT PK_WorkOut2 PRIMARY KEY (DateOut, EmployeeID)
)
На плане выполнения можно увидеть разницу в стоимости, которое зависит от среднего размера строки и ожидаемого количество строк, которое вернет запрос:
Весьма логично, что чем меньший объем данных требуется прочитать – тем быстрее будет выполняться сам запрос:
(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1919 ms, elapsed time = 33606 ms.
(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1420 ms, elapsed time = 29694 ms.
Как Вы видите, использование менее избыточных типов данных зачастую положительно сказывается на производительности запросов и позволяет существенно снизить размер проблемных таблиц.
К слову, узнать размер таблицы можно посредством следующего запроса:
SELECT
table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4))
FROM sys.objects o
JOIN (
SELECT
p.[object_id]
, total_rows = SUM(p.[rows])
, total_pages = SUM(a.total_pages)
, usedpages = SUM(a.used_pages)
, pages = SUM(
CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0
WHEN a.[type] != 1 AND p.index_id < 2 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages ELSE 0
END
)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]
GROUP BY p.[object_id]
) do ON o.[object_id] = do.[object_id]
WHERE o.[type] = 'U'
Для рассматриваемых таблиц, запрос вернет следующие результаты:
table_name data_size_mb
-------------------- -------------------------------
dbo.WorkOut1 167.2578
dbo.WorkOut2 97.1250
Правило второе. Избегайте дублирования и применяйте нормализацию данных.
Собственно, недавно я анализировал базу данных одного бесплатного веб-сервиса для форматирования T-SQL кода. Серверная часть там очень простая и состояла из одной единственной таблицы:
CREATE TABLE dbo.format_history (
session_id BIGINT
, format_date DATETIME
, format_options XML
)
Каждый раз при форматировании сохранялся id текущей сессии, системное время сервера и настройки, с которыми пользователь отформатировал свой SQL код. Затем полученные данные использовались для выявления наиболее популярных стилей форматирования.
С ростом популярности сервиса, количество строк в таблице увеличилось, а обработка профилей форматирования занимала все большее количество времени. Причина заключалась в архитектуре сервиса – при каждой вставке в таблицу сохранялся полный набор настроек.
Настройки имели следующую XML структуру:
<FormatProfile>
<FormatOptions>
<PropertyValue Name="Select_SelectList_IndentColumnList">true</PropertyValue>
<PropertyValue Name="Select_SelectList_SingleLineColumns">false</PropertyValue>
<PropertyValue Name="Select_SelectList_StackColumns">true</PropertyValue>
<PropertyValue Name="Select_SelectList_StackColumnsMode">1</PropertyValue>
<PropertyValue Name="Select_Into_LineBreakBeforeInto">true</PropertyValue>
...
<PropertyValue Name="UnionExceptIntersect_LineBreakBeforeUnion">true</PropertyValue>
<PropertyValue Name="UnionExceptIntersect_LineBreakAfterUnion">true</PropertyValue>
<PropertyValue Name="UnionExceptIntersect_IndentKeyword">true</PropertyValue>
<PropertyValue Name="UnionExceptIntersect_IndentSubquery">false</PropertyValue>
...
</FormatOptions>
</FormatProfile>
Всего 450 опций форматирования – каждая такая строка в таблице занимала примерно 33Кб. А ежедневный прирост данных составлял более 100Мб. С каждым днем база разрасталась, а делать аналитику по ней становилось делать дольше.
Исправить ситуацию оказалось просто – все уникальные профили были вынесены в отдельную таблицу, где для каждого набора опций был получен хеш. Начиная с SQL Server 2008 для этого можно использовать функцию sys.fn_repl_hash_binary.
В результате схема была нормализирована:
CREATE TABLE dbo.format_profile (
format_hash BINARY(16) PRIMARY KEY
, format_profile XML NOT NULL
)
CREATE TABLE dbo.format_history (
session_id BIGINT
, format_date SMALLDATETIME
, format_hash BINARY(16) NOT NULL
, CONSTRAINT PK_format_history PRIMARY KEY CLUSTERED (session_id, format_date)
)
И если запрос на вычитку раньше был таким:
SELECT fh.session_id, fh.format_date, fh.format_options
FROM SQLF.dbo.format_history fh
То на получение тех же данных в новой схеме потребовалось сделать JOIN:
SELECT fh.session_id, fh.format_date, fp.format_profile
FROM SQLF_v2.dbo.format_history fh
JOIN SQLF_v2.dbo.format_profile fp ON fh.format_hash = fp.format_hash
Если сравнить время выполнения запросов, то мы не увидим явного преимущества от изменения схемы.
(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 4698 ms.
(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 4479 ms.
Но цель в данном случае преследовалась другая – ускорить аналитику. И если раньше приходилось писать очень мудреный запрос для получения списка самых популярных профилей форматирования:
;WITH cte AS (
SELECT
fh.format_options
, hsh = sys.fn_repl_hash_binary(CAST(fh.format_options AS VARBINARY(MAX)))
, rn = ROW_NUMBER() OVER (ORDER BY 1/0)
FROM SQLF.dbo.format_history fh
)
SELECT c2.format_options, c1.cnt
FROM (
SELECT TOP (10) hsh, rn = MIN(rn), cnt = COUNT(1)
FROM cte
GROUP BY hsh
ORDER BY cnt DESC
) c1
JOIN cte c2 ON c1.rn = c2.rn
ORDER BY c1.cnt DESC
То за счет нормализации данных стало возможным существенно упростить не только сам запрос:
SELECT
fp.format_profile
, t.cnt
FROM (
SELECT TOP (10)
fh.format_hash
, cnt = COUNT(1)
FROM SQLF_v2.dbo.format_history fh
GROUP BY fh.format_hash
ORDER BY cnt DESC
) t
JOIN SQLF_v2.dbo.format_profile fp ON t.format_hash = fp.format_hash
Но и сократить время его выполнения:
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 2684 ms, elapsed time = 2774 ms.
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 379 ms.
Приятным дополнением также стало и снижение размера база данных на диске:
database_name row_size_mb
---------------- ---------------
SQLF 123.50
SQLF_v2 7.88
Вернуть размер файла данных для базы можно следующим запросом:
SELECT
database_name = DB_NAME(database_id)
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files
WHERE database_id IN (DB_ID('SQLF'), DB_ID('SQLF_v2'))
GROUP BY database_id
Надеюсь, на этом примере, мне удалось показать важность нормализации данных и минимизации избыточности в базе.
Третье. Осторожно выбирайте столбцы, входящие в индексы.
Индексы позволяют существенно ускорить выборку из таблицы. Также как и данные из таблиц, индексы хранятся на страницах. Соотвественно. чем меньше страниц требуется для хранения индекса – тем быстрее по нему можно провести поиск.
Очень важно правильно выбрать поля, которые будут входить в кластерный индекс. Поскольку все столбцы кластерного индекса автоматически входят в каждый некластерный (по указателю).
Четвертое. Используйте промежуточные и консолидированные таблицы.
Здесь все достаточно просто – зачем каждый раз делать сложный запрос из большой таблицы, если есть возможность сделать простой запрос из маленькой.
Например, в наличии есть запрос по консолидации данных:
SELECT
WorkOutID
, CE = SUM(CASE WHEN WorkKeyCD = 'CE' THEN Value END)
, DE = SUM(CASE WHEN WorkKeyCD = 'DE' THEN Value END)
, RE = SUM(CASE WHEN WorkKeyCD = 'RE' THEN Value END)
, FD = SUM(CASE WHEN WorkKeyCD = 'FD' THEN Value END)
, TR = SUM(CASE WHEN WorkKeyCD = 'TR' THEN Value END)
, FF = SUM(CASE WHEN WorkKeyCD = 'FF' THEN Value END)
, PF = SUM(CASE WHEN WorkKeyCD = 'PF' THEN Value END)
, QW = SUM(CASE WHEN WorkKeyCD = 'QW' THEN Value END)
, FH = SUM(CASE WHEN WorkKeyCD = 'FH' THEN Value END)
, UH = SUM(CASE WHEN WorkKeyCD = 'UH' THEN Value END)
, NU = SUM(CASE WHEN WorkKeyCD = 'NU' THEN Value END)
, CS = SUM(CASE WHEN WorkKeyCD = 'CS' THEN Value END)
FROM dbo.WorkOutFactor
WHERE Value > 0
GROUP BY WorkOutID
Если данные в таблице изменяются не слишком часто, можно создать отдельную таблицу:
SELECT *
FROM dbo.WorkOutFactorCache
И не удивительно, что чтение из консолидированной таблицы будет проходить быстрее:
(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 3448 ms, elapsed time = 3116 ms.
(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 1410 ms, elapsed time = 1202 ms.
Пятое. В каждом правиле есть свои исключения.
Я показал пару примеров, когда изменение типов данных на менее избытоные позволяет сократить время выполнения запроса. Но это бывает не всегда.
Например, у типа данных BIT есть одна особенность – SQL Server оптимизирует хранение группы столбцов этакого типа на диске. Например, если в таблице имеется 8 или меньше столбцов типа BIT, они хранятся на странице как 1 байт, если до 16 столбцов типа BIT, они хранятся как 2 байта и т.д.
Хорошая новость – таблица будет занимать существенно меньше места и сократит количество дисковых операций.
Плохая новость – при выборке данных этого типа будет происходить неявное декодирование, которое очень требовательно к ресурсам процессора.
Покажу это на примере. Есть три идентичные таблицы, которые содержат информацию о календарном графике сотрудников (31 + 2 PK столбца). Все они отличаются только типом данных для консолидированных значений (1 — вышел на работу, 0 — отсутствовал):
SELECT * FROM dbo.E_51_INT
SELECT * FROM dbo.E_52_TINYINT
SELECT * FROM dbo.E_53_BIT
При использовании менее избыточных данных размер таблицы заметно уменьшился (особенно последняя таблица):
table_name data_size_mb
-------------------- --------------
dbo.E31_INT 150.2578
dbo.E32_TINYINT 50.4141
dbo.E33_BIT 24.1953
Но существенного выигрыша в скорости выполнения от использования типа BIT мы не получим:
(1000000 row(s) affected)
Table 'E31_INT'. Scan count 1, logical reads 19296, physical reads 1, read-ahead reads 19260, ...
SQL Server Execution Times:
CPU time = 1607 ms, elapsed time = 19962 ms.
(1000000 row(s) affected)
Table 'E32_TINYINT'. Scan count 1, logical reads 6471, physical reads 1, read-ahead reads 6477, ...
SQL Server Execution Times:
CPU time = 1029 ms, elapsed time = 16533 ms.
(1000000 row(s) affected)
Table 'E33_BIT'. Scan count 1, logical reads 3109, physical reads 1, read-ahead reads 3096, ...
SQL Server Execution Times:
CPU time = 1820 ms, elapsed time = 17121 ms.
Хотя план выполнения будет говорить об обратном:
В результате наблюдений было замечено, что негативный эффект от декодирования не будет проявятся если таблица содержит не более 8 BIT столбцов.
Попутно стоит отметить, что в метаданных SQL Server тип данных BIT используется очень редко – чаще применяют тип BINARY и вручную делают сдвиг для получения того или иного значения.
И последнее о чем нужно упомянуть. Удаляйте ненужные данные.
Собственно, зачем это делать?
При выборке данных, SQL Server поддерживает механизм оптимизации производительности, называемый упреждающим чтением, который пытается предугадать, какие именно страницы данных и индексов понадобятся для выполнения запроса, и помещает эти страницы в буферный кэш, прежде чем в них возникнет реальная необходимость.
Соответственно, если таблица содержит много лишних данных – это может привести к ненужным дисковым операциям.
Кроме того, удаление ненужных данных позволяет сократить количество логических операций при чтении данных из Buffer Pool – поиск и выборка данных будет проходить по меньшему объёму данных.
В заключение, что могу еще добавить – внимательно выбирайте типы данных для столбцов в Ваших таблицах и старайтесь учитывать будущие нагрузки на базу данных.
Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:
Practical Tips to Reduce SQL Server Database Table Size