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

Откуда: Саратов
Сообщений: 1140
Всем привет! Подскажите пожалуйста, как и когда правильно обслуживать статистику в OLTP системе?

Система Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Aug 15 2017 10:23:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

В системе AlweysOn из 2-ух машин, пишем на одной читаем на другой, синхронный режим доступности. В базе включены параметры автоматического создания и пересчёта статистики.

Никак не могу подобрать правильную комбинацию плана обслуживания статистики...
Пробовал так
1. sp_updatestats
2. Перекомпиляция процедур (sp_recompile для всех процедур)

После выполнения этих действий сервер начинает работать заметно быстрее (Zabix не даст соврать). Поставил эти задачи на автовыполнение с интервалом 1 час, в основном система стала работать так, как и ожидалось "заметно быстрее" после выполнения процедур, НО переодически, после выполнения этих задача результат получаю с точностью до наоборот, сервер начинает жутко тормозить, съедает всё процессорное время. Пока он тупит и тормозит я вижу что хранимые процедуры, которые в обчном режиме выполняются моментально, работают по несколько десятков минут. Лечиться так - kill всем зависшим процесcам, DBCC FREEPROCCACHE и опять полёт нормальный как нивчём не бывало. Кто-нибудь сталкивался с подобной ситуацией?
28 май 18, 12:21    [21446013]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
Mandarin
Пробовал так
1. sp_updatestats
2. Перекомпиляция процедур (sp_recompile для всех процедур)
и вы понимаете, что при этом происходит?
это я вот к этому
Mandarin
Лечиться так - kill всем зависшим процесcам, DBCC FREEPROCCACHE и опять полёт нормальный как нивчём не бывало.
про статистики нормально написано в доке
28 май 18, 13:18    [21446344]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 49508
Mandarin
Никак не могу подобрать правильную комбинацию плана обслуживания статистики...
Пробовал так
1. sp_updatestats
2. Перекомпиляция процедур (sp_recompile для всех процедур)

Зачем? Если в системе количество данных стабильно - нет смысла пересчитывать неизменяющуюся статистику.
28 май 18, 13:33    [21446447]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Dimitry Sibiryakov
Mandarin
Никак не могу подобрать правильную комбинацию плана обслуживания статистики...
Пробовал так
1. sp_updatestats
2. Перекомпиляция процедур (sp_recompile для всех процедур)

Зачем? Если в системе количество данных стабильно - нет смысла пересчитывать неизменяющуюся статистику.


Система достаточно динамична, данные там изменяются круглосуточно.
28 май 18, 14:29    [21446774]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Дедушка
Mandarin
Пробовал так
1. sp_updatestats
2. Перекомпиляция процедур (sp_recompile для всех процедур)
и вы понимаете, что при этом происходит?
это я вот к этому
Mandarin
Лечиться так - kill всем зависшим процесcам, DBCC FREEPROCCACHE и опять полёт нормальный как нивчём не бывало.
про статистики нормально написано в доке


Спасибо за ссылку.
28 май 18, 14:29    [21446777]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Владислав Колосов
Member

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

только не подумайте, что это лечение, это приём болеутоляющих.
28 май 18, 14:41    [21446829]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Владислав Колосов
Mandarin,

только не подумайте, что это лечение, это приём болеутоляющих.


Подскажите направление на лечение :)
28 май 18, 14:56    [21446930]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mind
Member

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

только не подумайте, что это лечение, это приём болеутоляющих.


Подскажите направление на лечение :)
Направление - Parameter Sniffing

Если кратко, то после ваших манипуляций все процедуры перекомпилиреются, если вам не повезет, то процедура первый раз вызывается с "плохими" параметрами и соответственно строится "плохой" план, ну и так до следующей рекомпиляции. Смотреть надо каждый запрос/план индивидуально, общего решения проблемы нет.
29 май 18, 03:38    [21448669]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Mind
Mandarin
пропущено...


Подскажите направление на лечение :)
Направление - Parameter Sniffing

Если кратко, то после ваших манипуляций все процедуры перекомпилиреются, если вам не повезет, то процедура первый раз вызывается с "плохими" параметрами и соответственно строится "плохой" план, ну и так до следующей рекомпиляции. Смотреть надо каждый запрос/план индивидуально, общего решения проблемы нет.


Нам повезёт :) потому, что мы используем подсказку option (optimize for unknown)
29 май 18, 08:15    [21448817]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Col
Member

Откуда: Торонто
Сообщений: 177
Mandarin
Включите флаг 2371, в 2016 эта логика автоапдейта уже включена по умолчанию, если конечо совместимость базы выставлена в 130.
2371 изменяет логику автоапдейта с тупой процентовки на динамику зависящую от колличества измененных строк и процентовку.
Плюс можно статистику упдейтить вручную/динамически, логика автоапдейта примерно совпадает с логикой скрипта ниже.
Естественно перекомпиляции процедур полезут только по тем процедурам что затронуты динамическим апдейтом.


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Store relevant details 
SELECT
	ss.name AS SchemaName
	, st.name AS TableName
	, si.name AS IndexName
	, ssi.rowcnt
INTO #IndexUsage
FROM sys.indexes si
INNER JOIN sys.sysindexes ssi ON si.object_id = ssi.id
					AND si.name = ssi.name
INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE st.is_ms_shipped = 0	-- Only application indexes
	AND si.index_id != 0	-- Ignore heaps
	AND ssi.rowcnt > 100	-- Only indexes with at least 100 rows
	AND ssi.rowmodctr > 0	-- Only indexes with changed data
	
-- Build Update Statistics SQL (concatenated)	
DECLARE @UpdateStatisticsSQL NVARCHAR(MAX)
SET @UpdateStatisticsSQL = ''

SELECT @UpdateStatisticsSQL = @UpdateStatisticsSQL
		+ CHAR(10) + 'UPDATE STATISTICS '
		+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
		+ ' ' + QUOTENAME(IndexName) + ' WITH SAMPLE '
		+ CASE
			WHEN rowcnt < 500000 THEN '100 PERCENT'
			WHEN rowcnt < 1000000 THEN '50 PERCENT'
			WHEN rowcnt < 5000000 THEN '25 PERCENT'
			WHEN rowcnt < 10000000 THEN '10 PERCENT'
			WHEN rowcnt < 50000000 THEN '2 PERCENT'
			WHEN rowcnt < 100000000 THEN '1 PERCENT'
			ELSE '3000000 ROWS '
		END
		+ '-- ' + CAST(rowcnt AS VARCHAR(22)) + ' rows'
FROM #IndexUsage

-- Debug
DECLARE @StartOffset INT
DECLARE @Length INT

SET @StartOffset = 0
SET @Length = 4000

WHILE (@StartOffset < LEN(@UpdateStatisticsSQL))
BEGIN
	PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
	SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)

-- Execute Update Statistics.
EXECUTE sp_executesql @UpdateStatisticsSQL

-- Tidy up.
DROP TABLE #IndexUsage
29 май 18, 18:36    [21451509]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Col,
Большое спасибо за скрипт!
30 май 18, 09:46    [21452596]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mandarin
Member

Откуда: Саратов
Сообщений: 1140
Вопрос возник с новой силой :)
Поправьте меня если я ошибаюсь
1. При обновлении статистики, статистика сначала удаляется, потом создаётся снова, это я понял из описании опции incremental
2. При включенном AlweysOn, время между удалением статистики и её созданием, на читающей реплике, может быть достаточно продолжительным, например когда много данных синхронизируется.
3. Когда статистика удалена и ещё не создана, запросы, которые выполняются на читающей реплике, будут тормозить, потому что выполняются без статистики.
И как раз получается такая ситуация как я описал в начале этого топика - тормоза на читающей реплике.
Если я прав, то у меня вопрос как обновлять статистику без удаления?
p.s. incremental не предлагать, т.к. он не поддерживается в 2012 версии.
14 июн 18, 16:02    [21491384]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Mandarin
1. При обновлении статистики, статистика сначала удаляется, потом создаётся снова, это я понял из описании опции incremental
В документации описана логика процесса, а не физика. К тому же перевод не совсем корректный. Под "удаляется" следует понимать, что статистика целиком заменяется на новую.
Mandarin
3. Когда статистика удалена и ещё не создана, запросы, которые выполняются на читающей реплике, будут тормозить, потому что выполняются без статистики.
Для выполнения запроса статистика не требуется. Она нужна при компиляции.
14 июн 18, 18:50    [21491902]     Ответить | Цитировать Сообщить модератору
 Re: Как и когда правильно обслуживать статистику в OLTP системе?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mandarin
1. При обновлении статистики, статистика сначала удаляется, потом создаётся снова
Приведите хотя бы одну логическую причину почему разработчики SQL Server-a могли бы это так реализовать?

Не ищите проблем там где их нет. Лучше достаньте план выполнения процедур(ы) в момент когда все тормозит и сравните с обычным.
14 июн 18, 20:43    [21492214]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить