Секционированные представления и магические 64 таблицы

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

Ситуация воспроизводится на разных версиях SQL Server, в том числе и на версии SQL Server 2014.

Но прежде нам необходимо создать несколько объектов (100 таблиц и 1 представление).

use tempdb;
go
set nocount on;
go
------------
--В динамике создаём 100 таблиц с чеками, в каждую таблицу вставляю по 100 записей
------------
declare @cmd varchar(4096)
      , @i int = 1
      , @dt datetime = '20140101';

while @i <= 100
begin
set @cmd = ' create table dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' ( id uniqueidentifier, dt datetime '
         + ' , constraint ck_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' check ( dt >= ' + quotename ( convert( varchar, @dt, 126 ),'''' ) 
         + ' and dt < ' + quotename ( convert( varchar, @dt + 1, 126 ),'''' ) + ' )'
         + ' , constraint pk_' + right( '00' + cast( @i as varchar ), 3 )
         + ' primary key( dt, id )' 
         + ' );';
exec ( @cmd );
 
set @cmd = ' insert into dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' select top 100 newid(), dateadd( mi, t1.number, ' + quotename ( convert( varchar, @dt, 126 ),'''' ) 
         + ' ) from master..spt_values t1 '
         + ' where t1.number between 0 and 1439 '
         + ' order by newid()';
exec ( @cmd );

select @dt += 1, @i += 1;
end
go

Теперь у меня в БД TempDB 100 таблиц с именами: tbl_001, tbl_002, tbl_003, ..., tbl_100. Создадим представление:

------------
--В динамике создаём представление по этим таблицам
------------
declare @cmd varchar(4096)
      , @i int = 1;
while @i <= 100
begin
set @cmd = isnull( @cmd + char(13) + 'union all' + char(13)  , '' )
        + 'select dt,id from dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 );        
set @i += 1
end
set @cmd = 'create view dbo.v'
         + char(13)
         + 'with schemabinding' 
         + char(13)
         + 'as' + char(13)
         + @cmd;
exec ( @cmd );
go

Теперь у нас всё готово для проведения экспериментов. Для начала выполним простой запрос: "Получить максимальную дату за 31 января"

select top (1) dt 
  from dbo.v
  where dt >= '20140131' 
    and dt < '20140201'
  order by dt desc;
go

Запрос примитивный, план тоже:

Теперь немного изменим запрос и будем передавать период в виде параметров:

declare @ds datetime = '20140131' 
      , @dt datetime = dateadd( day, 1, '20140131' );
select top (1) dt from dbo.v
  where dt >= @ds 
    and dt < @dt
  order by dt desc;
go

Теперь оптимизатор не знает какие будут входные параметры и план запроса у нас меняется:

Т.е. в плане присутствуют все наши таблицы (план упрощён, основная часть таблиц вырезана). Но не стоит пугаться, т.к. реально данные были "подняты" только из одной таблицы dbo.tbl_031, как и в предыдущем запросе. Кроме того это можно увидеть если выполнить наш запрос с выводом статистик ввода/вывода:

set statistics io on;
declare @ds datetime = '20140131' 
      , @dt datetime = dateadd( day, 1, '20140131' );
select top (1) dt from dbo.v
  where dt >= @ds 
    and dt < @dt
  order by dt desc;
go

Т.е. чтения были только из одной таблицы:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_031'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Но в любом случаи у нас план стал "тяжелее", т.е. он перестал быть тривиальным (Optimization Level: Full). На что ещё следует обратить внимание, так это на Reason for Early Termination: Time Out, т.е. оптимизатор вывел тот план, который он смог найти за отведенное ему количество итераций (Optimization Task) и далеко не факт, что этот план является оптимальным (по данным темам настоятельно рекомендую блог Дмитрия Пилюгина). Мы можем всячески влиять на запрос, чтобы оптимизатор смог найти достаточно хороший план(Good Enough Plan), например подсказка оптимизатору recompile. Но гораздо интересней такое наблюдение, что хороший план оптимизатор сможет самостоятельно найти, если мы наш запрос явно ограничим 64-таблицами (это можно сделать, как через удаление некоторых таблиц из представления либо ограничить константами период для поиска). Для наглядности можно выполнить 2 запроса:

declare @ds datetime = '20140131' 
      , @dt datetime = dateadd( day, 1, '20140131' );
select top (1) dt from dbo.v
  where dt between '20140101' and '20140306' --период затрагивает 65 таблиц по дню
    and dt >= @ds 
    and dt < @dt
    order by dt desc;
go

declare @ds datetime = '20140131' 
      , @dt datetime = dateadd( day, 1, '20140131' );
select top (1) dt from dbo.v
  where dt between '20140101' and '20140305' --период затрагивает 64 таблицы по дню
    and dt >= @ds 
    and dt < @dt
    order by dt desc;
go

У первого план похож на тот, который выше (только кол-во таблиц в плане не 100, а 65) и он Reason for Early Termination: Time Out, а второй план Reason for Early Termination: Good Enough Plan Found.

Но так ли принципиально бороться за то, чтобы оптимизатор всегда выбирал оптимальный план?

И для чего вообще эта статья? А дело в том, что, как я писал выше, это пример из реальной жизни, когда представление со временем достигло этого "магического" предела в 64 таблицы то на больших объёмах данных была получена существенная деградация в привычных казалось бы запросах. Для демонстрации удалим наши объекты и создадим их по новой, но уже с достаточным кол-ом записей в каждой таблице (до нескольких млн.).

------------
--Подчищаем за собой
------------
if object_id ( N'dbo.v', N'V' ) is not null
drop view dbo.v;
go

exec sp_MSforeachtable 'if "?" like ''%tbl_[0-9][0-9][0-9]%'' drop table ?'
go

А теперь создадим объекты с данными (скрипт тяжёлый и работает достаточно долго):

use tempdb;
go
set nocount on;
go
------------
--В динамике создаём 100 таблиц с чеками, в каждую таблицу вставляю несколько млн. строк
------------
declare @cmd varchar(4096)
      , @i int = 1
      , @dt datetime = '20140101';

while @i <= 100
begin
set @cmd = ' create table dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' ( id uniqueidentifier, dt datetime '
         + ' , constraint ck_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' check ( dt >= ' + quotename ( convert( varchar, @dt, 126 ),'''' ) 
         + ' and dt < ' + quotename ( convert( varchar, @dt + 1, 126 ),'''' ) + ' )'
         + ' , constraint pk_' + right( '00' + cast( @i as varchar ), 3 )
         + ' primary key( dt, id )' 
         + ' );';
exec ( @cmd );
 
set @cmd = ' insert into dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' select newid(), dateadd( mi, t1.number, ' + quotename ( convert( varchar, @dt, 126 ),'''' ) 
         + ' ) from master..spt_values t1, master..spt_values t2 '
         + ' where t1.number between 0 and 1439 '
         + ' order by newid()';
exec ( @cmd );

select @dt += 1, @i += 1;
end
go


------------
--В динамике создаём представление по этим таблицам
------------
declare @cmd varchar(4096)
      , @i int = 1;
while @i <= 100
begin
set @cmd = isnull( @cmd + char(13) + 'union all' + char(13)  , '' )
        + 'select dt,id from dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 );        
set @i += 1
end
set @cmd = 'create view dbo.v'
         + char(13)
         + 'with schemabinding' 
         + char(13)
         + 'as' + char(13)
         + @cmd;
exec ( @cmd );
go

А теперь выполним 2 запроса на таблицах с данными:

set statistics io on;
set statistics time on;
go

declare @ds datetime = '20140131' 
      , @dt datetime = dateadd( day, 1, '20140131' );
select top (1) dt from dbo.v
  where dt between '20140101' and '20140306' --период затрагивает 65 таблиц по дню
    and dt >= @ds 
    and dt < @dt
    order by dt desc;
go

declare @ds datetime = '20140131' 
      , @dt datetime = dateadd( day, 1, '20140131' );
select top (1) dt from dbo.v
  where dt between '20140101' and '20140305' --период затрагивает 64 таблицы по дню
    and dt >= @ds 
    and dt < @dt
    order by dt desc;
go

Результат 1-ого запроса (65 таблиц):

Table 'tbl_018'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_065'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_064'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_063'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_062'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_061'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_060'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_059'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_058'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_057'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_056'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_055'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_054'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_053'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_052'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_051'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_050'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_049'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_048'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_047'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_046'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_045'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_044'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_043'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_042'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_041'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_040'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_039'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_038'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_037'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_036'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_035'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_034'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_033'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_032'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_031'. Scan count 9, logical reads 18957, physical reads 2, read-ahead reads 17186, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_030'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_029'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_028'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_027'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_026'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_025'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_024'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_023'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_022'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_021'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_020'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_019'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_017'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_016'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_015'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_014'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_013'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_012'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_011'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_010'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_009'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_008'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_007'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_006'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_005'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_004'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_003'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_002'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_001'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1466 ms,  elapsed time = 3111 ms.

Результат 2-ого запроса (64 таблицы):

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_031'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 31 ms.

В первом запросе "дикие" чтения:

Table 'tbl_031'. Scan count 9, logical reads 18957 ahead reads 17186

против

Table 'tbl_031'. Scan count 1, logical reads 3 ahead reads 0.

И время отличается в разы:

CPU time = 1466 ms, elapsed time = 3111 ms.

против

CPU time = 0 ms, elapsed time = 31 ms.

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

Версии SQL Server:

Microsoft SQL Server  2000 - 8.00.2305 (Intel X86) 
	May 14 2012 16:24:50 
	Copyright (c) 1988-2003 Microsoft Corporation
	Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
	Feb 20 2014 20:04:26 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)