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

есть некоторый набор процедур, которые работали на SQL 2012. При переходе на SQL 2014 заметно стал нагружаться процессор при запуске этих процедур.
Посмотрел в планы выполнения, оказывается у одной процедуры появилось аж 12 планов выполнения (число колеблется от процедуры, но в среднем по 12 на каждую) и планы идентичные (сравнивал xml с помощью comparer-a).

Никто не сталкивался с таким поведением? Что это может значить и как с этим бороться?
26 дек 14, 12:26    [17056432]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
хмхмхм
Guest
Версия:
Microsoft SQL Server 2014 - 12.0.2456.0 (X64)
Dec 11 2014 17:32:03
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

В процедурах используются in-memory таблицы.
26 дек 14, 12:27    [17056447]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
SomewhereSomehow
Member

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

А plan_handle то разные? У процедуры может быть несколько планов для разных настроек опций соединения.
Возьмите два "одинаковых" плана и сравните их опции при помощи dmv: sys.dm_exec_plan_attributes(plan_handle) ... where is_cache_key = 1
Какой-то из ключей кэша должен отличаться, если так - ищите, кто у вас с какими настройками соединения подключается.
26 дек 14, 12:53    [17056643]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
хмхмхм
Guest
SomewhereSomehow,

plan_handle одинаковые

SELECT qs.plan_handle, count(1) --a.attrlist, qp.query_plan
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
--CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
--              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
--              WHERE  epa.is_cache_key = 1
--              ORDER  BY epa.attribute
--              FOR    XML PATH('')) AS a(attrlist)
--CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE  
  est.dbid = db_id('DB')
  and est.objectid = object_id('myProc')
  group by qs.plan_handle



Рузельтат:
0x05000A007F19AB66D066DE740300000001000000000000000000000000000000000000000000000000000000 34

Соответственно и опции подключения у них у всех одинаковые.
34 одинаковых плана для одной процедуры, как это можно объяснить?
26 дек 14, 13:05    [17056767]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Glory
Member

Откуда:
Сообщений: 104751
хмхмхм
34 одинаковых плана для одной процедуры, как это можно объяснить?

сколько _разных записей_ в dm_exec_query_stats для этого плана ?
26 дек 14, 13:12    [17056841]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
SomewhereSomehow
Member

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

Если plan_handle одинаковый, то это один и тот же план.

sys.dm_exec_query_stats
автор
Представление содержит по одной строке для каждой инструкции запроса в плане в кэше, а время жизни строк связано с самим планом.

Ошиблись немного с запросом.
26 дек 14, 13:12    [17056844]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
хмхмхм
Guest
Glory
хмхмхм
34 одинаковых плана для одной процедуры, как это можно объяснить?

сколько _разных записей_ в dm_exec_query_stats для этого плана ?


34 шт.

SELECT count(1)
FROM   sys.dm_exec_query_stats qs
where qs.plan_handle = 0x05000A007F19AB66D066DE740300000001000000000000000000000000000000000000000000000000000000
26 дек 14, 13:59    [17057258]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
хмхмхм
Guest
SomewhereSomehow,

спасибо

SomewhereSomehow
Ошиблись немного с запросом.

так и есть
26 дек 14, 14:01    [17057285]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
SomewhereSomehow
Member

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

Вот еще немного в помощь, если планы смотреть по инструкциям: sys.dm_exec_text_query_plan (Transact-SQL) Примеры В и Г.
Если статистику смотреть по процедурам, а не по инструкциям: sys.dm_exec_procedure_stats
26 дек 14, 14:07    [17057338]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
SomewhereSomehow,
а как сюда выгрызть время компиляции? парсить текст плана?
8 июл 15, 17:26    [17868641]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
Mike_za,

set statistics time on
8 июл 15, 17:55    [17868804]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
хмхмхм
Версия:
Microsoft SQL Server 2014 - 12.0.2456.0 (X64)
Dec 11 2014 17:32:03
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

В процедурах используются in-memory таблицы.


1) Если вы говорите, что в процедурах используются in-memory таблицы, то значит вы ПЕРЕПИСАЛИ ПРОЦЕДУРЫ, а не перенесли. Потому что In-Memeory появилось в 2014.
2) Прочитайте мой пост по InMemeory https://www.sql.ru/forum/1113054/vpechatlenie-ot-memory-optimized-tehnologii-na-sql-2014?hl=inmemeory
3) естественно у вас стал нагружаться процессор, потому что InMemоry снижает чтение с диска и больше загружает процессор. Вам нужно понять, что для вас критичнее.
4) Как вы тестировали? Чисто по времени исполнения отдельный InMemory запрос может быть хуже предыдущей версии. Выгода достигается лишь при большой параллельности. Вам надо сравнить 20 параллельных запрос с Inmemory и без. Тогда будет более понятная картина.
8 июл 15, 18:02    [17868847]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mike_za
SomewhereSomehow,
а как сюда выгрызть время компиляции? парсить текст плана?
А вы уверены что именно в этом проблема? Запрос может компилироваться один раз, а потом использоватся без перекомпиляции. Чтобы убедится в этом, смотрите на plan_generation_num и execution_count.
Да и процессор нагружается не только при компиляции.
8 июл 15, 22:38    [17869753]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Mind, я рассматриваю пачку кучу проблем. И одна из них - это куча динамики и времянок. Хотелось получить суммарное время компиляций, и обосновать необходимость переписания большого пласта кода в базе.
8 июл 15, 22:59    [17869811]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
[quot Mike_zaкуча динамики и времянок.
...
обосновать необходимость переписания большого пласта кода в базе.[/quot]

динамика динамике рознь
8 июл 15, 23:03    [17869819]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
churupaha,

Select
Bklablabla
Into ##blablabla_+ cast( newid()...)+ _blablabla

Я думаю, что про повторное использование кеша планов подобных запросов говорить не стоит?)
8 июл 15, 23:39    [17869914]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mike_za
Mind, я рассматриваю пачку кучу проблем. И одна из них - это куча динамики и времянок. Хотелось получить суммарное время компиляций, и обосновать необходимость переписания большого пласта кода в базе.
тогда - парсить текст плана.
9 июл 15, 01:28    [17870011]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Mike_za
Хотелось получить суммарное время компиляций
Если нет монструозных запросов, план которых невозможно получить в виде xml:
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select
 sum(t.n.value('@CompileTime', 'int'))
from
 sys.dm_exec_cached_plans cp cross apply
 sys.dm_exec_query_plan(cp.plan_handle) p cross apply
 p.query_plan.nodes('//QueryPlan') t(n);
9 июл 15, 10:02    [17870516]     Ответить | Цитировать Сообщить модератору
 Re: SQL 2014 - по 12 одинаковых планов на процедуру - почему  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
invm,

Спасибо!
9 июл 15, 21:24    [17874501]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить