Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sql2014 - выросшее время компиляции  [new]
гость_20140813
Guest
Добрый день,

После перехода на sql2014 было замечено, что время _компиляции_ некоторых запросов стало катастрофически долгим.

Чтоб отгородиться от вопросов "статистику обновляли?", привожу тут тестовый код, который создает и заполняет таблицы с нуля.
Заинтересованным предлагается попробовать запустить у себя.

1. Создаем и заполняем
if object_id( 'dbo.Test_Main' ) is not null drop table dbo.Test_Main 
if object_id( 'dbo.Test_Data_Date' ) is not null  drop table dbo.Test_Data_Date 
if object_id( 'dbo.Test_Data_Num' ) is not null  drop table dbo.Test_Data_Num
go

create table dbo.Test_Main 
  ( ID_Record int identity (1, 1) not null
  , Name varchar(256) null
  )
create table dbo.Test_Data_Date 
  ( ValueType smallint not null
  , ID_Record int not null
  , [Value] datetime not null
  )
create table dbo.Test_Data_Num
  ( ValueType smallint not null
  , ID_Record int not null
  , [Value] int not null
  )

create unique clustered index cidx_1 ON dbo.Test_Main ( ID_Record )
create unique clustered index cidx_1 ON dbo.Test_Data_Date ( ValueType, ID_Record )
create unique clustered index cidx_1 ON dbo.Test_Data_Num ( ValueType, ID_Record )

-- Добавляем одну запись
insert into dbo.Test_Main ( Name ) select 'c1'
go


2. Выполняем наш интересный запрос и засекаем время компиляции
set statistics time on
go

select cm.ID_Record as ID_Record
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 1  ) 
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 2  ) 
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 3  ) 
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 4  )  
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 5  )  
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 6  )  
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 7  )  
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 8  )  
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 9  )  
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 10 )  
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 11 )  
     , ( select [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 12 ) 
     , ( select [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 13 ) 
     , ( select [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 14 ) 
     , ( select [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 15 ) 
     , ( select [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 16 ) 
     , ( select [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 17 ) 
     , ( select [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 18 ) 
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 19 ) 
     , ( select [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 20 ) 
  from dbo.Test_Main cm
go

set statistics time off
go


Получаем время компиляции 7 секунд! :
Время синтаксического анализа и компиляции SQL Server: 
время ЦП = 7166 мс, истекшее время = 7166 мс.


3. Найденные обходные пути
  • меняем уровень совместимости БД set compatibility_level= 110 (т.е. отключаем новый cardinality estimator 2014) - время компиляции 6 мс.
    Что интересно, план запроса при этом такой же, как в исходном тесте. Всё время тратится именно на компиляцию.
  • в подзапросах явно указываем top 1 - время компиляции 9 мс :
    set statistics time on
    go
    
    select cm.ID_Record as ID_Record
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 1  ) 
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 2  ) 
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 3  ) 
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 4  )  
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 5  )  
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 6  )  
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 7  )  
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 8  )  
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 9  )  
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 10 )  
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 11 )  
         , ( select top 1 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 12 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 13 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 14 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 15 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 16 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 17 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 18 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 19 ) 
         , ( select top 1 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 20 ) 
      from dbo.Test_Main cm
    go
    
    set statistics time off
    go
    


    Вопрос - почему так долго компилирует? Почему помог top 1 ?
  • 13 авг 14, 08:33    [16434581]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813_2
    Guest
    гость_20140813,

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

    @@version
    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.3 <X64> (Build 9600: )

    1 запрос
    SQL Server parse and compile time:
    CPU time = 16 ms, elapsed time = 29 ms.

    2 запрос (с топ 1)
    SQL Server parse and compile time:
    CPU time = 15 ms, elapsed time = 15 ms.
    13 авг 14, 10:23    [16434974]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13148
    версия сервера, точная?
    трасфлаги?
    13 авг 14, 10:26    [16435002]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813_2
    Guest
    гость_20140813_2,

    А сорри - был сам не прав - база была в совместимости 2012
    поправил на 2014

    1 запрос
    SQL Server parse and compile time:
    CPU time = 9422 ms, elapsed time = 9474 ms.
    2 запрос
    SQL Server parse and compile time:
    CPU time = 14 ms, elapsed time = 14 ms.

    Чудненько :-)
    13 авг 14, 10:28    [16435011]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    гость_20140813_2,

    Что значит не полностью? Вот именно на таких данных тестирую и получаю результат.

    По поводу ваших результатов - уровень совместимости БД какой ?
    13 авг 14, 10:29    [16435017]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    Отлично, у кого-то тоже проявляется.

    Версия -
    Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    	Feb 20 2014 20:04:26 
    	Copyright (c) Microsoft Corporation
    	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    
    13 авг 14, 10:31    [16435032]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813_2
    Guest
    гость_20140813,


    если к первому запросу добавить
    option (recompile, QUERYTRACEON 9481 ) -- отключаем новый CE

    то всё красиво получается, в смысле некраcиво со стороны нового CE
    13 авг 14, 10:33    [16435049]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13148
    на Connect? а мы - голоснем
    13 авг 14, 10:36    [16435069]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    гость_20140813_2
    если к первому запросу добавить
    option (recompile, QUERYTRACEON 9481 ) -- отключаем новый CE

    то всё красиво получается, в смысле некраcиво со стороны нового CE


    Да, в исходном посте в пункте 3 у меня этот обходной путь тоже замечен.
    13 авг 14, 10:44    [16435140]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    хмхмхм
    Guest
    Подтвердился на версии:

    Microsoft SQL Server 2014 - 12.0.2370.0 (X64)
    Jun 21 2014 15:21:00
    13 авг 14, 11:01    [16435277]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    emperor_bms
    Member

    Откуда:
    Сообщений: 122
    У меня тоже подтвердилось.

    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.2 <X64> (Build 9200: )
    13 авг 14, 11:19    [16435398]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    хмхмхм
    Guest
    Если не поправят, придется переписывать для 2014 сервера такие зподапросы на top 9223372036854775807:

    select cm.ID_Record as ID_Record
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 1  ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 2  ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 3  ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 4  )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 5  )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 6  )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 7  )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 8  )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 9  )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 10 )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 11 )  
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 12 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 13 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 14 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 15 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 16 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 17 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Num d where d.ID_Record = cm.ID_Record and ValueType = 18 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 19 ) 
         , ( select top 9223372036854775807 [Value] from dbo.Test_Data_Date d where d.ID_Record = cm.ID_Record and ValueType = 20 ) 
      from dbo.Test_Main cm
    
    13 авг 14, 11:27    [16435466]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    хмхмхм,

    Именно в этом случае - подзапрос в списке выбора select, возвращает только 1 значение. Не совсем понятно, зачем тут 9223372036854775807.
    У меня мысль была про top 1 - поможем компилятору, подскажем что тут одно значение в выборке. Хотя там индекс уникальный есть..
    Видимо, top уводит оптимизатор на другую ветку алгоритма, правильную :) Возможно на ту же, по которой он ходит без нового CE.

    Это просто догадки..
    13 авг 14, 11:36    [16435547]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    хмхмхм
    Guest
    гость_20140813,

    не хочу дискутировать, но попробуйте добавить еще одну запись в таблицу dbo.Test_Main
    13 авг 14, 11:50    [16435635]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    eny
    Member

    Откуда: москва
    Сообщений: 292
    мдаа накрутили запрос, варианты:
    - выбрать все нужные записи сразу и сделать PIVOT
    - приджойнить таблицы явно и выбрать столбцы, что собсно и должен делать анализатор запроса за кадром, посмотрите план запроса
    13 авг 14, 12:53    [16435994]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    хмхмхм
    гость_20140813,

    не хочу дискутировать, но попробуйте добавить еще одну запись в таблицу dbo.Test_Main


    Не помогает.
    13 авг 14, 12:57    [16436029]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    eny
    мдаа накрутили запрос, варианты:
    - выбрать все нужные записи сразу и сделать PIVOT
    - приджойнить таблицы явно и выбрать столбцы, что собсно и должен делать анализатор запроса за кадром, посмотрите план запроса

    Спасибо. Запрос разные бывают, а этот специально "накручен" (попробовали 10 подзапросов вместо 20 - гораздо всё лучше). Сейчас не об этом.
    Вопрос - почему компилирует долго. И нет ли еще в каких-то случаях таких подводных камней.
    13 авг 14, 13:04    [16436081]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    a_voronin
    Member

    Откуда: Москва
    Сообщений: 4804
    Я всегда считал, что лобовой переход на новую версию без тестирования это неправильно. Мы тоже вот так с кандачка переехали и почувствовали замедление.

    Вторая вещь, это чистая длина запроса. Мегаоптимизированный компилятор переваривает запросы медленно. Я вот держу профайлер включенным сутками без перерыва и оптимизирую.
    13 авг 14, 13:16    [16436158]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    На Microsoft SQL Server 2014 - 12.0.2342.0 (X64) - (2014 CU1) не воспроизвелось.
    На CTP2 тоже нет.

    У кого-то тут подтвердилось на версии 12.0.2370.0 (2014 CU2) и на версии 12.0.2000.8 (2014 RTM) - странно. Сейчас попробую накатить CU2 и проверить.
    13 авг 14, 20:36    [16438563]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    SomewhereSomehow,

    Поправка. На CU1 тоже воспроизвелось.
    13 авг 14, 21:11    [16438651]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    SomewhereSomehow,

    на CU2 воспроизводится.
    14 авг 14, 09:33    [16439465]     Ответить | Цитировать Сообщить модератору
     Re: sql2014 - выросшее время компиляции  [new]
    гость_20140813
    Guest
    Уже есть голосовалка по этой проблеме:
    [url=]https://connect.microsoft.com/SQLServer/feedbackdetail/view/905677/new-2014-cardinality-estimator-changes-compile-duration-from-nothing-to-infinity[/url]
    14 авг 14, 14:34    [16441654]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить