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

Откуда: ->|<- :адуктО
Сообщений: 21520
SQL2000SP4

Есть процедура, состоящая из одного элементарного запроса:
return (
SELECT    COUNT(ToMsg)
FROM      dbo.SY_MG_Log WITH (NOLOCK)
WHERE     (RecipientID = @Recipient) AND (Priority >= @Priority) AND (ReadDate IS NULL)
)

Вот ее план:
       |--Compute Scalar(DEFINE:([Expr1002]=[Expr1002]))
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1008])))
| |--Stream Aggregate(DEFINE:([Expr1008]=COUNT_BIG([SY_MG_Log].[ToMsg])))
| |--Filter(WHERE:([SY_MG_Log].[Priority]>=[@Priority]))
| |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([SVA_CUR].[dbo].[SY_MG_Log]) WITH PREFETCH)
| |--Index Scan(OBJECT:([SVA_CUR].[dbo].[SY_MG_Log].[IX_SY_MG_Log_1]), WHERE:([SY_MG_Log].[ReadDate]=NULL AND [SY_MG_Log].[RecipientID]=[@Recipient]))
|--Constant Scan


При настройке, отладке и оптимизации она берет около 2000 READ и время около 50ms.
Когда она запускается в рабочем режиме, то внезапно ее READS вырастает до 50000-100000
и время выполнения до 20-40 секунд.

Таблица содержит около 500 тыс записей. Новые записи добавляются в нее (в те времена,
когда я наблюдаю эти страшные цифры) со скоростью 1 шт в 1-5 минут.

Насколько я понимаю, проблема не связана с блокировками, во-первых блокировки не должны
влиять на READS, во-вторых я не наблюдал их в EM, когда во время выполнения процедуры
снимал Activity. Насколько я понимаю, проблема в добавлении новых записей, которые как-то
влияют на выборку. Тут у меня не хватает знаний, куда дальше рыть.
28 сен 09, 23:40    [7717799]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Попробуйте создать индекс с ключем (RecipientID, Priority, ReadDate, ToMsg), а если вместо ToMsg в COUNT хватит и звездочки (*), то не включайте в ключ ToMsg.
29 сен 09, 06:18    [7718032]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Shocker.Pro
Member

Откуда: ->|<- :адуктО
Сообщений: 21520
tpg,

поставил звездочку в Count
сделал индекс на три поля
ща буду мониторить выполнение

       |--Compute Scalar(DEFINE:([Expr1002]=[Expr1002]))
|--Nested Loops(Inner Join)
|--Constant Scan
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1008])))
|--Stream Aggregate(DEFINE:([Expr1008]=Count(*)))
|--Index Seek(OBJECT:([SVA_CUR].[dbo].[SY_MG_Log].[IX_SY_MG_Log_7]), SEEK:([SY_MG_Log].[RecipientID]=[@Recipient] AND [SY_MG_Log].[Priority] >= [@Priority]), WHERE:([SY_MG_Log].[ReadDate]=NULL) ORDERED FORWARD)

P.S. Надеюсь, я вас правильно понял, вы не имели ввиду primary key, а просто обычный индекс.
29 сен 09, 09:28    [7718296]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Shocker.Pro
P.S. Надеюсь, я вас правильно понял, вы не имели ввиду primary key, а просто обычный индекс.
Ога.
29 сен 09, 10:23    [7718494]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
имхо, ReadDate лучше поставить перед Priority
29 сен 09, 11:25    [7718868]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
l_slava
Guest
Ray D,

ReadDate вообще в индексе не нужен
по поводу Priority однозначно трудно сказать,
какой процент записей по условию Priority >=@Priority?
29 сен 09, 11:34    [7718956]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Shocker.Pro
Member

Откуда: ->|<- :адуктО
Сообщений: 21520
примерно 70%

сейчас, с новым индексом пока все летает (тьфу-тьфу-тьфу). Время 0-200ms, READS 70-200

странно, почему было такое поведение? неужели план периодически менялся на другой?
29 сен 09, 12:03    [7719211]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
l_slava
Guest
Shocker.Pro
примерно 70%



Тогда по Priority индекс очень спорен, скорее даже не нужен.
Попробуй создать оба индекса и посмотри какой будет использоваться.
29 сен 09, 13:00    [7719705]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
l_slava
Shocker.Pro
примерно 70%



Тогда по Priority индекс очень спорен, скорее даже не нужен.
Попробуй создать оба индекса и посмотри какой будет использоваться.
Ога... У него уже был индекс без Priority (см. план в первом посте). В результате сканирование индекса + поиск по закладкам. Отсюда и столько чтений.
С замечанием Ray D пожалуй соглашусь... а в опчем, смотреть нада...
29 сен 09, 13:30    [7719938]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Shocker.Pro
Member

Откуда: ->|<- :адуктО
Сообщений: 21520
tpg
Ога... У него уже был индекс без Priority (см. план в первом посте). В результате сканирование индекса + поиск по закладкам. Отсюда и столько чтений.
С замечанием Ray D пожалуй соглашусь... а в опчем, смотреть нада...



А, Index Scan поменялся на Index Seek. Я не знаю, что это такое, вроде это хорошо.

Народ, посоветуйте какую-нить вменяемую и простую статью на русском по отладке запросов с помощью просмотра их планов в QA. А то я отлаживаю по наитию - гляжу, чтобы не было TableScan-ов по большим таблицам, создаю составные индексы, если вижу, что отбор разложен на несколько этапов... Но там же куча информации, до сих пор не понимаю, что такое Cost, почему он может быть 300% и от чего эти проценты меряются

Конечно, можно почитать БОЛ, но это как перерыть кучу навоза в поисках потерянной монетки.
29 сен 09, 13:44    [7720032]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Сперва чуток теории - https://www.sql.ru/articles/mssql/03013101Indexes.shtml
29 сен 09, 14:00    [7720161]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Shocker.Pro
Но там же куча информации, до сих пор не понимаю, что такое Cost, почему он может быть 300% и от чего эти проценты меряются
Ну, "это элементарно Ватсон"... https://www.sql.ru/faq/faq_topic.aspx?fid=391
29 сен 09, 14:01    [7720171]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Shocker.Pro
Member

Откуда: ->|<- :адуктО
Сообщений: 21520
tpg
Ну, "это элементарно Ватсон"... https://www.sql.ru/faq/faq_topic.aspx?fid=391



У-у-у-у. Лечение головной боли гильотинированием.... :(
29 сен 09, 14:12    [7720243]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
l_slava
Ray D,

ReadDate вообще в индексе не нужен
по поводу Priority однозначно трудно сказать,
какой процент записей по условию Priority >=@Priority?

Для этого запроса существует единственный идеальный индекс:
RecipientID, ReadDate, Priority

Никаких неоднозначностей и трудностей.
29 сен 09, 14:43    [7720504]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Shocker.Pro
Member

Откуда: ->|<- :адуктО
Сообщений: 21520
tpg
Сперва чуток теории - https://www.sql.ru/articles/mssql/03013101Indexes.shtml


Прочел. Правда все как-то скомканно и куча ошибок, но в основе разобрался. Куда дальше копать на предмет анализа запросов?
30 сен 09, 18:05    [7726597]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Очень всем рекомендую:
https://www.sql.ru/forum/actualthread.aspx?tid=139757&pg=5#2786465
30 сен 09, 19:23    [7726940]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
l_slava
Guest
Ray D
l_slava
Ray D,

ReadDate вообще в индексе не нужен
по поводу Priority однозначно трудно сказать,
какой процент записей по условию Priority >=@Priority?

Для этого запроса существует единственный идеальный индекс:
RecipientID, ReadDate, Priority

Никаких неоднозначностей и трудностей.


Без комментариев
И что тогда эта строка в плане говорит?
автор
|--Index Seek(OBJECT:([SVA_CUR].[dbo].[SY_MG_Log].[IX_SY_MG_Log_7]), SEEK:([SY_MG_Log].[RecipientID]=[@Recipient] AND [SY_MG_Log].[Priority] >= [@Priority]), WHERE:([SY_MG_Log].[ReadDate]=NULL) ORDERED FORWARD)
1 окт 09, 11:09    [7728508]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Вам что-то непонятно?
1 окт 09, 14:51    [7730055]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
l_slava
Guest
Ray D,
Я всегда считал, что NULL в индекс не попадает.
Еcтественно SEEK по полю ReadDate не происходит.
Тогда зачем добавлять это поле в индекс, тем самым увеличивая размер индекса?
1 окт 09, 15:47    [7730431]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36807
l_slava
Ray D,
Я всегда считал, что NULL в индекс не попадает.
Еcтественно SEEK по полю ReadDate не происходит.
Тогда зачем добавлять это поле в индекс, тем самым увеличивая размер индекса?
А с какого перепугу null не должно попадать в индекс?
1 окт 09, 15:49    [7730457]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Посмотрите планы и статистику ввода-вывода

ЗЫ Серега, привет! :)

drop table dbo.tt
go

create table dbo.tt	(
		aa		int		not	null
	,	bb		int		null
	,	cc		int
)
go

insert into dbo.tt	(
		aa
	,	bb
	,	cc
)
select	top 100000
		abs(checksum(newid())) % 10
	,	case when abs(checksum(newid())) % 100 < 50 then null else abs(checksum(newid())) % 100 end
	,	abs(checksum(newid())) % 100
from master.sys.objects s1, master.sys.objects s2, master.sys.objects s3
go
create index ix_test1 on dbo.tt(aa, bb, cc)
create index ix_test2 on dbo.tt(aa, cc, bb)
go

set statistics profile on
set statistics io on
go

select	count(*)
from dbo.tt with(index(ix_test1))
where	aa = 5
	and	bb is null
	and cc > 20
go

select	count(*)
from dbo.tt with(index(ix_test2))
where	aa = 5
	and	bb is null
	and cc > 20
go

set statistics profile off
set statistics io off
go
1 окт 09, 15:55    [7730515]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
l_slava
Member

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

Век живи - век учись. Буду знать, что NULL тоже в индексе.
1 окт 09, 18:30    [7731540]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Shocker.Pro
Member

Откуда: ->|<- :адуктО
Сообщений: 21520
Shocker.Pro
tpg
Сперва чуток теории - https://www.sql.ru/articles/mssql/03013101Indexes.shtml

Прочел. Правда все как-то скомканно и куча ошибок, но в основе разобрался. Куда дальше копать на предмет анализа запросов?


В принципе, сильно помогла и эта статья - стал лучше понимать планы и оптимизировать.

=====================================================================================

Не хочу плодить новые топики, попробую тут задать новый вопрос по оптимизации:

Итак:
MSSQL2000SP4

Запрос:
SELECT BASE.ID
FROM   dbo.MG_Log BASE WITH (NOLOCK)
       LEFT JOIN dbo.MG_Bookmarks BM WITH (NOLOCK) ON BASE.ID=BM.sMsg AND BM.sUser=11
WHERE  BASE.ID IN
       (
       SELECT     dbo.MG_Log.ID
       FROM       dbo.MG_Log WITH (NOLOCK) 
       WHERE      (dbo.MG_Log.RecipientID = 11) AND (dbo.MG_Log.ReadDate IS NULL)
       UNION
       SELECT     dbo.MG_Log.ID
       FROM       dbo.MG_Log WITH (NOLOCK) 
       WHERE      (dbo.MG_Log.RecipientID = 11) AND (dbo.MG_Log.IsJob=1) AND (dbo.MG_Log.JobCompleteDate IS NULL) AND Hide=0
       UNION
       SELECT     dbo.MG_Bookmarks.sMsg AS ID
       FROM       dbo.MG_Bookmarks 
       WHERE      dbo.MG_Bookmarks.sUser=11
       )
dbo.MG_Log - 500килозаписей

Почему именно так (через подзапрос) - лучше получается план, если все пихать в WHERE, получается в разы тормознее и план кривее.

Тем не менее, вот план:
|--Hash Match(Right Outer Join, HASH:([BM].[sMsg])=([BASE].[ID]))
|--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([SVA_CUR].[dbo].[MG_Bookmarks] AS [BM]))
| |--Index Seek(OBJECT:([SVA_CUR].[dbo].[MG_Bookmarks].[IX_SY_MG_Bookmarks] AS [BM]), SEEK:([BM].[sUser]=11) ORDERED FORWARD)
|--Hash Match(Right Semi Join, HASH:([Union1009])=([BASE].[ID]))
|--Concatenation
| |--Index Seek(OBJECT:([SVA_CUR].[dbo].[MG_Log].[IX_SY_MG_Log_7]), SEEK:([MG_Log].[RecipientID]=11), WHERE:([MG_Log].[ReadDate]=NULL) ORDERED FORWARD)
| |--Index Seek(OBJECT:([SVA_CUR].[dbo].[MG_Log].[IX_SY_MG_Log_1]), SEEK:([MG_Log].[IsJob]=1 AND [MG_Log].[JobCompleteDate]=NULL AND [MG_Log].[RecipientID]=11), WHERE:([MG_Log].[Hide]=0) ORDERED FORWARD)
| |--Bookmark Lookup(BOOKMARK:([Bmk1007]), OBJECT:([SVA_CUR].[dbo].[MG_Bookmarks]))
| |--Index Seek(OBJECT:([SVA_CUR].[dbo].[MG_Bookmarks].[IX_SY_MG_Bookmarks]), SEEK:([MG_Bookmarks].[sUser]=11) ORDERED FORWARD)
|--Index Scan(OBJECT:([SVA_CUR].[dbo].[MG_Log].[IX_SY_MG_Log_2] AS [BASE]))


Вопрос: что делает последняя строка в плане, особенно с учетом того что поле в индексе IX_SY_MG_Log_2 не имеет
никакого отношения к запросу? Соответственно - весит немало, создает кучу READS, EstimatedRowCount - все полмиллиона записей.

И вообще, мне не в первый раз попадается "NonClustered Index Scan", в котором не указано условие отбора.
Никак не могу понять, откуда оно берется и как от него избавиться.
24 ноя 09, 01:39    [7967428]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Ну и мешанина! :)

Ваш запрос будет эквивалентен этому:

SELECT     dbo.MG_Log.ID
FROM       dbo.MG_Log BASE WITH (NOLOCK) 
WHERE      (dbo.MG_Log.RecipientID = 11) AND ((dbo.MG_Log.ReadDate IS NULL) 
                                              or (dbo.MG_Log.IsJob=1) AND (dbo.MG_Log.JobCompleteDate IS NULL) AND Hide=0)
LEFT JOIN dbo.MG_Bookmarks BM WITH (NOLOCK) ON BASE.ID=BM.sMsg AND BM.sUser=11
UNION
SELECT     dbo.MG_Log.ID
FROM       dbo.MG_Log BASE WITH (NOLOCK)
INNER JOIN dbo.MG_Bookmarks on dbo.MG_Bookmarks.sMsg=BASE.ID
WHERE      dbo.MG_Bookmarks.sUser=11

Лишний Index Scan уйдет.
Для нормального плана нужны индексы:
MG_Log (ID) и MG_Bookmarks (sMsg) - по плану не видно есть они или нет
и MG_Bookmarks (sUser) и MG_Log (RecipientID) - уже есть


Ваш "NonClustered Index Scan" лезет от того, что либо нет индекса MG_Log (ID), либо в силу ккаких-либо обстоятельств оптимизатор считает, что сканирование того индекса будет выгоднее. А берется оно из вашего
SELECT BASE.ID
FROM   dbo.MG_Log BASE WITH (NOLOCK)
. Надо же откуда-то тащить MG_Log.ID...

ЗЫ. Кстати, при наличии нескольких записей с одним MG_Bookmarks.sMsg в вашей выборке могут появиться дубли. Вы уверены, что оно вам надо?
24 ноя 09, 10:25    [7968243]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос (ну а как еще назвать тему :( )  [new]
Shocker.Pro
Member

Откуда: ->|<- :адуктО
Сообщений: 21520
Anddros
Ну и мешанина! :)
Ваш запрос будет эквивалентен этому:
Лишний Index Scan уйдет.


Как я уже говорил, пробовал разные варианты, начиная с самого короткого (с одним селектом), но оптимизатор не справлялся.

Ваш вариант (если поменять местами LEFT и WHERE и разобраться с алиасами, дает более
оптимальный планчик, хотя, от индекс-скана не ушли (точнее появился другой):

  |--Hash Match(Union)
|--Nested Loops(Inner Join, OUTER REFERENCES:([MG_Bookmarks].[sMsg]) WITH PREFETCH)
| |--Bookmark Lookup(BOOKMARK:([Bmk1005]), OBJECT:([SVA_CUR].[dbo].[MG_Bookmarks]))
| | |--Index Seek(OBJECT:([SVA_CUR].[dbo].[MG_Bookmarks].[IX_SY_MG_Bookmarks]), SEEK:([MG_Bookmarks].[sUser]=11) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([SVA_CUR].[dbo].[MG_Log].[PK_SY_MG_Log]), SEEK:([MG_Log].[ID]=[MG_Bookmarks].[sMsg]) ORDERED FORWARD)
|--Index Scan(OBJECT:([SVA_CUR].[dbo].[MG_Log].[IX_SY_MG_Log_1]), WHERE:([MG_Log].[RecipientID]=11 AND ([MG_Log].[ReadDate]=NULL OR (([MG_Log].[IsJob]=1 AND [MG_Log].[JobComleteDate]=NULL) AND [MG_Log].[Hide]=0))))


Anddros
Для нормального плана нужны индексы:
MG_Log (ID) и MG_Bookmarks (sMsg) - по плану не видно есть они или нет
и MG_Bookmarks (sUser) и MG_Log (RecipientID) - уже есть


MG_Log (ID) разумеется есть кластерный это PK
MG_Bookmarks (sMsg) есть, но табличка маленькая, оптимизатор может его и не взять

Anddros
либо в силу ккаких-либо обстоятельств оптимизатор считает, что сканирование того индекса будет выгоднее. А берется оно из вашего SELECT BASE.ID
FROM dbo.MG_Log BASE WITH (NOLOCK). Надо же откуда-то тащить MG_Log.ID...


Так зачем он сканирует ВЕСЬ чужой индекс, если дешевле сначала провести Join, а потом брать его результаты, тем более,
что объединение идет именно по этому полю?

Anddros
ЗЫ. Кстати, при наличии нескольких записей с одним MG_Bookmarks.sMsg в вашей выборке могут появиться дубли. Вы уверены, что оно вам надо?


не будет таких записей - стоит уникальный констреинт на sMsg+sUser
24 ноя 09, 13:18    [7970078]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить