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

Откуда:
Сообщений: 22
Доброго времени суток.
Столкнулся с проблемой, когда для получения оптимального плана запроса, мне пришлось создать фильтрованную статистику.
если кому интересен пример, то похожая проблема описана здесь http://stackoverflow.com/questions/31101773/case-of-using-filtered-statistics

но меня интересует другое. есть ли подводные камни в фильтрованной статистике?
спасибо.
15 ноя 16, 15:04    [19896276]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

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

странный подход, вы как будто стесняетесь :)
автор
мне пришлось создать фильтрованную статистику.

статистика для того и создана что бы помогать оптимизатору, единственное что для отфильтрованных возможно надо обновлять чаще чем срабатывает автоматическое обновление
15 ноя 16, 16:32    [19896842]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Kos_Da
Member

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

спасибо!

просто был неудачный пример с созданием план гайда, а потом разработчики удалили индекс. а запрос продолжил работать, но возвращал просто пустоту вместо того чтобы упасть и выдать ошибку. хорошо хоть это отловилось на тесте, когда перестал отрабатывать бизнес-процесс. после этого поставил в расписание проверку правильности план гайдов.

вот и с фильтрованной статистикой боюсь о что-нибудь споткнуться.
16 ноя 16, 11:11    [19899139]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

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

а запрос продолжил работать, но возвращал просто пустоту вместо того чтобы упасть и выдать ошибку.
эээ что?
16 ноя 16, 11:17    [19899175]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Владислав Колосов
Member

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

Вы сказки рассказываете, если меняется какое-то условие или структура данных, руководство планов не использует план. Там что-то другое было.
16 ноя 16, 11:33    [19899253]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
Kos_Da
есть ли подводные камни в фильтрованной статистике?

У нас был такой случай на SQL Server 2008: большая таблица фактов, секционированная по дням.
Для каждой секции по колонке секционирования ( которая одновременно была ключом кластерного индекса ) построили фильтрованные статистики.
Дак вот, если запрос должен вернуть данные более чем одной секции( грубо говоря объединить статистики ), то эти фильтрованные статистики просто игнорировались.
16 ноя 16, 11:50    [19899366]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

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

автор
грубо говоря объединить статистики

есть две гистограммы... с 200 разделами... объедение их, на словах хотя бы
16 ноя 16, 11:53    [19899386]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Kos_Da
Member

Откуда:
Сообщений: 22
вот делать мне больше нечего, как сказки рассказывать.
сейчас на всякий случай проверил - отключил индекс, который используется в план гайде.
выполнил запрос. вместо возвращения данных, он вернул просто "Command(s) completed successfully".
после включения индекса обратно, запрос вернул запрошенные данные.
16 ноя 16, 12:07    [19899454]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Kos_Da
вот делать мне больше нечего, как сказки рассказывать.
сейчас на всякий случай проверил - отключил индекс, который используется в план гайде.
выполнил запрос. вместо возвращения данных, он вернул просто "Command(s) completed successfully".
после включения индекса обратно, запрос вернул запрошенные данные.

тут без комментариев.
16 ноя 16, 12:11    [19899468]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
o-o
Guest
Kos_Da
просто был неудачный пример с созданием план гайда, а потом разработчики удалили индекс. а запрос продолжил работать, но возвращал просто пустоту вместо того чтобы упасть и выдать ошибку.

BOL
We recommend re-evaluating and testing plan guide definitions when you upgrade your application to a new release of SQL Server.
Performance tuning requirements and plan guide matching behavior may change.
Although an invalid plan guide will not cause a query to fail,
the plan is compiled without using the plan guide
and may not be the best choice.

ну и для тех, кто поверил в сказочки:
в первом окне создаю plan guide для запроса
select * 
from Person.Contact
where EmailAddress = 'gustavo0@adventure-works.com'

в нем поиск по индексу IX_Contact_EmailAddress, на выходе 1 строка.
во втором окне
ALTER INDEX [IX_Contact_EmailAddress] ON [Person].[Contact] DISABLE;

план строится без использования plan guide и там full scan.
на выходе 1 строка.
не только оцененная, но и реальная.

К сообщению приложен файл. Размер - 92Kb
16 ноя 16, 13:13    [19899765]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Kos_Da
Member

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

в план гайде создайте хинт использовать индекс, который потом выключите.
16 ноя 16, 13:32    [19899856]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
TaPaK
объедение их, на словах хотя бы

Чтобы понять, сколько строк вернет запрос, загляни в две гисторгаммы по фильтрованным статистикам.

В чем подвох?
16 ноя 16, 13:37    [19899885]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Pavel1211
TaPaK
объедение их, на словах хотя бы

Чтобы понять, сколько строк вернет запрос, загляни в две гисторгаммы по фильтрованным статистикам.

В чем подвох?

в том что вы не понимаете что такое статистика
16 ноя 16, 13:40    [19899902]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Kos_Da
Member

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

не поленился, создал тестовый сценарий. попробуйте.

set nocount on
create table test(id int, detail int,typeid uniqueidentifier)
create nonclustered index nci_typeid on test(typeid)
go

declare @i int =1
while @i <= 1000 begin
insert test values (0, @i,'FE180EB8-22EB-4395-B51C-12203A8EC84F')
set @i = @i + 1
end

set @i=0
while @i <= 1000 begin
insert test values (1, @i,'CBAACF6D-ECCD-41DD-9856-D3ACBCF4BCE4')
set @i = @i + 1
end
go

EXEC sp_create_plan_guide @name = N'[test-plan]', @stmt = N'select id,detail from test where typeid=@P1',
@type = N'SQL',
@module_or_batch = N'select id,detail from test where typeid=@P1',
@params = N'@P1 uniqueidentifier',
@hints = N'OPTION ( TABLE HINT ( test,INDEX (nci_typeid) ) )'
GO

exec sp_executesql N'select id,detail from test where typeid=@P1',N'@P1 uniqueidentifier',@P1='FE180EB8-22EB-4395-B51C-12203A8EC84F'

ALTER INDEX nci_typeid ON [dbo].[test] DISABLE;

exec sp_executesql N'select id,detail from test where typeid=@P1',N'@P1 uniqueidentifier',@P1='FE180EB8-22EB-4395-B51C-12203A8EC84F'

ALTER INDEX nci_typeid ON [dbo].[test] REBUILD;

exec sp_executesql N'select id,detail from test where typeid=@P1',N'@P1 uniqueidentifier',@P1='FE180EB8-22EB-4395-B51C-12203A8EC84F'
16 ноя 16, 13:57    [19899983]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

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

воистину, был не прав. Судя по всему используйте sys.fn_validate_plan_guide ( ) для проверки валидности прибитых планов
16 ноя 16, 14:06    [19900043]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
или ловите Plan Guide Unsuccessful
16 ноя 16, 14:08    [19900054]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
o-o
Guest
Kos_Da
o-o,

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

офигизм какой-то
в случае гайда с готовым планом, если индекс задизэйблен или его нет,
строится новый план и результат выдается.

в случае гайда с хинтом в тех же условиях выдается
SSMS
Command(s) completed successfully.

плана никакого нет, результата нет,
в профайлере ловится событие PLAN GUIDE UNSUCCESSFUL
и полное отсутствие SHOWPLAN XML, SHOWPLAN XML STATISTICS PROFILE

К сообщению приложен файл. Размер - 94Kb
16 ноя 16, 14:20    [19900138]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9168
Похоже, что sp_executesql некорректно работает в этом случае

exec sp_executesql N'select id,detail from test where typeid=@P1',N'@P1 uniqueidentifier',@P1='FE180EB8-22EB-4395-B51C-12203A8EC84F'

т.к.

select id,detail from test where typeid='FE180EB8-22EB-4395-B51C-12203A8EC84F'

возвращает результат, как и ожидается.
16 ноя 16, 14:28    [19900184]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
o-o
Guest
Kos_Da
не поленился, создал тестовый сценарий. попробуйте.

у меня и со своими таблицами воспроизвелось.
мой первый пример был с гайдом без хинта:
я генерю нужный план, выцепляю его из кэша,
по нему строится гайд с использованием нужного индекса.
потом индекс дизэйблю,
результат выдается, новый план строится.
в профайлере тот же PLAN GUIDE UNSUCCESSFUL,
но и план тоже есть, не гайдовый, новый

К сообщению приложен файл. Размер - 41Kb
16 ноя 16, 14:30    [19900202]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
f070214f
Member

Откуда:
Сообщений: 13
Интересно, что Profiler ловит Exception, но почему-то эта ошибка не передается в сессию, выполняющую sp_executesql.
16 ноя 16, 14:31    [19900209]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
o-o
Guest
Владислав Колосов
Похоже, что sp_executesql некорректно работает в этом случае

exec sp_executesql N'select id,detail from test where typeid=@P1',N'@P1 uniqueidentifier',@P1='FE180EB8-22EB-4395-B51C-12203A8EC84F'

т.к.

select id,detail from test where typeid='FE180EB8-22EB-4395-B51C-12203A8EC84F'

возвращает результат, как и ожидается.

в моем примере нет sp_executesql(см. первую картинку),
и текст запроса просто скопирован из
select *
from  sys.plan_guides

чтобы точно соответствовать.
вы вот тоже оттуда скопипасьте, все получится
16 ноя 16, 14:33    [19900218]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Владислав Колосов
Member

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

У Вас прослушивание параметров в базе простое или принудительное?

При отключении кластеризованного индекса реакция меняется:

set nocount on 
create table test(id int, detail int, typeid uniqueidentifier) 
create clustered index nci_typeid on test(typeid) 
go 

declare @i int =1
while @i <= 1000 begin 
insert test values (0, @i,'FE180EB8-22EB-4395-B51C-12203A8EC84F') 
set @i = @i + 1 
end 

set @i=0
while @i <= 1000 begin 
insert test values (1, @i,'CBAACF6D-ECCD-41DD-9856-D3ACBCF4BCE4') 
set @i = @i + 1 
end 
go


EXEC sp_create_plan_guide @name = N'[test-plan]', @stmt = N'select id,detail from test where typeid=@P1', 
@type = N'SQL',
@module_or_batch = N'select id,detail from test where typeid=@P1', 
@params = N'@P1 uniqueidentifier', 
@hints = N'OPTION ( TABLE HINT ( test,INDEX (nci_typeid) ) )'
GO


ALTER INDEX nci_typeid ON [dbo].[test] DISABLE;
GO

select id,detail from test where typeid='FE180EB8-22EB-4395-B51C-12203A8EC84F'
GO


Сообщение 8655, уровень 16, состояние 1, строка 1
Обработчику запросов не удалось предоставить план, потому что индекс "nci_typeid" таблицы или представления "test" отключен.
16 ноя 16, 14:55    [19900311]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
o-o
Guest
при чем тут параметризация,
если вы отключили КЛАСТЕРНЫЙ?
и нет ни одного некластерного, содержащего данные?
тут хоть с гайдом, хоть без, план построить невозможно: отсутствует сама таблица
16 ноя 16, 15:03    [19900342]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
вообщем, оказывается :), поведение стандартное, только event и всё...
все проверяют планы, чем то типа засунутого в джоб или т.п.

SELECT plan_guide_id, msgnum, severity, state, message  
FROM sys.plan_guides  
CROSS APPLY fn_validate_plan_guide(plan_guide_id);  
16 ноя 16, 15:16    [19900408]     Ответить | Цитировать Сообщить модератору
 Re: фильтрованная статистика и возможный вред  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9168
o-o
при чем тут параметризация,
если вы отключили КЛАСТЕРНЫЙ?
и нет ни одного некластерного, содержащего данные?
тут хоть с гайдом, хоть без, план построить невозможно: отсутствует сама таблица


Да, логично. Но Ваш пример я не могу воспроизвести за неимением тестовой базы.
Самописный же пример у меня возвращает данные при отключении индекса, как я писал выше.
16 ноя 16, 15:31    [19900470]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить