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

Откуда: Moscow
Сообщений: 2480
Блог
сервер
Microsoft SQL Server 2005 - 9.00.4211.00 (Intel X86) Jan 30 2009 13:43:44 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Всем привет!
Опишу порядок действий и собственно вопросы, наверняка кто-нибудь знает.

Резко увеличилось время выполнения ХП. Увеличилось так, что при вызове ее с клиента - истекает таймаут. Отловил вызов ХП в профайлере, запустил ту же процедуру в среде мэнеджмент студио 2008.
Там ХП отрабатывает "на ура" за полсекунды.
Далее, при помощи
sys.dm_exec_sql_text(...)
и
sys.dm_exec_query_plan(...)

Вытащил из кэша планы выполнения, как оказалось,во первых, они разные (причем используются стабильно, один - для вызова из среды SSMS 2008, другой - для вызова через компоненты ADO.NET с клиента, проверил это опытным путем по кол-ву использований того и другого плана).
Счетчик использований второго плана, был довольно большим, т.е. я так понял это значит что план используется давно.
Перекомпилировал процедуру, чтобы сбросить кэш. Планы пересоздались. Все заработало нормально!

В связи с этим есть два вопроса:
1) Интересно, почему генерируются разные планы в зависимости от способа вызова ХП?
(причем если из среды вызвать несколько раз ХП - то вызовы не приводят к созданию новых планов выполнения, как только вызываешь через компоненты доступа - нате вам второй план)
2) Более важный вопрос. Как избежать такого "слетания" запросов в будущем? М.б. периодически пересобирать скриптом все ранимки и функции и т.д.? Кто что посоветует?
8 сен 09, 18:06    [7633343]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
--------------------
Guest
А обращения к объектам идут с указанием схемы?
Типа dbo.tab1 ?
Если нет, то попробуйте указать явно
8 сен 09, 18:37    [7633528]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Да, с явным указанием схемы.
8 сен 09, 18:40    [7633546]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Сравните результаты sys.dm_exec_plan_attributes для обоих случаев - наверняка параметры сессий отличаются...

Слетают по причине "протухшей" статистики. Рекомендуется перестраивать ежедневно, а та и ежечасно (если в таблицах меняется существенный % по отношению к общему количества записей в течении дня)
8 сен 09, 19:16    [7633713]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Вот вывел переметры запорсом:
select * 
from 
	sys.dm_exec_plan_attributes(0x05000700074AF875B8C12213000000000000000000000000) a1
	inner join sys.dm_exec_plan_attributes(0x05000700074AF875B8C12213000000000000000000000000) a2
on 
	(a1.attribute = a2.attribute)
Результат:
attributevalueis_cache_keyattributevalueis_cache_key
set_options2511set_options2511
objectid19792061511objectid19792061511
dbid71dbid71
dbid_execute71dbid_execute71
user_id11user_id11
language_id01language_id01
date_format11date_format11
date_first71date_first71
status01status01
required_cursor_options01required_cursor_options01
acceptable_cursor_options01acceptable_cursor_options01
inuse_exec_context00inuse_exec_context00
free_exec_context10free_exec_context10
hits_exec_context1560hits_exec_context1560
misses_exec_context40misses_exec_context40
removed_exec_context20removed_exec_context20
inuse_cursors00inuse_cursors00
free_cursors00free_cursors00
hits_cursors00hits_cursors00
misses_cursors00misses_cursors00
removed_cursors00removed_cursors00
sql_handleNULL0sql_handleNULL0

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

Насчет статистики спасибо, поставлю джоб раз в день.
Только разве сервер не должен автоматически обновлять статистику? БД не высокозагруженная, обновляется, добавляется и удаляется не так уже много записей в день...
9 сен 09, 11:15    [7635813]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
окопипастился
select * 
from 
	sys.dm_exec_plan_attributes(0x05000700074AF875B8C12213000000000000000000000000) a1
	inner join sys.dm_exec_plan_attributes(0x05000700074AF875B8417813000000000000000000000000) a2
on 
	(a1.attribute = a2.attribute)
9 сен 09, 11:17    [7635829]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
attributevalueis_cache_keyattributevalueis_cache_key
set_options2511set_options43471
objectid19792061511objectid19792061511
dbid71dbid71
dbid_execute71dbid_execute71
user_id11user_id11
language_id01language_id01
date_format11date_format11
date_first71date_first71
status01status01
required_cursor_options01required_cursor_options01
acceptable_cursor_options01acceptable_cursor_options01
inuse_exec_context00inuse_exec_context00
free_exec_context10free_exec_context00
hits_exec_context1600hits_exec_context70
misses_exec_context40misses_exec_context00
removed_exec_context20removed_exec_context10
inuse_cursors00inuse_cursors00
free_cursors00free_cursors00
hits_cursors00hits_cursors00
misses_cursors00misses_cursors00
removed_cursors00removed_cursors00
sql_handleNULL0sql_handleNULL0

параметры все же отличаются, но насколько я понял отличаются они только количеством использований...хотя что такое set_options.

Сообщение было отредактировано: 9 сен 09, 11:29
9 сен 09, 11:26    [7635886]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
Glory
Member

Откуда:
Сообщений: 104760
set_options 251
set_options 4347
У вас коннекты отличаются набором команд SET
Некоторые из них существенно могут влиять на план
9 сен 09, 11:30    [7635914]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Glory,
понятно, спасибо.

А есть ли какой-то способ гарантировать, что написанная и отлаженная мною процедура в среде SSMS, будет выполнятся таким же образом и при вызове с помощью компонентов ADO?

Смотрел в сторону use plan и Plan Guides, но там насколько я понял необходимо указывать явный план запроса в XML. Т.е. если запрос меняется (что бывает частенько) придется заново генерировать план и указывать новый план в измененной процедуре. Чего делать не хотелось бы, т.к. это повышает накладные рамходы при развитии и поддержке бд, проще говоря геморойно =)

Хотелось бы после того как написал хп и отладил "сказать" серверу, "работает? ну вот по такому плану и выполняй". Чтоб было одинаково, если уж хп отваливается по какой-то причине, чтоб она так же отваливалась если я вызываю ее искусственно, чтоб было легко воспроизвести ошибку.
Без мистики типа руками проверяю - все работает, а с клиента не работает! =)
9 сен 09, 11:55    [7636108]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
step_ks
Member

Откуда:
Сообщений: 936
SomewhereSomehow
Glory,
понятно, спасибо.

А есть ли какой-то способ гарантировать, что написанная и отлаженная мною процедура в среде SSMS, будет выполнятся таким же образом и при вызове с помощью компонентов ADO?

Перед выполнением запроса явно ставить в коннект нужные одинаковые SET-ы.
9 сен 09, 13:56    [7637008]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Здорово! А где это можно сделать? В свойствах комопнентов адо я не нашел такую возможность...
9 сен 09, 14:29    [7637220]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
step_ks
Member

Откуда:
Сообщений: 936
Выполнить в коннекте скрипт, устанавливающий нужные SET
9 сен 09, 14:32    [7637234]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Не подскажешь где посмотреть как можно выполнять "скрипты в коннекте"?
Дело в том что коннект осуществляется при помощи компонента .NET SqlConnection, и как можно "выполнить в нем скрипт" не представляю
9 сен 09, 14:37    [7637266]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
Konst_One
Member

Откуда:
Сообщений: 11514
executenonquery
9 сен 09, 14:38    [7637276]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Это метод другого компонента, SqlCommand.
Или вы имеете в виду что нужно перед вызовом хп, таким же образом, через эти компоненты исполнить другой скрипт?
Кстати какой скрипт? Который возьмет из кэша несколько планов запрсоа, опередил какой из них соответсвует тому что исполняется из среды студии а какой при вызове через компоненты (кстати как он это определит) и потом проадейтит эти свойства? Или предлагается конкретно для этой процедуры прописать жестко эти опции и перед ее вызовом дополнительным запросом проставлять их? И это проделать для каждой из хранимок в бд? =)
9 сен 09, 14:48    [7637349]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
Konst_One
Member

Откуда:
Сообщений: 11514
вам бы в форум по .NET

так делаете?



        Using con As SqlConnection = Main.GetConnect()

           'вот тут можно еще кучу всего повызывать что вам надо

            Using cmd As New SqlCommand
                cmd.CommandText = "ReportEvent"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection = con
                cmd.Parameters.AddRange(parameters)
                cmd.ExecuteNonQuery()
            End Using

        End Using
9 сен 09, 14:51    [7637372]     Ответить | Цитировать Сообщить модератору
 Re: перекомпиляция плана выполнения ХП  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Да примерно так, только на шарпе.
Просто формулировка подкачала - "скрипты в коннекте". Другое дело написать, что в рамках одного коннекта перед вызовом процедуры, исполнить некие скрипты.
В любом случае все вопросы заданные на пост выше - остаются. Не будешь же для каждой процедуры выяснять какой план для чего используется и какие опции ставить.
В этом смысле форум все-таки правильный.

Кстати наткнулся на интересный перевод статьи "Методика, которая гарантирует в SQL Server 2000 выбор определенного плана исполнения запроса", которая прояснила некие моменты о том как именно оптимизатор работает с планами. Буду копать дальше в той области.
В публикациях есть еще много чего есть почитать про оптимизатор =)
9 сен 09, 15:07    [7637493]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить