Устранение неполадок с устаревшей статистикой в SQL Server

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

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

Давайте обсудим, как SQL Server использует статистику для оптимизации планов выполнения запросов. Мы расскажем, как обновлять устаревшую статистику, и рассмотрим рекомендации по ведению статистики SQL Server.

Исправление устаревшей статистики в SQL Server

Итак, что такое статистика в SQL Server? Статистика в SQL Server – это двоичные большие объекты (BLOBS), хранящиеся в определенной внутренней структуре, называемой объектом статистики. Эти объекты содержат информацию о распределении значений в одном или нескольких столбцах таблицы или индексированного представления. Здесь распределение значений относится к шаблону значений.

При выполнении запроса SQL Server использует статистику для оценки количества строк, которые вернет запрос. Затем оптимизатор запросов SQL Server использует это число для определения наиболее эффективного плана выполнения для этого запроса. Обратите внимание, что статистика используется не для всех типов запросов. Например, статистика не используется для запросов, которые включают обычные табличные выражения (CTE), табличные переменные или динамические SQL-запросы.

Рассмотрим таблицу Orders со столбцом OrderTotal, который содержит себестоимость заказа. Объект статистики для столбца OrderTotal содержит информацию о диапазоне сумм заказа, количестве отдельных сумм заказа и частоте каждого количества. В этом примере, если заказов с небольшими суммами заказов больше, чем заказов с крупными закупками, распределение значений имеет тенденцию к уменьшению.

Эта информация полезна оптимизатору запросов SQL Server для определения наиболее эффективного способа выполнения запросов. Оптимизатор запросов SQL Server анализирует статистику, чтобы решить, как получить доступ к данным в таблице или индексированном представлении и обработать их. Такой подход значительно повышает производительность запроса.

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

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

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

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

Понимание того, как устаревшая статистика влияет на производительность запросов

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

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

Выявление устаревшей статистики SQL Server

Статистика устаревает, когда распределение значений столбцов изменяется после таких операций, как INSERT, UPDATE, и DELETE, изменяющих таблицу.

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

Доступ к статистике SQL Server

Статистика SQL Server хранится в базе данных. Вы можете использовать команду DBCC SHOW_STATISTICS для доступа к ним, используя следующий синтаксис:

DBCC SHOW_STATISTICS (table_name, index_name)

Например, чтобы просмотреть статистику таблицы Orders в образце базы данных wideworldimporterssdw, используйте следующую команду:

DBCC SHOW_STATISTICS ('WideWorldImporters.Sales.Orders', 'PK_Sales_Orders')

Эта команда возвращает статистику для индекса PK_Sales_Orders.

Вы также можете просмотреть статистику для определенной таблицы с помощью среды SQL Server Management Studio (SSMS). В проводнике объектов SSMS разверните папку Database, чтобы выбрать целевую базу данных. Затем разверните папку Tables и выберите нужную таблицу. Затем разверните папку Statistics, чтобы просмотреть статистику по различным индексам. Наконец, нажмите на нужную статистику для более подробного просмотра.

Теперь вы знаете, как просматривать статистику и определять устаревшие данные. В следующем разделе вы узнаете, как обновить эту статистику.

Обновление статистики в SQL Server

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

Вы можете выполнить команду UPDATE STATISTICS с помощью Transact-SQL для обновления определенного объекта статистики, всей статистики в таблице или всей статистики в базе данных.

Чтобы обновить конкретный объект статистики, укажите таблицу и целевой индекс. Например, приведенная ниже команда обновляет статистику для PK_Sales_Orders в таблице WideWorldImporters.Sales.Orders:

UPDATE STATISTICS WideWorldImporters.Sales.Orders PK_Sales_Orders

Укажите целевую таблицу, чтобы обновить всю статистику в таблице. Следующая команда обновляет всю статистику для таблицы WideWorldImporters.Sales.Orders:

UPDATE STATISTICS WideWorldImporters.Sales.Orders

Вы также можете указать количество строк, которые SQL Server должен сканировать во время обновления. Например, при использовании ПОЛНОГО СКАНИРОВАНИЯ сканируются все строки. Следующая команда сканирует все строки в таблице WideWorldImporters.Sales.Orders:

UPDATE STATISTICS WideWorldImporters.Sales.Orders WITH FULL SCAN

Помимо ПОЛНОГО СКАНИРОВАНИЯ, вы можете использовать SAMPLE для обновления статистики на основе процента. Команда WITH SAMPLE 50 ПРОЦЕНТОВ в следующей команде обновляет статистику на основе выборки строк на 50%.:

UPDATE STATISTICS WideWorldImporters.Sales.Orders PK_Sales_Orders WITH SAMPLE 50 PERCENT;

Другой вариант – ПОВТОРНАЯ ВЫБОРКА, которая обновляет статистику для определенного раздела таблицы. Например, параметр С ПОВТОРНОЙ ВЫБОРКОЙ ДЛЯ РАЗДЕЛОВ (1, 2, 3) обновляет статистику для разделов 1, 2 и 3.

Чтобы обновить всю статистику во всей базе данных, используйте хранимую процедуру sp_updatestats . Она обновляет все таблицы и индексы в указанной базе данных, даже если изменилась только одна строка. Например, выполнение следующей команды обновляет всю статистику в таблице WideWorldImporters:

USE WideWorldImporters; 
GO 
EXEC sp_updatestats;

Использование этой команды для обновления всей статистики таблиц и индексов может потребовать больших ресурсов. Используйте ее экономно или, что еще лучше, используйте команду UPDATE STATISTICS для обновления определенных таблиц.

Вы также можете использовать дополнительные обновления для пересмотра только статистики измененных разделов. Вы используете меньше ресурсов, поскольку серверу SQL не нужно сканировать все строки при обновлении статистики.

Рекомендации по поддержанию актуальной статистики

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

Используйте AUTO_UPDATES_STATISTICS

Включите параметр AUTO_UPDATE_STATISTICS для SQL Server для автоматического обновления статистики при изменении определенного количества строк. Этот оптимизатор запросов SQL Server выполняет параметр синхронно. Итак, когда вы запускаете запрос, а статистика таблицы устарела, SQL Server обновляет статистику перед выполнением запроса. Следовательно, производительность этого запроса снижается.

Чтобы решить эту проблему с производительностью, установите для параметра AUTO_UPDATE_STATISTICS_ASYNC значение ON. Таким образом, оптимизатор запросов SQL Server будет обновлять статистику асинхронно.

Регулярно обновляйте статистику

Не полагайтесь только на автоматическое обновление статистики SQL Server. Планируйте регулярные обновления статистики с помощью заданий агента SQL Server. Используйте команду UPDATE STATISTICS для обновления определенных таблиц или индексов или команду sp_updatestats для обновления всех таблиц.

Мониторинг показателей производительности

Отслеживайте показатели производительности запросов, такие как продолжительность запроса, загрузка ЦП и использование ввода-вывода (I/O), чтобы определить, устарела ли статистика. Если вы заметили снижение производительности, обновите статистику для затронутых таблиц или индексов.

Заключение

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

Информация жизненно важна для обеспечения оптимальной работы вашего SQL Server.

(1 оценок, среднее: 5,00 из 5)

Загрузка...