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

Откуда:
Сообщений: 76
всем привет,
есть табличка mainDataTable на 7 млн записей... уже всю статистику обновили и PK ре-билднули... но не помогает...
вот код:

--тут просто заполнятеся табличка
declare @EmailCodes dbo.userTableType
insert into @emailcodes select top 1000 emailcode from tblMaster order by emailcode desc

--первый запрос - использует Index(PK) scan и Hash Match, relative cost 99%
SELECT col1,col2,col3
FROM table1 ml WITH (NOLOCK)
INNER JOIN mainDataTable mol WITH (NOLOCK)
ON mol.ID = ml.ID
INNER JOIN @EmailCodes emc ON emc.ID = mol.EmailCode
WHERE ml.Deleted = 0
ORDER BY mol.EmailCode, ml.ID

-- второй запрос, то же самое но вместо Join с табличной переменной используется IN(..); использует Index(PK) seek и Nested loop, relative cost 1%

SELECT col1,col2,col3
FROM table1 ml WITH (NOLOCK)
INNER JOIN mainDataTable WITH (NOLOCK)
ON mol.ID = ml.ID
WHERE mol.EmailCode IN (SELECT ID FROM @EmailCodes) AND ml.Deleted = 0 ORDER BY mol.EmailCode, ml.ID

--

вопрос как всегда: почему для первого запроса план явно и далеко не самый оптимальный?
выборка одна и та же, то бишь одни и те же значения emailCodes....так почему планы разные?
29 июн 11, 07:05    [10890411]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А что будет, если вместо табличной переменной (@) использовать временную таблицу (#) ?
29 июн 11, 08:27    [10890487]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
me
Member

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

с переменной табличкой тоже выбирает плохой план с индекс сканом...
30 июн 11, 00:32    [10896449]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
me
tpg,

с переменной табличкой тоже выбирает плохой план с индекс сканом...
Я про временную таблицу говорил, а не про переменную табличного типа.
Вы оговорились наверное...
Хоть бы скрипты своих таблиц привели.
30 июн 11, 05:56    [10896701]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
me
Member

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

да, извините, временная таблица, именно...

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

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

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

потому вот и прошу помощи y гуру..=)
30 июн 11, 06:28    [10896724]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ну, вы б статистику обновили б штоли... Тогда и понятно серверу будет, что и как делать.
30 июн 11, 07:14    [10896764]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
me,

вы бы оба реальных плана выложили, было б понятнее куда копать...
30 июн 11, 08:58    [10896917]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
me
Member

Откуда:
Сообщений: 76
статистику естественно уже обновляли и индексы ребилдили... как уже писалось вначале..
ниже полные запросы и планы:


-- данные для теста
declare @EmailCodes table(ID int)
insert into @emailcodes select top 1000 emailcode from tblMasterEmails order by emailcode desc

-- первый запрос с "плохим" планом
 SELECT mol.ID,mol.MailingListID,mol.EmailCode,...	
  FROM dbo.tblMailingLists ml WITH (NOLOCK)
         INNER JOIN dbo.tblMailinglist_EmailCode_link mol WITH (NOLOCK)
            ON mol.MailingListID = ml.MailingListID
         INNER JOIN @EmailCodes emc ON emc.ID = mol.EmailCode
         WHERE ml.Deleted = 0 ORDER BY mol.EmailCode, ml.MailingListID
  
  -- второй запрос с "хорошим" планом
SELECT mol.ID,mol.MailingListID,mol.EmailCode,...	
  FROM dbo.tblMailingLists ml WITH (NOLOCK)
         INNER JOIN dbo.tblMailinglist_EmailCode_link mol WITH (NOLOCK)
            ON mol.MailingListID = ml.MailingListID
   WHERE mol.EmailCode IN (SELECT ID FROM @EmailCodes) AND ml.Deleted = 0 ORDER BY mol.EmailCode, ml.MailingListID

планы выполнения должны быть приатачены...

и главная табличка c PK который почему-то сканится для первого запроса, а для второго используется seek:

CREATE TABLE [dbo].[tblMailinglist_EmailCode_link](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MailingListID] [int] NOT NULL,
	[EmailCode] [int] NOT NULL,
	.......,
 CONSTRAINT [PK_tblMailinglist_EmailCode_link] PRIMARY KEY CLUSTERED 
(
	[MailingListID] ASC,
	[EmailCode] ASC
)) ON [PRIMARY]


К сообщению приложен файл (plan.sqlplan - 45Kb) cкачать
5 июл 11, 07:58    [10921852]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
me,

в любом случае, вот это будет не вредным.
там и про оптимизатора, и про порядок таблиц в запросе.
5 июл 11, 22:03    [10928111]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
Нектотам
Guest
1. Запросы не эквивалентны. Как минимум, первый может вернуть по нескольку строк, если SELECT ID FROM @EmailCodes возвращает повторяющиеся данные. MS SQL это учитывает.
2. Индексы и особенно уникальные индексы на временных таблицах тоже пригодятся.
Например так
me
-- данные для теста
declare @EmailCodes table(ID int primary key)
insert into @emailcodes select top 1000 distinct emailcode from tblMasterEmails order by emailcode desc
6 июл 11, 08:06    [10929354]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
aleks2
Guest
1. Научитесь не третировать сервер
declare @EmailCodes table(ID int primary key clustered)

2. Добавьте dbo.tblMailinglist_EmailCode_link индекс
([EmailCode], [MailingListID])

3. Вам точно нада mol.ID в результатах запроса?
6 июл 11, 08:06    [10929357]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
Нектотам
Guest
упс, дистинкт не там воткнул, ну да ладно. И так понятно.
6 июл 11, 08:08    [10929364]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
me
Member

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

с emailcode - все нормально, сама таблица имеет PK по нему, поэтому там всегда одни и те же значения.. так что distinct, к соажлению, погоды не делает.. опять же, табличка то заполняется только один раз и одна и та же табличка и те же самые значения используются для обоих заросов ( с дублями или без, но данные одинаковые для всех запросов)

с PK для @EmailCodes - у меня сомнения, в обоих случаях будем скан по @EmailCodes, так как все записи востребованы, так что какая разница PK или нет?! но в любом случае, попробовать стоило, но результат как и ожидалось тот же..
6 июл 11, 17:24    [10934174]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
me
Member

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

автор
1. Научитесь не третировать сервер

declare @EmailCodes table(ID int primary key clustered)


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

автор
3. Вам точно нада mol.ID в результатах запроса?

А что это меняет? мне точно нужны некоторые другие колонки из той же таблицы, так что без разницы. Но все равно на всякий случай проверить надо было, и результат был тот же.

автор

2. Добавьте dbo.tblMailinglist_EmailCode_link индекс
([EmailCode], [MailingListID])


Индекса по одному [EmailCode] хватает и как уже было сказано, это и есть решение...

Моя цель тут была не найти решение, а узнать причину, по которой выбирается неоптимальный план... вот если б можно было типа "продебагить" сам выбор плана скл сервером... почему был выбран скан а не seek?....

опять же как уже было сказано, в остальных клиентских базах данных выбирается seek, этот единственный со сканом... так что логически, дело должно быть в распределении данных, селективности и т.п.... но в случае с этими 2 запросами - @EmailCodes - та же самая, данные те же самые... значит не селективность ?! ... тогда что? или как подтвердить что дело именно в распределении данных фактически, а не логически...???
6 июл 11, 17:45    [10934373]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
me
Member

Откуда:
Сообщений: 76
ну что, ни у кого никаких идей как это можно подвердить или проверить?
7 июл 11, 21:39    [10941835]     Ответить | Цитировать Сообщить модератору
 Re: помогите понять выбор execution plan-а  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
me
ну что, ни у кого никаких идей как это можно подвердить или проверить?
а создайте-ка индекс, или статистику по табличной переменной...

А вот по временной табличке - статистика-таки может создаваться сервером, и юзаться планировщиком.
8 июл 11, 05:00    [10942737]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить