PDF
Периодически возникает задача загрузить какую-то порцию данных в уже имеющуюся таблицу, чтобы потом, например, построить по ним отчет или выполнить еще какие-то действия.
Иногда, после загрузки большой (относительно количества уже имеющихся данных в таблице) порции данных, запрос, который используется для построения отчета, может начать «тормозить» при первом выполнении. И хотя потом все начинает работать так же быстро, как и до загрузки, хотелось бы избавиться от этого первоначального «торможения».
Причина в первоначальном торможении заключается в том, что при изменении большого количества данных статистика и план выполнения становятся неактуальными, поэтому оптимизатор вынужден автоматически обновлять статистику (если конечно у вас включена соответствующая опция) и пытаться найти новый план выполнения.
Этого (пересчета при запросе) можно избежать, если после операции загрузки вручную обновить статистику. Как правило, пользователя легче убедить в том, почему он должен ждать когда данные загружаются, чем в том почему он должен ждать когда отчет строится по готовым данным.
При обновлении статистики, может возникнуть соблазн уменьшить время обновления, обновляя только необходимую статистику. Например, если в запросе используется только один индекс, то на первый взгляд было бы логично обновить статистику только по этому индексу, экономя ресурсы сервера. Но на самом деле не все так очевидно. Посмотрим, что делает оптимизатор на примере.
Пример:
Допустим, у нас есть сеть ресторанов различных типов (суши бар, пиццерия, китайский), общий ассортимент блюд около 500 наименований. Пусть наша сеть присутствует условно в 100 городах, и имеет около 5 различных ресторанов в каждом городе. Каждый месяц, в главном офисе, мы получаем информацию о том сколько и каких блюд было продано в каждом из ресторанов.
Создадим таблицу с данными, в которой будем хранить поступающую информацию и процедуру, которая заполняет эту таблицу условно случайным образом.
Загрузим данные
exec dbo.RestaurantSales_load go
Допустим, мы хотим посмотреть по городам, какие виды пиццы больше всего востребованы в пиццериях. Для этого нам нужны все данные собранные по блюдам, в названии которых присутствует слово «pizza» от ресторанов типа «пиццерия», агрегированные по городу и названию блюда. Напишем и выполним его в SSMS, включив опцию «Include Actual Execution Plan».
declare @LoadDate datetime select @LoadDate = max(LoadDate) from dbo.RestaurantSales select rs.CityID, rs.DishName, SaledInCity = sum(rs.SaledCount) from dbo.RestaurantSales rs where rs.LoadDate = @LoadDate and rs.RestaurantType = 'pizza' and rs.DishName like '%pizza%' group by rs.CityID, rs.DishName go
посмотрим план запроса
Оптимизатор предложил нам создать недостающий индекс, который, по его мнению, даст прирост около 70% производительности, последуем его совету и создадим индекс в точности как предложил оптимизатор (здесь мы не задумываемся о стоимости поддержки такого индекса, т.к. задача примера не в этом).
CREATE NONCLUSTERED INDEX [IX_RestaurantSales_LoadDate_RestType] ON [dbo].[RestaurantSales] ([LoadDate],[RestaurantType]) INCLUDE ([CityID],[DishName],[SaledCount])
Выполним запрос (не отключая «Include Actual Execution Plan»), и посмотрим на план и время. Здесь и далее, чтобы получить не только время компиляции и выполнения запроса, но так же все время, включая авто обновление статистики, будем замерять его как простую разницу между двумя вызовами getdate() вместо set statistics time on.
-- стартуем таймер declare @start_time datetime set @start_time = getdate() -- выполняем запрос declare @LoadDate datetime select @LoadDate = max(LoadDate) from dbo.RestaurantSales select rs.CityID, rs.DishName, SaledInCity = sum(rs.SaledCount) from dbo.RestaurantSales rs where rs.LoadDate = @LoadDate and rs.RestaurantType = 'pizza' and rs.DishName like '%pizza%' group by rs.CityID, rs.DishName -- печатаем показания таймера print convert(varchar(100), datediff(ms,@start_time,getdate()))+' ms.' go
(5000 row(s) affected) (1 row(s) affected) 546 ms.
Итак, мы видим, что запрос выполняется примерно полсекунды, а в плане используется только поиск по заданному индексу. Теперь, загрузим данные (см. сноску *) еще раз и выполним наш запрос два раза (не отключая «Include Actual Execution Plan»).
exec dbo.RestaurantSales_load go -- стартуем таймер declare @start_time datetime set @start_time = getdate() -- выполняем запрос declare @LoadDate datetime select @LoadDate = max(LoadDate) from dbo.RestaurantSales select rs.CityID, rs.DishName, SaledInCity = sum(rs.SaledCount) from dbo.RestaurantSales rs where rs.LoadDate = @LoadDate and rs.RestaurantType = 'pizza' and rs.DishName like '%pizza%' group by rs.CityID, rs.DishName -- печатаем показания таймера print convert(varchar(100), datediff(ms,@start_time,getdate()))+' ms.' go 2 --два раза
Beginning execution loop (1 row(s) affected) (5000 row(s) affected) (1 row(s) affected) 4720 ms. (1 row(s) affected) (5000 row(s) affected) (1 row(s) affected) 580 ms. Batch execution completed 2 times.
Мы видим, что после добавления, время первого выполнения запроса — возросло почти в 10 раз. Во второй раз запрос выполнился уже быстро.* При этом планы были одинаковые. Дело в том, что в первый раз, т.к. запрос выполнялся сразу после добавления большого числа строк, происходил пересчет статистики и создание нового плана выполнения. Попробуем обновить статистику сразу после загрузки, чтобы на момент получения отчета вся статистика была актуальна и не пересчитывалась. Т.к. оптимизатор для поиска данных использует только индекс [IX_RestaurantSales_LoadDate_RestType], то логичным было бы перестроить статистику только по этому индексу. И это кажется таким правильным, ведь мы действуем из лучших побуждений, не заставляя сервер выполнять лишнюю работу и тратить лишние ресурсы. Но на самом деле этого будет недостаточно.
Проведем эксперимент. Загрузим данные, обновим статистику и снова выполним наш запрос.
exec dbo.RestaurantSales_load go update statistics dbo.RestaurantSales IX_RestaurantSales_LoadDate_RestType go -- стартуем таймер declare @start_time datetime set @start_time = getdate() -- выполняем запрос declare @LoadDate datetime select @LoadDate = max(LoadDate) from dbo.RestaurantSales select rs.CityID, rs.DishName, SaledInCity = sum(rs.SaledCount) from dbo.RestaurantSales rs where rs.LoadDate = @LoadDate and rs.RestaurantType = 'pizza' and rs.DishName like '%pizza%' group by rs.CityID, rs.DishName -- печатаем показания таймера print convert(varchar(100), datediff(ms,@start_time,getdate()))+' ms.' go
(5000 row(s) affected) (1 row(s) affected) 4436 ms.
Что мы в итоге получаем, так это такое же большое значение времени. Чтобы понять, на что оптимизатор тратит это время, если мы обновили статистику по используемому индексу, воспользуемся профайлером.
Перед этим еще раз загрузим данные.
exec dbo.RestaurantSales_load go
Теперь откроем профайлер и выберем события Auto stats, Performance statistics, BatchCompleted, BatchStarted. Исключим лишние колонки, запустим профайлер и выполним наш запрос.
declare @LoadDate datetime select @LoadDate = max(LoadDate) from dbo.RestaurantSales select rs.CityID, rs.DishName, SaledInCity = sum(rs.SaledCount) from dbo.RestaurantSales rs where rs.LoadDate = @LoadDate and rs.RestaurantType = 'pizza' and rs.DishName like '%pizza%' group by rs.CityID, rs.DishName go
Вот что выдал профайлер.
Глядя на эту картину, становится видно куда уходит время. Вместо того чтобы обновить статистику один раз только по нашему индексу, он обновляет статистику целых пять раз. Некоторые из них занимают вполне ощутимое время, около 2 секунд. Что это за статистики?
Дело в том что если в БД включена опция AUTO_CREATE_STATISTICS (по-умолчанию включена), то оптимизатор автоматически создает статистику для полей в предикатах запросах.
Посмотреть включена ли такая опция в ваших БД можно запросом.
select name, is_auto_create_stats_on from sys.databases
Подробнее про использование статистики тут.
Посмотрим какие статистики оптмизатор создал для нашей таблицы:
select
stat_name = s.name,
s.auto_created,
column_name = c.name
from
sys.stats s
join sys.stats_columns sc on s.stats_id = sc.stats_id and s.[object_id] = sc.[object_id]
join sys.columns c on sc.column_id = c.column_id and s.[object_id] = c.[object_id]
where
s.[object_id] = object_id('dbo.RestaurantSales')
|
Видим, что помимо статистики, созданной по двум индексам, существую еще три вспомогательные статистики по столбцам CityID, RestaurantType, DishName. Можно сравнить их названия с тем, что показывает нам профайлер, и мы увидим, что именно они обновляются при генерации нового плана.
На картинке планы запросов 1-6 являются планами запросов для обновления статистики (не нашего запроса).
План 7 — план нашего запроса. Мы видим, что он был получен после того как обновились все статистики (а не только статистика по индексу).
Так происходит потому, что после очередной фазы оптимизации, оптимизатор в некоторых случаях, принимает решение обновить статистику, после продолжить поиск плана с более точными сведениями о данных. Именно на это обновление и уходит время.
Теперь проведем финальную часть эксперимента. Загрузим данные. Обновим статистику по всей таблице.
exec dbo.RestaurantSales_load go update statistics dbo.RestaurantSales go
Включим профайлер, выполним запрос 2 раза и посмотрим на время.
-- стартуем таймер declare @start_time datetime set @start_time = getdate() -- выполняем запрос declare @LoadDate datetime select @LoadDate = max(LoadDate) from dbo.RestaurantSales select rs.CityID, rs.DishName, SaledInCity = sum(rs.SaledCount) from dbo.RestaurantSales rs where rs.LoadDate = @LoadDate and rs.RestaurantType = 'pizza' and rs.DishName like '%pizza%' group by rs.CityID, rs.DishName -- печатаем показания таймера print convert(varchar(100), datediff(ms,@start_time,getdate()))+' ms.' go 2
(5000 row(s) affected) (1 row(s) affected) 546 ms. (5000 row(s) affected) (1 row(s) affected) 533 ms.
Итак, время запроса что в первый что во второй раз примерно одинаково. Посмотрим что нам отразил профайлер.
Видим что по-прежнему, после загрузки большого количества строк план перестраивается как неактуальный, однако статистика уже не обновляется.
drop table dbo.RestaurantSales drop proc dbo.RestaurantSales_load go
Кому-то проблема покажется надуманной или не принципиальной, кто-то не сталкивался с таким т.к. обновлял статистику сразу по всей таблице, а кому-то покажется, что и проблемы нет вовсе (ну подождет пользователь в первый раз, ничего страшного).
Все это зависит от ситуации и может оцениваться субъективно. Но мне показалось просто интересным докопаться до сути процесса и понять, что происходит, и в очередной раз, наблюдать механизмы работы оптимизатора.
И последнее замечание. Так как речь идет об оптимизаторе, довольной тонкой материи, то воспроизводя у себя на сервере представленные скрипты вы можете получить немного другие результаты (например, другое время, планы и т.д.).
Версия сервера (чтобы узнать свою выполните select @@version), на которой получены все представленные выше цифры:
1) Microsoft SQL Server 2005 — 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Версии сервера на которых я дополнительно прогонял эти скрипты:
2) Microsoft SQL Server 2005 — 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
3) Microsoft SQL Server 2008 R2 (RTM) — 10.50.1746.0 (X64) Oct 5 2010 12:32:09 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
Но даже выполняя скрипты на таких же в точности версиях серверов — совпадение не является гарантией.
Надеюсь кому-то эта статья как и мне помогла лучше понять — как работает SQL Server.
* примечания
Если после загрузки данных запрос и в первый раз выполняется быстро, значит в вашем случае оптимизатор решил не обновлять статистику,
но это не говорит о том что при следующей загрузке все пройдет так же гладко. У меня было такое несколько раз, когда я прогонял эти скрипты на разных серверах. В этом случае просто загрузите больше данных, дважды вызвав процедуру,
exec dbo.RestaurantSales_load
go 2
— и повторите эксперимент, почти наверняка вы увидите разницу во времени.
