Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 CXPACKET  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
Всем привет,

Сразу скажу я не админ SQL Server, разбираюсь поверхностно.

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

Max Degree of Parallelism = 0
Cost Threshold for Parallelism = 5
Snapshot Isolation не включен, но мы рассматриваем такую возможность, т.к. многие, до 10 компонентов системы работают одновременно и как минимум читают данных друг друга, из-за чего 2-3 раза в день случаются дедлоки.

Первое на что я подумал это locks и wait types.
В топе оказался CXPACKET с 65.61 % ожидания.
Кто-то говорит это много, кто-то мало.

Но одно я могу сказать, у нас система по архитектуре напоминает OLAP, т.е. пакетная обработка данных, но объемы данные как у OLTP. Тяжелые вычесления со считыванием больше 100 Mb составляют < 1%, т.е. почти все селективные поля индексированны.

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

Прошу пожалуйста подсказать, есть ли проблемма в CXPACKET или нет?

WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S
CXPACKET111651706.80106300279.695351427.117041667639965.610.00160.00150.0001
PREEMPTIVE_XE_DISPATCHER15063530.6115063530.610.0010798.8513960.640013960.64000.0000
PREEMPTIVE_SP_SERVER_DIAGNOSTICS14701860.1914701860.190.0098.641633540.02141633540.02140.0000
PREEMPTIVE_HADR_LEASE_MECHANISM14701856.3514701856.350.0088.641837732.04361837732.04360.0000
LATCH_EX5857094.075662921.87194172.2030715203473.440.00190.00180.0001


К сообщению приложен файл (wait_types_summery.sql - 3Kb) cкачать
15 май 17, 16:19    [20482337]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Skulll,

автор
В топе оказался CXPACKET с 65.61 % ожидания.

у меня на одном из - 67%

автор
Cost Threshold for Parallelism = 5

можно поднимать/экспериментировать , тем более "Я это к тому что многопоточность нам почти не нужна"

и вообще не лечите здорового, есть проблем - изучайте
15 май 17, 16:26    [20482356]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
TaPaK
можно поднимать/экспериментировать , тем более "Я это к тому что многопоточность нам почти не нужна"

и вообще не лечите здорового, есть проблем - изучайте


Проблем в отдельных запросах нет, проблемы есть в производительности всей системы, 90% из которох зашифрованно.
Если скажите куда копить, то было бы здорово.

Есть ли такая возможность в SQL Server 2014 как запись и воспроизведения всей активности за сутки?
Т.е. записываем в некий файл все sql команды за день с прода, затем восстанавливаем копию прода на утро, отрабатываем этот файл, мерим загруженность системы и время выполнения команд, и далее эксперементируем с Cost Threshold for Parallelism
15 май 17, 16:34    [20482373]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Skulll
Есть ли такая возможность в SQL Server 2014 как запись и воспроизведения всей активности за сутки?
Т.е. записываем в некий файл все sql команды за день с прода, затем восстанавливаем копию прода на утро, отрабатываем этот файл, мерим загруженность системы и время выполнения команд, и далее эксперементируем с Cost Threshold for Parallelism
Зашифрованые не запишите и не увилидите. Обращайтесь к разработчикам вашего чудо-сфота.
15 май 17, 16:55    [20482445]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
Гавриленко Сергей Алексеевич
Skulll
Есть ли такая возможность в SQL Server 2014 как запись и воспроизведения всей активности за сутки?
Т.е. записываем в некий файл все sql команды за день с прода, затем восстанавливаем копию прода на утро, отрабатываем этот файл, мерим загруженность системы и время выполнения команд, и далее эксперементируем с Cost Threshold for Parallelism
Зашифрованые не запишите и не увилидите. Обращайтесь к разработчикам вашего чудо-сфота.


А зачем их видеть, можно просто записать их вызовы, а то что он внутри там делает не важно, т.к. никто ничего там менять не будет
15 май 17, 17:07    [20482484]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Skulll
Гавриленко Сергей Алексеевич
пропущено...
Зашифрованые не запишите и не увилидите. Обращайтесь к разработчикам вашего чудо-сфота.


А зачем их видеть, можно просто записать их вызовы, а то что он внутри там делает не важно, т.к. никто ничего там менять не будет
Еще раз -- сервер никаким образом ни в каких трассах не выдаст и не запишет вам зашифрованные команды.
15 май 17, 17:08    [20482492]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
Гавриленко Сергей Алексеевич
Skulll
пропущено...


А зачем их видеть, можно просто записать их вызовы, а то что он внутри там делает не важно, т.к. никто ничего там менять не будет
Еще раз -- сервер никаким образом ни в каких трассах не выдаст и не запишет вам зашифрованные команды.


Мне не нужны зашифрованные команды ни в каком виде, мне нужен их вызов. Вы же можете вызвать зашифрованную процедуру?
15 май 17, 17:17    [20482525]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Skulll
Гавриленко Сергей Алексеевич
пропущено...
Еще раз -- сервер никаким образом ни в каких трассах не выдаст и не запишет вам зашифрованные команды.


Мне не нужны зашифрованные команды ни в каком виде, мне нужен их вызов. Вы же можете вызвать зашифрованную процедуру?
Вызов записать можно. например, профайлером.
15 май 17, 17:18    [20482530]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
Гавриленко Сергей Алексеевич
Skulll
пропущено...


Мне не нужны зашифрованные команды ни в каком виде, мне нужен их вызов. Вы же можете вызвать зашифрованную процедуру?
Вызов записать можно. например, профайлером.


Да, но хотелось бы комплексной тулзы типа вот такой, которая захватывает, накатывает и выдает тестовую информацию
http://www.oracle.com/technetwork/articles/sql/11g-replay-099279.html
15 май 17, 18:32    [20482738]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Владислав Колосов
Member

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

там специальный шаблон есть.
15 май 17, 18:36    [20482745]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
Владислав Колосов
Skulll,

там специальный шаблон есть.


Можете пожалуйста скинуть ссылку на шаблон?
16 май 17, 11:35    [20484191]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Владислав Колосов
Member

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

в профайлере шаблон TSQL_replay.
https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/replay-requirements

Если у вас 1.6 микросекунды среднее ожидание, то это многовато, у меня 0.15.
16 май 17, 12:23    [20484419]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
ZaloJNik
Member

Откуда:
Сообщений: 45
Добрый день.
В дополнении к теме, решил поиграть с параметром cost threshold for parallelism и понят откуда скуль берет это значение.
Использовал базу AdventureWorks2014 и Microsoft SQL Server 2014 (SP2) 12.0.5000.0 Developer Edition (64-bit)
Запустил запрос при стандартных параметрах (cost threshold for parallelism - 5 и MAXDOP -0):
SELECT TOP (1000)
ROW_NUMBER() over(partition by city order by city)as part,
[AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2014].[Person].[Address]

Построился план с параллелизмом и estimate subtree cost 55,6262, ну думаю, вот она цена, на которую ссылается скуль при выборе паралеллить запрос или нет. Поставил ctfp - 60, перезапустил, опять параллелит. В общем опытным путем было найдено число 222 для ctfp при котором скуль перестает использовать распараллеливание.
Остается вопрос, почему именно 222, на каком основании он принимает это решение?
На системе использую 8 ядер и 4ГБ ОЗУ под скуль.
З.Ы. : про компьютер Ника читал, ссылку на него не предлагать))
17 май 17, 11:10    [20488685]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ZaloJNik,
З.Ы. : про компьютер Ника читал, ссылку на него не предлагать))
так что вам ещё рассказать о попугаях?
17 май 17, 11:15    [20488727]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
ZaloJNik
Member

Откуда:
Сообщений: 45
TaPaK,
Можно ли самому вычислить это значение, ну там по плану запроса или еще где
17 май 17, 11:20    [20488765]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
invm
Member

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

https://technet.microsoft.com/en-us/library/ms190949(v=sql.120).aspx
In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism value. This can happen because the decision to use a parallel or serial plan is based on a cost estimate provided before the full optimization is complete.
17 май 17, 11:27    [20488825]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
ZaloJNik
Member

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

Да, все так, но как он посчитал? Что на что умножил, что появилось именно число 222. Тем более estimated subtree cost 55,62, такое же как и у актуального плана.
17 май 17, 11:34    [20488880]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ZaloJNik
invm,

Да, все так, но как он посчитал? Что на что умножил, что появилось именно число 222. Тем более estimated subtree cost 55,62, такое же как и у актуального плана.

выбор параллельного плана не самая последняя операция в алгоритме оптимизации. Т.е переведя то что привёл invm: у вас нет таких данных.
17 май 17, 11:36    [20488902]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
ZaloJNik
Member

Откуда:
Сообщений: 45
Разобрался.
Если посмотреть план запроса без параллелизма, то его estimated subtree cost -221,3. А цена параллельного плана 55. Т.е. изначально отталкивался не от того значения.
Получается скуль строит план, смотрит его цену и если она выше чем cost threshold for parallelism то принимает решение на распараллеливание и пересобирает план с параллелизмом.
17 май 17, 11:49    [20488982]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Skulll
Member

Откуда: Чехия
Сообщений: 487
ZaloJNik
Добрый день.
В дополнении к теме, решил поиграть с параметром cost threshold for parallelism и понят откуда скуль берет это значение.
Использовал базу AdventureWorks2014 и Microsoft SQL Server 2014 (SP2) 12.0.5000.0 Developer Edition (64-bit)
Запустил запрос при стандартных параметрах (cost threshold for parallelism - 5 и MAXDOP -0):
SELECT TOP (1000)
ROW_NUMBER() over(partition by city order by city)as part,
[AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2014].[Person].[Address]

Построился план с параллелизмом и estimate subtree cost 55,6262, ну думаю, вот она цена, на которую ссылается скуль при выборе паралеллить запрос или нет. Поставил ctfp - 60, перезапустил, опять параллелит. В общем опытным путем было найдено число 222 для ctfp при котором скуль перестает использовать распараллеливание.
Остается вопрос, почему именно 222, на каком основании он принимает это решение?
На системе использую 8 ядер и 4ГБ ОЗУ под скуль.
З.Ы. : про компьютер Ника читал, ссылку на него не предлагать))


У меня нет возможности установить эту базу.
В теории параллелизм должен использоваться когда 1. потоки не мешают друг другу 2. когда стоимость на их управление меньше преимущества от них
Но на практике я бы не стал включать параллелизм для запроса он обрабатывает меньше 100-1000 мегабайт данных. В нашей базе такие запросов <1%, но параллелизм включается везде, хоть на 2 строчках
17 май 17, 11:50    [20488992]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ZaloJNik,
почти так
https://www.google.com.ua/url?sa=i&rct=j&q=&esrc=s&source=images&cd=&cad=rja&uact=8&ved=0ahUKEwi91q7GyPbTAhXHXRQKHQpfBjMQjRwIBw&url=https://msdn.microsoft.com/en-us/library/aa226174(v=sql.70).aspx&psig=AFQjCNFAGAAXySD3GY3XxZsoArZsFz5eTw&ust=1495098089291997


автор
Но на практике я бы не стал включать параллелизм для запроса он обрабатывает меньше 100-1000 мегабайт данных.
на практике это показатели для конкретного сервера и базы, а не МБ и подобная чушь
17 май 17, 12:05    [20489110]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
ZaloJNik
Разобрался.
Если посмотреть план запроса без параллелизма, то его estimated subtree cost -221,3. А цена параллельного плана 55. Т.е. изначально отталкивался не от того значения.
Кто же мог подумать, что вы берете оценку из параллельного плана?
17 май 17, 12:06    [20489121]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
ZaloJNik
Member

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

Да, поспешил.
17 май 17, 12:10    [20489149]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
Владислав Колосов
Member

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

если не прочтете справку по cost threshold for parallelism так и будете теряться в догадках. И метод тыка не поможет.
17 май 17, 12:22    [20489201]     Ответить | Цитировать Сообщить модератору
 Re: CXPACKET  [new]
ZaloJNik
Member

Откуда:
Сообщений: 45
Владислав Колосов,
Ваши замечания не уместы и не относятся к теме обсуждения.
17 май 17, 12:33    [20489245]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить