SQL-Ex blog

• Устаревшая статистика


• Прослушивание параметра (parameter sniffing)


• Настройки tempdb


• Недостаточная производительность ввода/вывода диска


• Глобально примененные флаги трассировки


• Установка базы данных или сервера БД


• Фрагментированные индексы


• Изменение установок сессии клиентского приложения


• Изменения схемы таблиц запроса


• Недостаточно памяти и ресурсов ЦП


• Установки виртуальной машины


• Проблемы блокировок и тупика


• Баги версии SQL Server

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

Устаревшая статистика

Статистика является одним из главных факторов, которые могут повлиять на производительность запросов, поскольку она хранит аналитическую информацию о распределении значений в столбце таблицы. Эти статистические данные используются оптимизатором для оценки числа строк, которые будут возвращаться при выполнении запроса. На основе этой информации оптимизатор вычисляет потребность в ресурсах (ЦП, память и т.п.) для запроса. В некоторых случаях статистика может устареть из-за множественной модификации данных, даже если включено автообновление статистики. Неточная статистическая информация может привести к некорректным оценкам, в результате чего оптимизатор может построить неэффективный план запроса. При заметной разнице между предварительным и актуальным числом строк в актуальном плане выполнения запроса вы можете догадаться, что статистика устарела. Давайте теперь проведем небольшой тест. Сначала мы выполним следующий запрос, чтобы создать тестовую таблицу и заполнить ее некоторыми данными в базе данных Adventureworks.

DROP TABLE IF EXISTS Test_Table

  GO

  CREATE TABLE Test_Table(

    Id int PRIMARY KEY IDENTITY(1,1) NOT NULL,

    UnitPrice money NULL,

    CarrierTrackingNumber varchar(100) NULL)

  GO

  INSERT INTO Test_Table (UnitPrice,CarrierTrackingNumber)

  SELECT TOP 2 UnitPrice,CarrierTrackingNumber FROM Sales.SalesOrderDetail

  GO

  SELECT  UnitPrice,CarrierTrackingNumber FROM Test_Table

  WHERE UnitPrice BETWEEN 5 and 2020

  GO

  UPDATE STATISTICS Test_Table

      WITH FULLSCAN, NORECOMPUTE;

    GO

  INSERT INTO Test_Table (UnitPrice,CarrierTrackingNumber)

  SELECT   UnitPrice,CarrierTrackingNumber FROM Sales.SalesOrderDetail

  GO

  DBCC FREEPROCCACHE

  GO 

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

SELECT  UnitPrice,CarrierTrackingNumber FROM Test_Table

  WHERE UnitPrice BETWEEN 5 AND 2020

  ORDER BY CarrierTrackingNumber DESC

Если навести мышку на стрелку между оператором index scan и sort, во всплывающем окне появятся действительное и предварительное число строк. В нашем примере имеет место значительная разница между фактическим и предварительным числом строк, и эта разница приводит к тому, что оптимизатор делает неверную оценку. В результате этой неверной оценки для запроса выделяется меньше памяти, чем требуется фактически для выполнения операции сортировки, поэтому sort завершается сбросом на диск. Соответственно, этот пример показывает, как устаревшая статистика отрицательно влияет на производительности запроса, а также, как промах в оценке числа строк искажает расчет оптимизатора оценочной стоимости поддерева (Estimated Subtree Cost). После обновления статистики Estimated Subtree Cost изменится, а предварительное и действительное число строк окажутся очень близкими.

UPDATE STATISTICS Test_Table

  WITH FULLSCAN, NORECOMPUTE;

    GO

    DBCC FREEPROCCACHE

GO

    SELECT  UnitPrice,CarrierTrackingNumber FROM Test_Table

WHERE UnitPrice between 5 and 2020

ORDER BY CarrierTrackingNumber DESC

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

SELECT DISTINCT 

  OBJECT_NAME(stasts.[object_id]) AS TableName, 

  c.name AS ColumnName, 

  stasts.name AS StatName, 

  STATS_DATE(stasts.[object_id], stasts.stats_id) AS LastUpdateDate, 

  dsp.modification_counter, 

  stasts.auto_created, 

  stasts.user_created, 

  stasts.no_recompute, 

  stasts.[object_id], 

  stasts.stats_id, 

  statscol.stats_column_id, 

  statscol.column_id

FROM sys.stats stasts

JOIN sys.stats_columns statscol ON statscol.[object_id] = stasts.[object_id] AND 

                                   statscol.stats_id = stasts.stats_id

JOIN sys.columns c ON c.[object_id] = statscol.[object_id] AND 

                      c.column_id = statscol.column_id

JOIN sys.partitions partion ON partion.[object_id] = stasts.[object_id]

JOIN sys.objects objectlist ON partion.[object_id] = objectlist.[object_id]

CROSS APPLY sys.dm_db_stats_properties(statscol.[object_id], stasts.stats_id) AS dsp

WHERE OBJECTPROPERTY(stasts.OBJECT_ID, 'IsUserTable') = 1 AND 

 (stasts.auto_created = 1 OR 

  stasts.user_created = 1)

ORDER BY modification_counter DESC, 

    STATS_DATE(stasts.[object_id], stasts.stats_id) ASC

Прослушивание параметра

Хранимые процедуры широко используются разработчиками и администраторами баз данных, поскольку обладают следующими двумя преимуществами:

• Позволяет инкапсулировать скрипты T-SQL, обеспечивая их повторное использование.


• Улучшает производительность запроса за счет использования кэшированных планов выполнения для различных параметров.

При выполнении хранимой процедуры с параметром или параметрами оптимизатор генерирует оптимальный план выполнения в соответствии с переданными параметрами и закэширует этот план для последующих выполнений хранимой процедуры. Однако parameter sniffing вызывает проблему, когда кэшированный план выполнения не показывает эффективной производительности для заданных входных параметров при каждом выполнении процедуры. Главным симптомом этого явления служит то, что производительность запроса оказывается очень плохой в приложении, в то время как при выполнении той же самой процедуры в SQL Server Management Studio (SSMS) она вполне удовлетворительна.

Мы можем предложить следующие действия для исправления проблем, вызванных прослушиванием параметра:

• Использование хинта запроса OPTIMIZE FOR


• Использование хинта WITH RECOMPILE


• Использование хинта OPTION (RECOMPILE)


• Преобразование хранимой процедуры на использование динамического SQL


• Выключить опцию Parameter Sniffing на уровне баз данных


• Использование локальных переменных в хранимой процедуре

Tempdb

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

• Создание нескольких файлов данных tempdb в соответствии с ядром ЦП


• Использовать быстрый выделенный диск для файлов базы данных tempdb


• Рассмотреть использование флагов трассировки 1117 и 1118 для более ранней версии SQL Server 2016


• Отслеживать рост tempdb и проблемы конкуренции за ресурсы


• Рассмотреть включение функции Memory-Optimized TempDB Metadata (оптимизированные для памяти метаданные tempdb) для версий SQL Server 2019

Недостаточная производительность дискового ввода/вывода

Производительность подсистемы ввода/вывода непосредственно влияет на производительность запросов, поскольку SQL Server, принимая запрос на выполнение, считывает данные из системы хранения, если данных нет в буферном пуле. В то же время работа с файлам журнала транзакций, tempdb и резервированием могут создавать значительный трафик ввода/вывода с подсистемами хранения. Одним из главных симптомов плохой производительности ввода/вывода является мониторинг типов ожиданий PAGEIOLATCH, WRITELOG и ASYNC_IO_COMPLETION в SQL Server. Тут мы можем начать исследование для измерения Avg Disk Sec/Transfer.

Avg Disk Sec/Transfer показывает время задержки при выполнении операций записи и чтения. Если это значение оказывается более 25 мс, мы можем подозревать производительность ввода/вывода нашей дисковой подсистемы.



Diskspd - еще один инструмент, который может помочь идентифицировать проблемы ввода/вывода. С помощью Diskspd вы можем протестировать производительные способности нашей дисковой системы.

Наконец, для мониторинга проблем ввода/вывода SQL Server мы можем использовать представление sys.dm_io_virtual_file_stats. С помощью этого представления мы можем получить информацию о задержках для всех файлов базы данных. Используя следующий запрос, мы можем вернуть средние задержки чтения и записи для файлов базы данных.

SELECT  DB_NAME(vfs.database_id) AS database_name ,physical_name AS [Physical Name],

  size_on_disk_bytes / 1024 / 1024. AS [Size of Disk] ,

  CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [Average Read latency] ,

  CAST(io_stall_write_ms/(1.0 + num_of_writes) AS NUMERIC(10,1)) AS [Average Write latency] ,

  CAST((io_stall_read_ms + io_stall_write_ms)

/(1.0 + num_of_reads + num_of_writes) 

AS NUMERIC(10,1)) AS [Average Total Latency],

  num_of_bytes_read / NULLIF(num_of_reads, 0) AS    [Average Bytes Per Read],

  num_of_bytes_written / NULLIF(num_of_writes, 0) AS   [Average Bytes Per Write]

FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

JOIN sys.master_files AS mf 

ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

ORDER BY [Average Total Latency] DESC

Глобально применяемые флаги трассировк

Флаги трассировки используются для изменения конкретного поведения SQL Server и могут применяться глобально, сессионно и на уровне запроса.

Совет: Некоторые флаги трассировки могут быть не документированны Microsoft, поэтому перед использованием этих флагов трассировки необходимо рассмотреть всесторонне их влияние на рабочую нагрузку сервера.

С точки зрения производительности запросов некоторые флаги трассировки меняют принятые по умолчанию характеристики оптимизатора запросов, и эта ситуация может повлиять на производительность запросов как негативно, так и позитивно. Например, флаг трассировки 253 не дает планам ad-hoc запросов оставаться в кэше, поэтому каждое выполнение ad-hoc запроса будет снова проходить стадию компиляции. В следующем запросе мы включим этот флаг трассировки глобально и выполним запрос.

DBCC TRACEON(253,-1)

  GO

  SELECT TOP 100 UnitPrice,CarrierTrackingNumber FROM Test_Table

  WHERE UnitPrice between 5 and 2020

  ORDER BY CarrierTrackingNumber DESC

  GO

  DBCC TRACEOFF(253,-1)

При анализе плана выполнения запроса атрибут "флаг трассировки" показывает на каком уровне он применялся. В то же время подчненный атрибут IsTheCompileTime имеет значение true, которое означает, что флаг трассировки включен во время компиляции запроса.



Этот ad-hoc запрос не будет сохранен в кэше планов запросов, и он перекомпилируется при каждом своем выполнении.

Заключение

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