Как настроить Microsoft SQL Server для повышения производительности

Опубликовано: 2022-03-11

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

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

# 1 Поиск виновных

Как и в случае с любым другим программным обеспечением, нам необходимо понимать, что SQL Server — это сложная компьютерная программа. Если у нас есть проблема с ним, нам нужно выяснить, почему он не работает так, как мы ожидаем.

производительность sql-сервера

Из SQL Server нам нужно извлекать и отправлять данные как можно быстрее и точнее. Если есть проблемы, есть пара основных причин и первые две вещи, которые нужно проверить:

  • Параметры оборудования и установки, которые могут нуждаться в исправлении, поскольку потребности SQL Server специфичны.
  • Если мы предоставили правильный код T-SQL для реализации SQL Server

Несмотря на то, что SQL Server является проприетарным программным обеспечением, Microsoft предоставила множество способов понять и эффективно использовать его.

Если с оборудованием все в порядке и установка выполнена правильно, но SQL Server по-прежнему работает медленно, то сначала нам нужно выяснить, есть ли какие-либо ошибки, связанные с программным обеспечением. Чтобы проверить, что происходит, нам нужно наблюдать, как работают разные потоки. Это достигается путем вычисления статистики ожидания различных потоков. SQL-сервер использует потоки для каждого запроса пользователя, а поток — это не что иное, как другая программа внутри нашей сложной программы, называемой SQL Server. Важно отметить, что этот поток не является потоком операционной системы, на котором установлен SQL-сервер; это связано с потоком SQLOS, который является псевдооперационной системой для SQL Server.

Статистику ожидания можно рассчитать с помощью динамического административного представления (DMV) sys.dm_os_wait_stats , которое дает дополнительную информацию о его текущем состоянии. В Интернете есть много сценариев для запроса этого представления, но мой любимый сценарий Пола Рэндала, потому что он прост для понимания и имеет все важные параметры для наблюдения за статистикой ожидания:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Когда мы выполняем этот скрипт, нам нужно сосредоточиться на верхних строках результата, потому что они устанавливаются первыми и представляют максимальный тип ожидания.

Нам нужно понимать типы ожидания, чтобы мы могли принимать правильные решения. Чтобы узнать о различных типах ожидания, мы можем обратиться к отличной документации Microsoft.

Давайте возьмем пример, когда у нас слишком много PAGEIOLATCH_XX . Это означает, что поток ожидает чтения страницы данных с диска в буфер, который представляет собой не что иное, как блок памяти. Мы должны быть уверены, что понимаем, что происходит. Это не обязательно означает плохую подсистему ввода-вывода или нехватку памяти, и увеличение подсистемы ввода-вывода и памяти решит проблему, но только временно. Чтобы найти постоянное решение, нам нужно понять, почему с диска считывается так много данных: какие типы команд SQL вызывают это? Не читаем ли мы слишком много данных вместо того, чтобы читать меньше данных, используя фильтры, такие как операторы where ? Слишком много операций чтения данных происходит из-за сканирования таблиц или индексов? Можем ли мы преобразовать их в поиск по индексу, внедрив или изменив существующие индексы? Пишем ли мы SQL-запросы, которые неправильно понимает SQL Optimizer (другая программа внутри нашей программы SQL-сервера)?

Нам нужно думать с разных точек зрения и использовать разные тестовые примеры, чтобы найти решения. Для каждого из приведенных выше типов ожидания требуется свое решение. Администратор базы данных должен тщательно изучить их, прежде чем предпринимать какие-либо действия. Но в большинстве случаев поиск проблемных запросов T-SQL и их настройка решают от 60 до 70 процентов проблем.

# 2 Поиск проблемных запросов

Как упоминалось выше, первое, что мы можем сделать, это выполнить поиск проблемных запросов. Следующий код T-SQL найдет 20 худших запросов:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Нам нужно быть осторожными с результатами; даже несмотря на то, что запрос может иметь максимальное среднее время выполнения, если он выполняется только один раз, общее влияние на сервер будет низким по сравнению с запросом, который имеет среднее среднее время выполнения и выполняется много раз в день.

# 3 Запросы тонкой настройки

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

SQL Server использует расширенные алгоритмы поиска и сортировки. Если мы хорошо разбираемся в алгоритмах поиска и сортировки, то в большинстве случаев мы можем догадаться, почему SQL Server предпринимает определенные действия. Лучшая книга для изучения и понимания таких алгоритмов — «Искусство компьютерного программирования » Дональда Кнута.

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

Я не могу охватить здесь все аспекты плана выполнения, но на базовом уровне могу объяснить, что нам нужно учитывать.

  • Сначала нам нужно выяснить, какие операторы берут на себя большую часть стоимости запроса.
  • Если оператор берет на себя большие расходы, нам нужно выяснить, почему. В большинстве случаев сканирование требует больше затрат, чем поиск. Нам нужно выяснить, почему происходит конкретное сканирование (сканирование таблицы или индекса) вместо поиска по индексу. Мы можем решить эту проблему, реализуя правильные индексы для столбцов таблицы, но, как и в любой сложной программе, здесь нет фиксированного решения. Например, если таблица маленькая, сканирование выполняется быстрее, чем поиск.
  • Существует примерно 78 операторов, которые представляют различные действия и решения плана выполнения SQL Server. Нам необходимо подробно изучить их, ознакомившись с документацией Microsoft, чтобы мы могли лучше понять их и принять соответствующие меры.
Связанный: Объяснение индексов SQL, Pt. 1

# 4 Повторное использование плана выполнения

Даже если мы реализуем правильные индексы для таблиц и напишем хороший код T-SQL, если план выполнения не используется повторно, у нас будут проблемы с производительностью. После тонкой настройки запросов нам нужно убедиться, что план выполнения может быть повторно использован при необходимости. Большая часть процессорного времени будет потрачена на расчет плана выполнения, который можно исключить, если мы будем использовать план повторно.

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

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

Лучший способ повторно использовать план выполнения — реализовать параметризованные хранимые процедуры. Когда мы не в состоянии реализовать хранимые процедуры, мы можем использовать хранимую процедуру sp_executesql , которую можно использовать вместо выполнения операторов T-SQL, когда единственным изменением в операторах SQL являются значения параметров. SQL Server, скорее всего, повторно использует план выполнения, сгенерированный при первом выполнении.

Опять же, как и в любой сложной компьютерной программе, здесь нет фиксированного решения. Иногда лучше составить план заново.

Давайте рассмотрим следующие два примера запросов:

  • select name from table where name = 'sri';
  • select name from table where name = 'pal';

Предположим, что у нас есть некластеризованный индекс в столбце name , и половина таблицы имеет значение sri , а несколько строк имеют значение pal в столбце name . Для первого запроса SQL Server будет использовать сканирование таблицы, поскольку половина таблицы содержит одинаковые значения. Но для второго запроса лучше использовать сканирование индекса, потому что только несколько строк имеют значение pal .

Несмотря на то, что запросы похожи, один и тот же план выполнения не может быть хорошим решением. В большинстве случаев это будет другой случай, поэтому нам нужно тщательно все проанализировать, прежде чем принять решение. Если мы не хотим повторно использовать план выполнения, мы всегда можем использовать параметр «перекомпилировать» в хранимых процедурах.

Имейте в виду, что даже после использования хранимых процедур или sp_executesql бывают случаи, когда план выполнения не будет использоваться повторно. Они есть:

  • Когда индексы, используемые запросом, изменяются или удаляются
  • Когда статистика, структура или схема таблицы, используемой запросом, изменяются.
  • Когда мы используем опцию «перекомпилировать»
  • При большом количестве вставок, обновлений или удалений
  • Когда мы смешиваем DDL и DML в одном запросе

#5 Удаление ненужных индексов

После тонкой настройки запросов нам нужно проверить, как используются индексы. Обслуживание индекса требует много ресурсов ЦП и операций ввода-вывода. Каждый раз, когда мы вставляем данные в базу данных, SQL Server также нуждается в обновлении индексов, поэтому их лучше удалить, если они не используются.

производительность sql-сервера

SQL-сервер предоставляет нам DMV dm_db_index_usage_stats для поиска статистики индекса. Когда мы запускаем приведенный ниже код T-SQL, мы получаем статистику использования для разных индексов. Если мы обнаружим индексы, которые вообще не используются или используются редко, мы можем удалить их, чтобы повысить производительность.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 Установка SQL Server и настройка базы данных

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

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

# 7 Не перегружайте SQL Server

Основная задача любого администратора базы данных — убедиться, что производственный сервер работает бесперебойно и обслуживает клиентов как можно лучше. Чтобы это произошло, нам необходимо поддерживать отдельные базы данных (если возможно, на отдельных машинах) для следующих сред:

  • Производство
  • Разработка
  • Тестирование
  • аналитический

Для рабочей базы данных нам нужна база данных с полным режимом восстановления, а для других баз данных достаточно простого режима восстановления.

Тестирование рабочей базы данных сильно нагружает журнал транзакций, индексы, ЦП и ввод-вывод. Вот почему нам нужно использовать отдельные базы данных для производства, разработки, тестирования и анализа. Если возможно, используйте отдельные машины для каждой базы данных, потому что это снизит нагрузку на ЦП и ввод-вывод.

#8 Журнал транзакций, tempdb и память

В файле журнала должно быть достаточно свободного места для нормальной работы, поскольку операция автоматического увеличения файла журнала занимает много времени и может заставить другие операции ждать ее завершения. Чтобы узнать размер файла журнала для каждой базы данных и насколько он используется, мы можем использовать DBCC SQLPERF(logspace) .

Лучший способ настроить базу данных tempdb — поместить ее на отдельный диск. Нам нужно сохранить начальный размер настолько большим, насколько мы можем себе позволить, потому что, когда он достигнет ситуации автоматического увеличения, производительность снизится.

Как упоминалось ранее, нам нужно убедиться, что SQL-сервер работает на отдельной машине, желательно на машине без каких-либо других приложений. Нам нужно оставить немного памяти для операционной системы, а также еще немного, если она является частью кластера, поэтому в большинстве случаев достаточно около 2 ГБ.

Для критически важных сред миллисекундная задержка в получении информации может стать решающим фактором.
Твитнуть

Заключение:

Обсуждаемые здесь процедуры и предложения предназначены только для базовой настройки производительности. Если мы будем следовать этим шагам, мы можем в среднем повысить производительность на 40–50 процентов. Чтобы выполнить расширенную настройку производительности SQL Server, нам потребуется гораздо глубже изучить каждый из описанных здесь шагов.

Дальнейшее чтение в блоге Toptal Engineering:

  • Устранение узких мест с помощью индексов и разделов SQL
  • Руководство по переходу с Oracle на SQL Server и с SQL Server на Oracle