Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 нелогично? (EstCost уменьшается 99.2% -> 0,8%) (время выполнения увеличилось около +50%)  [new]
архивариус
Member

Откуда:
Сообщений: 159
 		меняю запрос (оба запроса выводят абсолютно одинаковые результаты)
(EstCost уменьшается 99.2% -> 0,8%)
(время выполнения увеличилось около +50%)(например 0.9 сек и 1,4 сек )
нелогично?
Вижу только что 2 запрос перестал параллелиться, непонимаю почему медленнее, подскажите что почитать по теме...
sqlplan (xml) в аттаче. (W2008R2, Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64))

1.
WITH dv1_CTE(SnapTime) AS (SELECT TOP 1800 SnapTime FROM dbo.FileStats WHERE SnapTime >= DATEADD(mi,-1801,CONVERT(DATETIME2(3),GETDATE())) GROUP BY SnapTime ORDER BY SnapTime DESC)
SELECT M.ID AS MID, M.DatabaseID, M.FileID, FS.DNReads, FS.DNWrites, FS.DBRead, FS.DBWritten, FS.DIOstall, FS.SnapTime,FS.waitReadMS,FS.waitWriteMS
FROM dbo.Filestats FS INNER JOIN dbo.DB2Monitor M ON FS.DatabaseID = M.DatabaseID AND FS.FileID = M.FileID
WHERE [SnapTime] >= (SELECT TOP 1 SnapTime FROM dv1_CTE ORDER BY SnapTime ASC) AND DNReads > 0
order by SnapTime, DBName
OPTION (MAXDOP 6)
2.
SELECT M.ID AS MID, M.DatabaseID, M.FileID, FS.DNReads, FS.DNWrites, FS.DBRead, FS.DBWritten, FS.DIOstall, FS.SnapTime,FS.waitReadMS,FS.waitWriteMS
FROM dbo.Filestats FS INNER JOIN dbo.DB2Monitor M ON FS.DatabaseID = M.DatabaseID AND FS.FileID = M.FileID
WHERE
[SnapTime] >= DATEADD(mi,-1800,CONVERT(DATETIME2(3),GETDATE())) AND DNReads > 0
order by SnapTime, DBName
OPTION (MAXDOP 6)



CREATE TABLE [dbo].[FileStats](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseID] [smallint] NULL,
[FileID] [smallint] NULL,
[FS_TimeStamp] [int] NULL,
[NumberReads] [bigint] NULL,
[DNReads] [bigint] NULL,
[NumberWrites] [bigint] NULL,
[DNWrites] [bigint] NULL,
[BytesRead] [bigint] NULL,
[DBRead] [bigint] NULL,
[BytesWritten] [bigint] NULL,
[DBWritten] [bigint] NULL,
[IOstallMS] [bigint] NULL,
[DIOstall] [bigint] NULL,
[IoStallReadMS] [bigint] NULL,
[DIoStallReadMS] [bigint] NULL,
[waitReadMS] [bigint] NULL,
[IoStallWriteMS] [bigint] NULL,
[DIoStallWriteMS] [bigint] NULL,
[waitWriteMS] [bigint] NULL,
[BytesOnDisk] [bigint] NULL,
[DBytesOnDisk] [bigint] NULL,
[%BytesRead] [smallint] NULL,
[%BytesWritten] [smallint] NULL,
[%NumberReads] [smallint] NULL,
[%NumberWrites] [smallint] NULL,
[B] [char](2) NOT NULL DEFAULT (' '),
[#] [char](2) NOT NULL DEFAULT (' '),
[AverageBytesReads] [bigint] NULL,
[AverageBytesWrites] [bigint] NULL,
[SnapTime] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]

CREATE CLUSTERED INDEX [CX_DBID_FILE_ID] ON [dbo].[FileStats]
(
[SnapTime] ASC,
[DatabaseID] ASC,
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ID] ON [dbo].[FileStats]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

К сообщению приложен файл (q.7z - 5Kb) cкачать
6 сен 16, 14:26    [19632959]     Ответить | Цитировать Сообщить модератору
 Re: нелогично? (EstCost уменьшается 99.2% -> 0,8%) (время выполнения увеличилось около +50%)  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
архивариус
непонимаю почему медленнее
Потому что первый распараллелился, а второй - нет.
Найдите для этих запросов соответствующие строки в sys.dm_exec_query_stats и сравните worker_time.
6 сен 16, 15:16    [19633249]     Ответить | Цитировать Сообщить модератору
 Re: нелогично? (EstCost уменьшается 99.2% -> 0,8%) (время выполнения увеличилось около +50%)  [new]
архивариус
Member

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

worker_time больше у 1 запроса, а elapsed у 2,
а ещё непонятно почему
physical_reads у 1 запроса "стремится" к 0, а у 2 всегда больше.

в аттаче
select * from sys.dm_exec_query_stats
WHERE
query_hash = 0xBF7E3015AE5FC24C --2 запрос
OR
query_hash = 0x7E22E24C363A4807 --1 запрос

К сообщению приложен файл (q.csv - 674bytes) cкачать
6 сен 16, 16:11    [19633622]     Ответить | Цитировать Сообщить модератору
 Re: нелогично? (EstCost уменьшается 99.2% -> 0,8%) (время выполнения увеличилось около +50%)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8309
 		архивариус,

Вы написали разные запросы и удивляетесь - почему выбраны разные планы выполнения?
7 сен 16, 11:23    [19636259]     Ответить | Цитировать Сообщить модератору
 Re: нелогично? (EstCost уменьшается 99.2% -> 0,8%) (время выполнения увеличилось около +50%)  [new]
архивариус
Member

Откуда:
Сообщений: 159
Владислав Колосов
архивариус,

Вы написали разные запросы и удивляетесь - почему выбраны разные планы выполнения?

нет,
вопрос почему запрос c EstCost 0,8 % выполняется медленнее чем запрос с EstCost 99,2%
7 сен 16, 12:06    [19636670]     Ответить | Цитировать Сообщить модератору
 Re: нелогично? (EstCost уменьшается 99.2% -> 0,8%) (время выполнения увеличилось около +50%)  [new]
SomewhereSomehow
Member

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

Добрый день.
Приведу описание из блога, чтобы не переписывать написанное
автор
Когда-то давно, когда деревья были больше, трава зеленее, а компьютеры имели дисководы — велась разработка оптимизатора для версии SQL Server 7.0. Для того, чтобы сравнивать разные операторы плана между собой, и выбирать наиболее выгодные из них, нужен был некий механизм оценки стоимости каждого оператора, какие-то цифры. Разработчик, ответственный за это (источники говорят, что его звали Ник) взял за основу время выполнения операции на своей собственной машине. Таким образом, стоимость плана равная 1, означала что оптимизатор, оценивает время выполнения запроса на машине Ника в 1 секунду. Оттуда же пошли некоторые константы, например случайный доступ 320 операций в секунду породил константу 1/320 = 0.003125, которая и по сей день зашита внутри сервера и используется для оценки стоимости операции случайного доступа.

Сейчас, эти цифры не несут никакой смысловой нагрузки, как единицы измерения чего либо. Это просто некоторые коэффициенты, которые служат для сравнения операторов между собой. Можно думать о них, как о предполагаемых затратах ресурсов на выполнение плана. Ключевое слово — предполагаемых, т.к. они являются оценкой, которая выполняется в соответствии с моделью.

Оценка довольно часто находится в рассогласовании с реальностью, поэтому, очень часто люди удивляются, почему сравнивая два запроса один стоит 10%, второй 90%, но первый выполняется 90 секунд а второй 10. Тут же находят аргументы, что «правильно, на второй же больше ресурсов, значит выполнится быстрее» — это тоже в общем не совсем верно. Ведь может быть, что запрос который не может использовать индекс и имеет огромную стоимость — будет действительно долго выполняться, по сравнению с запросом который сможет использовать поиск по индексу, будет стоить дешевле и выполнится мгновенно. Ответ тут простой, не нужно полагаться на стоимость как на меру времени — это просто результат работы модели. Ошибка модели — ошибка в стоимости.

Простой пример, необновленная статитика по возрастающему ключу. Допустим, есть таблица в 10 000 000, в последний раз было загружено 10 000 строк, это менее 20%, статистика не обновилась, а никаких регламентных работ по ее обновлению нет. Запрос по условию — where id > 10 000 000. В гистограмме просто нет значений более чем 10 000 000, выдается оценка одна строка. При этом, как правило, запрос более сложный, обычно там есть соединения, а для небольшого числа строк выгоднее использовать Nested Loops, он и выбирается, и т.д. План оказывается не дорогим (ведь предполагаемое число строк равно единице). Запрос начинает выполняться — все очень медленно, хотя стоимость низкая! Но на самом-то деле, строк 10 000. Обновили статистику, построился другой план, с другими стратегиями доступа, типами соединения и может быть даже параллельный, который выполняется очень быстро! Хотя стоит намного дороже первого. Значит ли это, что сервер использовал больше ресурсов и по этому выполнил быстрее? Вовсе нет, это просто значит, что оптимизатор более «трезво» взглянул на вещи и построил более адекватный план, который оказался дороже, т.к. оценка предполагаемого числа строк была выше, но больше соответствовала действительности.


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

К сообщению приложен файл. Размер - 14Kb
7 сен 16, 12:29    [19636844]     Ответить | Цитировать Сообщить модератору
 Re: нелогично? (EstCost уменьшается 99.2% -> 0,8%) (время выполнения увеличилось около +50%)  [new]
архивариус
Member

Откуда:
Сообщений: 159
SomewhereSomehow
архивариус,
После этого, сравните реальное число строк в первом и втором случае, первый просто ближе к реальности и адекватнее оценивает. Строк больше, поэтому стоимость выше, но это не значит, что он в реальности будет выполняться медленнее.

спасибо, это ответ на мой вопрос
7 сен 16, 12:35    [19636893]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить