Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Сильно тупит база  [new]
Быдло___кодер
Guest
имеем MSSQL Server 2012. На серваке 128 гиг оперативы и 16 ядерный Xeon
Когда с базой работают 10-20 пользователей все летает. В реальном режиме эксплуатации (несколько тысяч активных пользователей, генерящих по 1 запросу / сек) все умирает

Что было исследовано:
1. Посмотрели диспетчер задач, все ядра нагружены на 100%
Использование памяти 50 гиг из 128. Запрос
select cntr_value/1024 [memory, mb] from sys.sysperfinfo 
where counter_name = 'Total Server Memory (KB)'

Возвращает вообще 38 гиг
Настройки буферного кеша - по умолчанию
2. Отсюда
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
взял запрос на события ожидания
2 топ событий ожидания CXPACKET 46% и RESOURCE_SEMAPHORE 36%

3. Запросом

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads


Выявили что бОльшую часть времени база занимается 6 запросами. Каждый из 6 запросов выполняется в среднем более чем по минуте


Вопросы:
1. Правильная ли стратегия выявления проблемных мест?
2. Почему база не использует всю оперативную память? RESOURCE_SEMAPHORE говорит об ожидании выделения памяти под запрос, но свободной памяти то еще немерянно
3. Стоит ли ставить MDOP = 1?
4. Верно ли я понимаю, все усилия следует приложить на тюнинг проблемных запросов. Есть ли какие то общие стратегии тюнинга, например скормить каждый запрос Tuning Adviserу и применить все его советы
5. Индексы на таблицах в запросах сильно фрагментированы. Поможет ли перебилд индексов?
15 июн 17, 08:59    [20565661]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Быдло___кодер,

RESOURCE_SEMAPHORE 36%, это плохо, оч плохо. Это говорит о memory pressure. Если у вас стандарт, то макс 64 GB оперативы.
Ну вы посмотрели статичтику ожиданий, и? Она одна ниочем не говорит. Чтобы убедиться что у вас проблемы с памятью посмотрите счетчик PLE и memory grants pending, посмотрите DMV memory_grants и resource_semaphores для начала, а там видно будет.
15 июн 17, 09:14    [20565680]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
Быдло___кодер,

1. Нашли источник проблем, значит правильная
2. Видимо, потому что не нужно, и скорее всего дело в CXPACKET
3. Да, это скорее всего решит проблему. Есть давний косяк, из-за которого запрос блокирует сам себя при параллелизме (обычно при insert). И не может разрешить deadlock, тк процесс один и тот же
4. Не надо верить всему, что советуют помощники. Сначала пункт 3, потом тюнинг запросов, если потребуется
5. В периоды низкой нагрузки это нужно делать по расписанию
15 июн 17, 09:17    [20565684]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Быдло___кодер,

И по поводу CXPACKET, люди из за этого типа часто на крайние меры идут, ставя MAXDOP 1, не нужно, там же на sqlskills у Пола есть 2 статьи по этому типу, это как может быть проблемой, так и нет. Но если у вас OLTP система такого значения не должно быть, смотрите свои неоптимизированные запросы, котоые нужно так много памяти и которые превышают cost treshold for parallelism.
15 июн 17, 09:18    [20565687]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
Быдло___кодер,

Дополнение к последнему пункту: есть скрипт в тч в MSDN, который в зависимости от фрагментации делает дефраг или ребилд, лучше его использовать
15 июн 17, 09:20    [20565689]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
TaPaK
Member

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

автор
4. Не надо верить всему, что советуют помощники. Сначала пункт 3, потом тюнинг запросов, если потребуется

да у вас это самое правильное, потому что всё остальное у вас бред.

2 Быдло___кодер ищите того кто жрёт вашу память. dmv указанные aleksrov посмотрите и например https://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/
15 июн 17, 09:20    [20565690]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Сид
Быдло___кодер,

3. Да, это скорее всего решит проблему. Есть давний косяк, из-за которого запрос блокирует сам себя при параллелизме (обычно при insert). И не может разрешить deadlock, тк процесс один и тот же


Давние косяк которые встречается оч редко и который обнаружить оч легко и граф такого дедлока ни с чем не перепутать
15 июн 17, 09:23    [20565696]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

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

На OLTP как раз параллелизм в большинстве случаев вообще не нужен. А если и нужен, лучше в запросе прописать option (maxdop N). Например, на тяжёлых выборках со сканами.
15 июн 17, 09:24    [20565698]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

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

Бред или не бред - это будет понятно по результату: поможет или нет. А то тут количество экстрасенсов на квадратный метр зашкаливает
15 июн 17, 09:27    [20565704]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
aleksrov
Member

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

На OLTP как раз параллелизм в большинстве случаев вообще не нужен. А если и нужен, лучше в запросе прописать option (maxdop N). Например, на тяжёлых выборках со сканами.


Пишите нормально запросы, правильно проектируйте систему и ничего указывать не нужно. Любой хинт чаще всего это зло.
15 июн 17, 09:28    [20565706]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
TaPaK
Member

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

Бред или не бред - это будет понятно по результату: поможет или нет. А то тут количество экстрасенсов на квадратный метр зашкаливает

угу у меня CXPACKET 52% я прям в печали. Поставлю MAXDOP 1 куда нибудь.
15 июн 17, 09:31    [20565712]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

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

Так и слышу: перепроектируй систему, которой сто лет в обед, прям под нагрузкой 1000+ пользователей))) вот это реально бредовый совет
15 июн 17, 09:31    [20565713]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

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

Очень рад, что 52% не вызывают проблем. И даже 90% может нормально работать. А потом хренак, и встанет колом в self deadlock, когда надо будет вставить хотя бы несколько тыс записей, и в самый неподходящий момент))
15 июн 17, 09:39    [20565736]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
А статистику то хоть обновляли ?
15 июн 17, 09:40    [20565739]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
TaPaK
Member

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

Очень рад, что 52% не вызывают проблем. И даже 90% может нормально работать. А потом хренак, и встанет колом в self deadlock, когда надо будет вставить хотя бы несколько тыс записей, и в самый неподходящий момент))

вы теперь свой единственно увиденный дедлок всем впариваете? Ну и % для некоторых это наверное непостижимое понятие.
15 июн 17, 09:42    [20565747]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Быдло___кодер
Guest
А статистику то хоть обновляли ?

Сделал вчера UPDATE STATISTICS WITH FULLSCAN для всех таблиц что в глючных запросах, и заодно ALTER INDEX ALL REBUILD для самых тяжелых таблиц

Если у вас стандарт, то макс 64 GB оперативы.

Похоже что так, но он юзает 39 гигов, у него до лимита еще целых 25 гигов

Насчет оптимизации запросов. Самый глючный запрос в хранимке, куда скармливается строка сортировки, фильтры по полям и настрока разбиения по страницам. Монстрообразный запрос схематично можно представить так:

SELECT t.<колонки>, hist.<колонки>, <колонки>,
          count(*) partition by () as CNT,
FROM HUGE_MasterTable t
JOIN HUGE_MasterTableHistory hist
ON t.ActualHistId = hist.HistId
JOIN HUGE_AnotherTable t1
ON t.AnotherId = t1.AnotherId
JOIN HUGE_AnotherTableHistory t2
ON t1.AnotherId = t2.AnotherId AND t2.Active = 1
JOIN еще много таблиц от 1 до 30000 записей 
WHERE t.Param1 = @Param1 AND hist.Param2 = @Param2 and
 (  (@Param3 = 1 AND hist.Param3Detail in (1,2,3,4) OR
    (@Param3  = 2 AND hist.Param2Detail in (....)
...
)
AND (@Filter1 Is NULL OR t.Param5 = @Filter1) 
AND (@Filter2 IS NULL OR hist.Param6 = @Filter2)
....
ORDER BY
 CASE @SortField
   WHEN 'a' then order by t.A
   WHEN 'b' THEN order by hist.b
   WHEN 'c' THEN order by t1.c
.......
END
OFFSET @p_PageSize * @p_PageNo ROWS
FETCH NEXT @p_PageSize ROWS ONLY


Какие шаги предприняты:
1. В HUGE_MasterTable перенес колонки которые нужны из HUGE_MasterTableHistory, таким образом исключив деталь из запроса
2. В хранимке написал 2 отдельных варианта запроса:
когда не передали ни фильтрацию ни сортировку:
  ;with dt as 
  (SELECT ID
   FROM HUGE_MasterTable t
   WHERE t.Param1 = @Param1 AND t.Param2 = @Param2 AND (t.Param3 = @Param3 OR @Param3 IS NULL)
   ORDER BY t.ID
   OFFSET @p_PageSize * @p_PageNo ROWS
   FETCH NEXT @p_PageSize ROWS ONLY)
  cnt as (select COUNT(*) as CNT from HUGE_MasterTable t
   WHERE t.Param1 = @Param1 AND t.Param2 = @Param2 AND (t.Param3 = @Param3 OR @Param3 IS NULL)
   ORDER BY t.ID
   OFFSET @p_PageSize * @p_PageNo ROWS
   FETCH NEXT @p_PageSize ROWS ONLY)
  SELECT t.<колонки>, hist.<колонки>, <колонки>,
          cnt.cnt as CNT,
  FROM HUGE_MasterTable t
  JOIN dt
  ON t.ID = dt.ID 
  ......


когда не передали ни сортировки ни фильтрации по таблице HUGE_AnotherTable та же идея но в with пронес условия фильтрации

в остальных случаях старый монстрообразный запрос

При переписывании руководствовался этой статьей https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

3. Добавлен индекс по Param1, Param2, Param3
Добавлен индекс по Param1, Param2 и с инклудом почти всех полей HUGE_MasterTable (посоветовал адвайзер)
15 июн 17, 11:10    [20566033]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

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

Почему ж единственный, их было найдено несколько, при том на весьма простых операциях, которые без параллелизма работают практически мгновенно, а параллельный план, выбранный скулем, всё ставил колом.
Может, кто расскажет лучше, чем так хорош maxdop>1 на OLTP? Если всё так уж хорошо оптимизировано, то скуль и не будет выбирать параллельный план. Вопрос: какой смысл оптимизатору хотя бы предполагать, что план может быть параллельным? Жду вразумительный и логичный ответ
15 июн 17, 11:12    [20566044]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
TaPaK
Member

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

Почему ж единственный, их было найдено несколько, при том на весьма простых операциях, которые без параллелизма работают практически мгновенно, а параллельный план, выбранный скулем, всё ставил колом.
Может, кто расскажет лучше, чем так хорош maxdop>1 на OLTP? Если всё так уж хорошо оптимизировано, то скуль и не будет выбирать параллельный план. Вопрос: какой смысл оптимизатору хотя бы предполагать, что план может быть параллельным? Жду вразумительный и логичный ответ

судя по постановке вопроса читайте сразу итого
https://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
15 июн 17, 11:18    [20566075]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Быдло___кодер,

оптимизация путём выноса в cte это вообще не оптимизация
15 июн 17, 11:19    [20566079]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
Быдло___кодер,

Запрос с OR в условии требует 1 из 2 вариантов:
1) переписать запрос через union all, чтобы избавиться от OR
2) в конце запроса вставить option(recompile). Минус в том, что план каждый раз будет строиться заново. Плюс - в том, что оптимизатор исключит из работы те условия, которые не удовлетворяют конкретному значению @paramN.

По п.2 так и жду противников хинтов, которые своими экстрасенсорными способностями исправят производительность прямо на продакшне))

И пункт 3 - хорошо бы избавиться от курсора
15 июн 17, 11:22    [20566091]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
aleksrov
Member

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

Советчик. OFFSET FETCH от курсора отличить не может.
Recompile можно добавить, но если запрос и правда такой сложный с кучей кучей join можно оч. долго ждать пока он будет каждый раз строить план.
15 июн 17, 11:34    [20566144]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
TaPaK
Member

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

Советчик. OFFSET FETCH от курсора отличить не может.
Recompile можно добавить, но если запрос и правда такой сложный с кучей кучей join можно оч. долго ждать пока он будет каждый раз строить план.

можно пробовать переназначить переменные внутри запроса, это на 100% не замена recompile но от parameter sniffing спасает
15 июн 17, 11:38    [20566155]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
aleksrov
Member

Откуда:
Сообщений: 948
TaPaK
aleksrov
Сид,

Советчик. OFFSET FETCH от курсора отличить не может.
Recompile можно добавить, но если запрос и правда такой сложный с кучей кучей join можно оч. долго ждать пока он будет каждый раз строить план.

можно пробовать переназначить переменные внутри запроса, это на 100% не замена recompile но от parameter sniffing спасает


А как это должно помочь? Это не сарказм, правда немного непонятно, как в таком случае оптимизатор будет работать.
15 июн 17, 11:45    [20566177]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
Быдло___кодер
Guest
можно пробовать переназначить переменные внутри запроса, это на 100% не замена recompile но от parameter sniffing спасает


Можно подробнее что вы имеете ввиду?
15 июн 17, 11:46    [20566181]     Ответить | Цитировать Сообщить модератору
 Re: Сильно тупит база  [new]
o-o
Guest
Сид
aleksrov,

На OLTP как раз параллелизм в большинстве случаев вообще не нужен. А если и нужен,
лучше в запросе прописать option (maxdop N). Например, на тяжёлых выборках со сканами.

чтобы он до завтра что ли сканил?
как раз в таких и надо параллелить.
другое дело, откуда в OLTP большой скан?
индексы поубивали что ли?
так запрос надо переписывать или над индексами подумать,
а не истреблять параллелизм.
если уже пошел сканить, то и с MAXDOP 1 будет сканить.
просто в N раз дольше
15 июн 17, 11:52    [20566210]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить