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

Откуда:
Сообщений: 710
Добрый день, коллеги !!! Есть сервер сугубо под БД для 1С 8.2 на
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64) Nov 30 2012 17:11:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
hit rates на нём в процедурный кэш постоянно скачит с 70 до 90% в течении дня стандартной пользовательской нагрузки и если посмотреть на график то он пилообразен. Собственно с чем это связано, не пойму принцип взаимодействия 1С и MS SQL для объяснения такого поведения. Просветите тёмного :)
21 фев 13, 11:03    [13959423]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34998
Блог
может он у вас периодически чистится?
21 фев 13, 11:16    [13959523]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Критик
может он у вас периодически чистится?

ночью после сбора статистики выполняется DBCC FREEPROCCACHE, но по мере наполнения кэша показатель hit ratio более менее стабильно ползёт вверх и после достижения определённого значения(~8Гб) рост места в ОЗУ под процедурный кэш прекращается и начинаются весьма частые скачки с 70 до 90% как подсказывает мне мониторинг обусловлено это SQL Plans hit ratio которого точно также изменяется скачками в отличии от всех остальных структур в процедурном кэше hit ratio которых более менее стабильны.

С чем это связано ? Может не надо так часто делать DBCC FREEPROCCACHE и добавить в конфиг формирование стабов для первичного построения планов выполнения на adhoc запросах ?
21 фев 13, 11:49    [13959777]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Зарегался
Member

Откуда:
Сообщений: 56
stavgreengo
ночью после сбора статистики выполняется DBCC FREEPROCCACHE

А какой в этом смысл? После обновления статистики планы запросов и так будут постепенно перекомпилированы.
Я бы на вашем месте не заморачивался по поводу hit ratio, если нет явных тормозов с этим связанных. Кроме того, эффективней оптимизировать код самого приложения на предмет динамического формирования запросов, нежели пытаться сгладить недостатки этого кода настройками SQL сервера.
21 фев 13, 14:58    [13961376]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Зарегался
Кроме того, эффективней оптимизировать код самого приложения на предмет динамического формирования запросов, нежели пытаться сгладить недостатки этого кода настройками SQL сервера.
А что, код 1С правда можно оптимизировать?
21 фев 13, 21:52    [13963721]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
stavgreengo
Критик
может он у вас периодически чистится?

ночью после сбора статистики выполняется DBCC FREEPROCCACHE, но по мере наполнения кэша показатель hit ratio более менее стабильно ползёт вверх и после достижения определённого значения(~8Гб) рост места в ОЗУ под процедурный кэш прекращается
Сколько у вас всего памяти выделено под SQL Server? Может 8 гигов под планы запросов которые очень редко выполняются это много, есть же некие лимиты, и сервер просто выкидывает старые запросы из кэша из-за memory pressure.

Попробуйте таки
EXEC sp_CONFIGURE 'optimize for ad hoc workloads', 1
RECONFIGURE

Еще можно посмотреть что там в том кэше хотя бы в общих чертах.
+ вот так
;WITH cacheobjects
AS
(
SELECT 
  cacheobjtype,
  objtype, 
  CAST(SUM(ISNULL(size_in_bytes,0))/1024./1024. AS DECIMAL(20,2)) AS TotalMB, 
  AVG(ISNULL(size_in_bytes,0))/1024. AS AvgKB, 
  COUNT(*) AS [Count], 
  SUM(usecounts) AS usecounts, 
  AVG(CAST(usecounts AS BIGINT)) AvgUseCount, 
  SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) UsedOnceCount,
  CAST(SUM(CASE WHEN usecounts = 1 THEN ISNULL(size_in_bytes,0)/1024./1024. ELSE 0. END) AS DECIMAL(20,2)) AS UsedOnceMB
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
)
SELECT 
  cacheobjtype, 
  objtype, 
  TotalMB, 
  AvgKB, 
  [Count], 
  AvgUseCount, 
  UsedOnceCount,
  CAST(1.* UsedOnceCount/[Count] AS DECIMAL(20,2)) AS [UsedOnceCount%],
  UsedOnceMB,
  CAST(1.* UsedOnceMB/TotalMB AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
UNION ALL
SELECT 'Total', 'Total', 
  SUM(TotalMB), 
  CAST(SUM(TotalMB)/SUM(CAST([Count] AS BIGINT))*1024. AS DECIMAL(20,2)) AS AvgKB, 
  SUM(Count), 
  SUM(CAST(usecounts AS BIGINT))/SUM(CAST([Count] AS BIGINT)) AS AvgUseCount, 
  SUM(UsedOnceCount), 
  CAST(1.* SUM(UsedOnceCount)/SUM([Count]) AS DECIMAL(20,2)) AS [UsedOnceCount%],
  SUM(UsedOnceMB),
  CAST(1.* SUM(UsedOnceMB)/SUM(TotalMB) AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
ORDER BY TotalMB DESC
21 фев 13, 22:20    [13963803]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Sergey Sizov
Member

Откуда:
Сообщений: 1566
Mind
Зарегался
Кроме того, эффективней оптимизировать код самого приложения на предмет динамического формирования запросов, нежели пытаться сгладить недостатки этого кода настройками SQL сервера.
А что, код 1С правда можно оптимизировать?
В некоторых пределах можно. Еще можно подобрать и создать свои индексы.
22 фев 13, 12:01    [13965910]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Mind
stavgreengo
пропущено...

ночью после сбора статистики выполняется DBCC FREEPROCCACHE, но по мере наполнения кэша показатель hit ratio более менее стабильно ползёт вверх и после достижения определённого значения(~8Гб) рост места в ОЗУ под процедурный кэш прекращается
Сколько у вас всего памяти выделено под SQL Server? Может 8 гигов под планы запросов которые очень редко выполняются это много, есть же некие лимиты, и сервер просто выкидывает старые запросы из кэша из-за memory pressure.

Попробуйте таки
EXEC sp_CONFIGURE 'optimize for ad hoc workloads', 1
RECONFIGURE

Еще можно посмотреть что там в том кэше хотя бы в общих чертах.
+ вот так
;WITH cacheobjects
AS
(
SELECT 
  cacheobjtype,
  objtype, 
  CAST(SUM(ISNULL(size_in_bytes,0))/1024./1024. AS DECIMAL(20,2)) AS TotalMB, 
  AVG(ISNULL(size_in_bytes,0))/1024. AS AvgKB, 
  COUNT(*) AS [Count], 
  SUM(usecounts) AS usecounts, 
  AVG(CAST(usecounts AS BIGINT)) AvgUseCount, 
  SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) UsedOnceCount,
  CAST(SUM(CASE WHEN usecounts = 1 THEN ISNULL(size_in_bytes,0)/1024./1024. ELSE 0. END) AS DECIMAL(20,2)) AS UsedOnceMB
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
)
SELECT 
  cacheobjtype, 
  objtype, 
  TotalMB, 
  AvgKB, 
  [Count], 
  AvgUseCount, 
  UsedOnceCount,
  CAST(1.* UsedOnceCount/[Count] AS DECIMAL(20,2)) AS [UsedOnceCount%],
  UsedOnceMB,
  CAST(1.* UsedOnceMB/TotalMB AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
UNION ALL
SELECT 'Total', 'Total', 
  SUM(TotalMB), 
  CAST(SUM(TotalMB)/SUM(CAST([Count] AS BIGINT))*1024. AS DECIMAL(20,2)) AS AvgKB, 
  SUM(Count), 
  SUM(CAST(usecounts AS BIGINT))/SUM(CAST([Count] AS BIGINT)) AS AvgUseCount, 
  SUM(UsedOnceCount), 
  CAST(1.* SUM(UsedOnceCount)/SUM([Count]) AS DECIMAL(20,2)) AS [UsedOnceCount%],
  SUM(UsedOnceMB),
  CAST(1.* SUM(UsedOnceMB)/SUM(TotalMB) AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
ORDER BY TotalMB DESC

max server memory (MB) 88000
из них 74,6Гб отдано под буферный и 7,98Гб под процедурные кэши.
У скрипта начало срезалось, можете вложить заново ?
22 фев 13, 14:30    [13967064]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34998
Блог
stavgreengo
У скрипта начало срезалось, можете вложить заново ?


попробуйте выполнить )
22 фев 13, 14:49    [13967222]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
то есть не срезалось, сорри за дезу ) но...
Сообщение 8115, уровень 16, состояние 2, строка 2
Ошибка арифметического переполнения при преобразовании expression к типу данных int.

где подкрутить ?
22 фев 13, 15:03    [13967358]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
komrad
Member

Откуда:
Сообщений: 5607
stavgreengo
то есть не срезалось, сорри за дезу ) но...
Сообщение 8115, уровень 16, состояние 2, строка 2
Ошибка арифметического переполнения при преобразовании expression к типу данных int.

где подкрутить ?


подкрутил :

;WITH cacheobjects
AS
(
SELECT 
  cacheobjtype,
  objtype, 
  CAST(cast(SUM(ISNULL(cast(size_in_bytes as bigint),0)) as bigint)/1024./1024. AS DECIMAL(20,2)) AS TotalMB, 
  AVG(ISNULL(cast(size_in_bytes as bigint),0))/1024. AS AvgKB, 
  COUNT(*) AS [Count], 
  SUM(usecounts) AS usecounts, 
  AVG(CAST(usecounts AS BIGINT)) AvgUseCount, 
  SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) UsedOnceCount,
  CAST(SUM(CASE WHEN usecounts = 1 THEN ISNULL(size_in_bytes,0)/1024./1024. ELSE 0. END) AS DECIMAL(20,2)) AS UsedOnceMB
  
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
)
SELECT 
  cacheobjtype, 
  objtype, 
  TotalMB, 
  AvgKB, 
  [Count], 
  AvgUseCount, 
  UsedOnceCount,
  CAST(1.* UsedOnceCount/[Count] AS DECIMAL(20,2)) AS [UsedOnceCount%],
  UsedOnceMB,
  CAST(1.* UsedOnceMB/TotalMB AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
UNION ALL
SELECT 'Total', 'Total', 
  SUM(TotalMB), 
  CAST(SUM(TotalMB)/SUM(CAST([Count] AS BIGINT))*1024. AS DECIMAL(20,2)) AS AvgKB, 
  SUM(Count), 
  SUM(CAST(usecounts AS BIGINT))/SUM(CAST([Count] AS BIGINT)) AS AvgUseCount, 
  SUM(UsedOnceCount), 
  CAST(1.* SUM(UsedOnceCount)/SUM([Count]) AS DECIMAL(20,2)) AS [UsedOnceCount%],
  SUM(UsedOnceMB),
  CAST(1.* SUM(UsedOnceMB)/SUM(TotalMB) AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
ORDER BY TotalMB DESC
22 фев 13, 15:10    [13967402]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
cacheobjtype objtype TotalMB AvgKB Count AvgUseCount UsedOnceCount UsedOnceCount% UsedOnceMB UsedOnceMB%
Total Total 7874.56 154.130000 52317 2515 17991 0.34 3391.48 0.43
Compiled Plan Prepared 6739.19 184.152343 37474 1235 13959 0.37 3076.54 0.46
Compiled Plan Adhoc 933.63 80.799804 11832 66 2509 0.21 307.26 0.33
Parse Tree View 158.10 117.742187 1375 14 0 0.00 0.00 0.00
Compiled Plan Proc 42.25 376.208007 115 13 23 0.20 5.84 0.14
Compiled Plan Stub Adhoc 0.48 0.332031 1493 1 1493 1.00 0.48 1.00
Parse Tree UsrTab 0.23 120.000000 2 28 0 0.00 0.00 0.00
Compiled Plan Trigger 0.14 72.000000 2 2 1 0.50 0.07 0.50
Parse Tree Check 0.13 16.000000 8 10 2 0.25 0.03 0.23
Extended Proc Proc 0.10 8.000000 13 6499598 1 0.08 0.01 0.10

Такая ситуация считается нормальной ?

К сообщению приложен файл. Размер - 64Kb
22 фев 13, 15:22    [13967504]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
stavgreengo
max server memory (MB) 88000
из них 74,6Гб отдано под буферный и 7,98Гб под процедурные кэши
По идее, при таком количестве памяти, максимум для процедурного кэша - 10.4Гб, так что вы почти подошли к лимиту.
22 фев 13, 22:30    [13969282]     Ответить | Цитировать Сообщить модератору
 Re: 1С и процедурный кэш  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
stavgreengo,

43% процедурного кэша занято запросами которые выполнились всего 1 раз, я не думаю что это очень хорошо. Вопрос в том, можно ли что-то сделать. Я просто не много знаю про 1С-ку. Или там реально очень много AdHoc запросов, или же они просто не параметризируются. Если так, то вряд ли что-то можно сделать и проще тупо забить на счетчик hit rates :)
22 фев 13, 22:45    [13969337]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить