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

Откуда:
Сообщений: 94
Добрый день
Хочу поделиться интересным наблюдением. В конце будут вопросы.
Итак, я занимаюсь обслуживанием нескольких серверов, на которых стоят MS SQL Server 2012.
И все бы хорошо, но периодически все запросы в скуле начинают долго выыполняться от 5 до 40 сек. Затем опять быстро.
Помогала перезагрузка службы скуля.
Долгими поисками удалось установить следующий код, который с 40 сек делает 0-1 сек выполнения даже тяжелых запросов:

USE <ID Базы данных>;
go

--очищаем кэш по id БД
DBCC FLUSHPROCINDB(<ID Базы данных>);

--обновляем статистику
exec sp_updatestats;

Причем обновлять статистику не обязательно-все равно быстро работает.
А вот теперь вопрос к тому, кто знает, почему так происходит? Получается, что рано или поздно наступают моменты, когда поиск плана из кэша для нужного запроса становится в разы дольше, чем построить новый план для этого запроса.
В книжках и в инете пишут обратное, что кэш помогает (и я так думал, что логично). Но почему-то практика показала обратное.
Запросы тормозили любые-даже тупо вытащить 100 строк из таблицы (даже грязным чтением).
Спасибо за ответы)
20 сен 16, 12:38    [19687007]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ЕвгенийGEM,

может всё таки дело не в поиске в кеше, а в том что в кеше плохие планы, parameter sniffing и тп. Смотрите на планы "долгих запросов"
20 сен 16, 12:41    [19687021]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
ЕвгенийGEM
Member

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

а как понять что планы стали плохими? т е изначально они были хорошие быстро выполнялись, а потом стали плохими?
И что отвечает за их перестроение? Т е как скуль понимает, что план устарел?
Спасибо)
20 сен 16, 20:49    [19689341]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
Mike_za
Member

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

Аам выше написали. Планы не устарели, а неподходящие, читайте про прослушивание параметров
20 сен 16, 21:08    [19689397]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
Сид
Member

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

А сколько памяти выделено SQL Server'y? И сколько при этом всего? Может, он всё использовал, залез в файл подкачки и поэтому тормозит? Кэш тут явно ни при чём.
20 сен 16, 22:54    [19689701]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Сид,
Скл не должен залезатб в файлы подкачки. Он сам себе файл подкачки
20 сен 16, 23:45    [19689783]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
Mr. X
Guest
Mike_za
Сид,
Скл не должен залезатб в файлы подкачки. Он сам себе файл подкачки


Эвона как! Вы это Биллу Гейтсу расскажите :)

ЕвгенийGEM,
Сюда https://www.sql.ru/forum/1080907/maximum-server-memory заглятитеи, если у вас не так, то поправьте. Параметры динамические, рестарт не нужен.
Закоментируйте DBCC FLUSHPROCINDB(<ID Базы данных>); и посмотрите что получится. При обновлении статистики планы, в которых использовалась обновленная статистика, из кэша уйдут. Не надо сливать весь кэшь.
ЗЫ: регулярное обновление статистики это как профилактика гриппа (тока без фанатизма, лучше бы отслеживать какую надо, а какую не надо обновлять. Хотя с 2012 (могу ошибаться) эта хранимка стала умнее.)
21 сен 16, 00:28    [19689869]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
max44
Member

Откуда: МОСКВА
Сообщений: 273
Анализ проблем с "прослушиванием параметров":
http://www.somewheresomehow.ru/fast-in-ssms-slow-in-app-part2/
21 сен 16, 07:00    [19690044]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
вообще, по хорошему, что бы не кидаться во все стороны, посмотрите на ожидания в момент выполнения долгих запросов, может помочь в какую сторону смотреть
21 сен 16, 09:03    [19690168]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
ЕвгенийGEM
Member

Откуда:
Сообщений: 94
Mr. X,

делал обновление статистики без очистки кэша-не помогает. Только после очистки кэша.
21 сен 16, 09:19    [19690196]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
ЕвгенийGEM
Member

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

кэш причем-чистишь и работает в 10 раз быстрее.
Вопрос почему так
21 сен 16, 09:20    [19690198]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
ЕвгенийGEM
Member

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

Да-ожидания становятся больше, т е время выполнения в 2-3 раза дольше, а ожидания в 8-10 раз дольше становятся.
(конкретный пример-Elapsed - 24 сек, Worker - 4 сек вместо по нулям там и там-нули после очистки кэша).
21 сен 16, 09:21    [19690205]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ЕвгенийGEM
TaPaK,

Да-ожидания становятся больше, т е время выполнения в 2-3 раза дольше, а ожидания в 8-10 раз дольше становятся.
(конкретный пример-Elapsed - 24 сек, Worker - 4 сек вместо по нулям там и там-нули после очистки кэша).

Ожидание это не время, а то чего же ждёт запрос, например так для запущенных:

SELECT
        r.session_id
,       r.start_time
,       TotalElapsedTime_ms = r.total_elapsed_time
,       r.[status]
,       r.command
,       DatabaseName = DB_Name(r.database_id)
,       r.wait_type
,       r.last_wait_type
,       r.wait_resource
,       r.cpu_time
,       r.reads
,       r.writes
,       r.logical_reads
,       t.[text] AS [executing batch]
,       SUBSTRING(
                                t.[text], r.statement_start_offset / 2, 
                                (       CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
                                                 ELSE r.statement_end_offset 
                                        END - r.statement_start_offset ) / 2 
                         ) AS [executing statement] 
,       p.query_plan
FROM
        sys.dm_exec_requests r
CROSS APPLY
        sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY     
        sys.dm_exec_query_plan(r.plan_handle) AS p
WHERE 
		r.session_id != @@spid       
ORDER BY 
        r.total_elapsed_time DESC;
21 сен 16, 09:26    [19690210]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование тормозит выполнению запросов  [new]
ЕвгенийGEM
Member

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

огромное спасибо-прочитал, похоже на правду
осталось осмыслить полученные знания и посмотреть на запросы и хранимки с последущим их изменением.
Очистку кэша пока убирать не буду-уж лучше пусть чистит пока не разберусь со всеми тормозами.
21 сен 16, 09:58    [19690359]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить