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

Откуда: Харьков, Украина
Сообщений: 62034
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

есть запрос вида

select *
from t_sale left join t_item on .....
      left join t_location on ....
.....

Никаких агрегатов, никаких сортировок. * - в реальности там явно перечислены столбцы
t_sale, t_item etc - обычные таблицы с необходимым минимумом индексов
t_sale ~30M записей, остальные - от 10K до 2M

план запроса - достаточно ожидаемый

StmtText                                                                                                                                                                                                                                                                                                   --------                                                                                                                                                                                                                                                                                                              Compute Scalar(DEFINE:([Expr1013]=(1)))                                                                                                              
|--Hash Match(Right Outer Join, HASH:([r].[ContextID], [r].[Retail Date])=([s].[ContextID], [s].[Sale Date]), RESIDUAL:(...)
|--Clustered Index Scan(OBJECT:([dbo].[t_RetailCalendar].[PK_t_RetailCalendar] AS [r]))
|--Hash Match(Right Outer Join, HASH:([sp].[ContextID], [sp].[Salesperson Code])=([s].[ContextID], [s].[Sale Salesperson Code]), RESIDUAL:(....)
|--Clustered Index Scan(OBJECT:([dbo].[t_Salesperson].[PK_t_Salesperson] AS [sp]))
|--Hash Match(Right Outer Join, HASH:([c].[ContextID], [c].[Customer No])=([s].[ContextID], [s].[Sale Customer No]), RESIDUAL:(....)
|--Clustered Index Scan(OBJECT:([dbo].[t_Customer].[PK_t_Customer] AS [c]))
|--Hash Match(Right Outer Join, HASH:([l].[ContextID], [l].[Location Code])=([s].[ContextID], [s].[Sale Location Code]), RESIDUAL:(....)
|--Clustered Index Scan(OBJECT:([dbo].[t_Location].[PK_t_Location] AS [l]))
|--Hash Match(Right Outer Join, HASH:([i].[ContextID], [i].[Item ID])=([s].[ContextID], [s].[Sale Item ID]), RESIDUAL:(....)
|--Clustered Index Scan(OBJECT:([dbo].[t_Item].[PK_t_Item] AS [i]))
|--Table Scan(OBJECT:([dbo].[t_Sale] AS [s]))

вся эта красота выгружается при помощи bcp в нативный файл, при этом наблюдается следующая картина:
1. некоторое время потребление tempdb растет за счет sys.dm_db_file_space_usage.internal_object_reserved_page_count, bcp не фиксирует получение строк. Потребление растет примерно до 1-2 гектар
2. bcp начинает копировать строки в файл, но потребление tempdb продолжает расти до упора - пока не кончится диск или пока не прервут копирование.

Откуда возникает вопрос - куда девается место? в п.1. я как бы понимаю - строим хэш-таблицы по справочникам.
но когда уже начинается копирование строк - почему используемый объем возрастает? Я бы мог понять, если бы там был distinct - но ничего такого нет.

куда бежать, как лечить, куда смотреть?

-------------------------
There’s no silver bullet!
15 мар 13, 03:02    [14050395]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
locky,
когда совсем не понятно куда копать
надо постепенно упрощать задачу пока не начнет работать
ну или с другого конца от примитивной постепенно усложняя
или количества в таблицах порезать для тестов
еще вариант на другом серваке попробовать
15 мар 13, 07:32    [14050531]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
http://blogs.msdn.com/b/deepakbi/archive/2010/04/14/monitoring-tempdb-transactions-and-space-usage.aspx
15 мар 13, 10:54    [14051390]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
locky,
все таки странно индекс ов достаточно ,а реально одни сканы кластерных идут
Как вариант.. яб поигрался с самими соединениями сначала (loop, merge....)
Впихнул бы фейковое значение в where для t_Sale (хотя с учетом того что выгружаеться почти вся таблица - може и смысла не имеет)
Ну и навреное, посмотрел бы сколько файдлов тембд у вас есть ,может стоит добавить.. бо 1-2 гб..ето не размер по факту
15 мар 13, 11:31    [14051684]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Maxx
все таки странно индекс ов достаточно ,а реально одни сканы кластерных идут


ничего странного. это же выгрузка данных. там должны быть сканы:

locky
эта красота выгружается при помощи bcp


возможно, получится "полечить", сведя к merge. пока же у нас hash и ошибки оценки селективностей, что приводит к плотному использованию tempdb при избытке ОЗУ

ситуация в блогах описанная, однозначного лечения нет, иногда помогает перестроение индексов и шаманство с самплингом обновления статистики
15 мар 13, 11:48    [14051801]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
komrad
Member

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

а если выгружать view, построенную на данном запросе, картина та же?
15 мар 13, 12:40    [14052185]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
komrad
а если выгружать view, построенную на данном запросе, картина та же?


облинкак. а что, должна быть разница? или подразумевается предварительное индексирование представления?
15 мар 13, 12:56    [14052296]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
перечитал. merge вам не светит. тут 5 соединений - значит +4 сорта своей 30М таблицы вы точно не переживете :)
loop также не очень подходит - будет в разы (а то и на порядки) дольше работать
судя по плану - выгружаете все 30М записей? возможно получится свести к merge соединение 2 самых больших таблиц, остальные оставить hash. и бороться с верностью оценки селективностей
15 мар 13, 14:56    [14053117]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Crimean
перечитал. merge вам не светит. тут 5 соединений - значит +4 сорта своей 30М таблицы вы точно не переживете :)
loop также не очень подходит - будет в разы (а то и на порядки) дольше работать
судя по плану - выгружаете все 30М записей? возможно получится свести к merge соединение 2 самых больших таблиц, остальные оставить hash. и бороться с верностью оценки селективностей

Угу.
Ресортировать таблицу фактов для merge - тоже не сахар, но в теории можно попробовать.

Но всё равно остаётся вопрос - куда/на что тратится место.
Ну не должно ж быть такого.
И куда смотреть? если только одна цифра в dmv есть на этот счет.
15 мар 13, 15:11    [14053241]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Maxx
Member [скрыт]

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

а если попробовать залить результат селекта в реальную таблицу и потом ее скормить bcp?
15 мар 13, 15:16    [14053286]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Maxx
locky,

а если попробовать залить результат селекта в реальную таблицу и потом ее скормить bcp?

проблема как раз в том, что при выполнении этого запроса жрется tempdb
bcp я использую для простоты - эмуляция конечного приложения, которое быстро вычитывает резалтсет

ну и опять таки - это не вариант, т.к. целевой рекордсет будет достаточно большим - не могу себе позволить такие траты дискового пространства
15 мар 13, 15:25    [14053348]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
с местом-то как раз все понятно. хеш + ошибка оценки. была статья не так давно, это подробно описывалось. быстро не нашел. короче, у нас при избытке ОЗУ из-за "этого" (ошибок оценки) интенсивно пилится темп внезапным избытком данных. вот примерчик на скорую руку, данные:

-- drop table dbo.a1
-- create table dbo.a1 ( id int primary key , f1 int not null, f2 int not null, f3 int not null )
-- insert into dbo.a1 ( id , f1, f2, f3 ) select object_id, object_id, object_id, object_id from sys.objects

-- drop table dbo.f1 
-- create table dbo.f1 ( id int primary key , fx int not null )
-- insert into dbo.f1 ( id , fx ) select object_id, object_id from sys.objects

-- drop table dbo.f2
-- create table dbo.f2 ( id int primary key , fx int not null )
-- insert into dbo.f2 ( id , fx ) select object_id, object_id from sys.objects

-- drop table dbo.f3
-- create table dbo.f3 ( id int primary key , fx int not null )
-- insert into dbo.f3 ( id , fx ) select object_id, object_id from sys.objects


запросик №1 (идеал)

select * from dbo.a1
left join dbo.f1 on f1.id = a1.id
left join dbo.f2 on f2.id = a1.id
left join dbo.f3 on f3.id = a1.id


тут, конечно же, мерж. а вот реальный, ближе к топику

select * from dbo.a1
left join dbo.f1 on f1.id = a1.f1
left join dbo.f2 on f2.id = a1.f2
left join dbo.f3 on f3.id = a1.f3


тут, конечно же, hash. можно заставить делать merge, но будут сорты. если бы у вас было соединение только по ContextID (это уже из первого сообщения топика) - был бы идеал, то есть merge. а поскольку все соединения по ContextID + разные поля - получаем только hash

пока подпорка видится как-то так:

create index I_a1_f1 on dbo.a1( f1, id ) include( f2, f3 ) -- with drop_existing


f1 должна быть самая большая из f* чтобы получить профит. получим первый мерж и остальные hash. имхо лучше чем ничего
15 мар 13, 15:49    [14053520]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Crimean
с местом-то как раз все понятно. хеш + ошибка оценки. была статья не так давно, это подробно описывалось. быстро не нашел. короче, у нас при избытке ОЗУ из-за "этого" (ошибок оценки) интенсивно пилится темп внезапным избытком данных.

Вот нашёл ответ Paul'а White'а (того, который SQLKiwi) на некоем форуме. Выглядит как статья :-) Вот выдержка оттуда:
Paul White, http://dba.stackexchange.com/questions/28686/execution-plan-shows-expensive-convert-implicit-operation-can-i-fix-this-with-i
The row count underestimate is an issue for the Hash Match operator because the amount of memory reserved for the hash table is based on the estimated number of rows. Where the memory is too small for the size of hash table required at run time (due to the larger number of rows) the hash table recursively spills to physical tempdb storage, often resulting in very poor performance. In the worst case, the execution engine stops recursively spilling hash buckets and resorts to a very slow bailout algorithm. Hash spilling (recursive or bailout) is the most likely cause of the performance problems outlined in the question (not character-type join columns or implicit conversions). The root cause would be the server reserving too little memory for the query based on incorrect row count (cardinality) estimation.

Sadly, before SQL Server 2012, there is no indication in the execution plan that a hashing operation exceeded its memory allocation (which cannot dynamically grow after being reserved before execution starts, even if the server has masses of free memory) and had to spill to tempdb.
15 мар 13, 16:08    [14053683]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
почитал, протанцевал все рекомендации - статистика, слежение за Hash Warning Event
первое ничего не дало, второе - не возникало
residual совпадает с probe

будем смотреть дальше
15 мар 13, 16:49    [14053970]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Maxx
Member [скрыт]

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

если не секрет совет Crimean тестили ?
15 мар 13, 16:51    [14053986]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Maxx
locky,

если не секрет совет Crimean тестили ?

а, да
просто с индексами - ничего не дало, само оно merge не подхватывает, при явном указании - возникает сортировка таблицы фактов, что неприемлимо :(
Создать оный индекс кластерным, для повышения шансов - пока не представляется возможным.
Пока что стягиваю бэкап себе локально, буду смотреть на разных версиях и сервис-паках
15 мар 13, 16:54    [14054007]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
locky
просто с индексами - ничего не дало, само оно merge не подхватывает, при явном указании - возникает сортировка таблицы фактов, что неприемлимо :(
Создать оный индекс кластерным, для повышения шансов - пока не представляется возможным


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

да! (все стесняюсь спросить) а условия соединения декларативно уникальные?
15 мар 13, 17:10    [14054142]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Crimean
locky
просто с индексами - ничего не дало, само оно merge не подхватывает, при явном указании - возникает сортировка таблицы фактов, что неприемлимо :(
Создать оный индекс кластерным, для повышения шансов - пока не представляется возможным


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

да! (все стесняюсь спросить) а условия соединения декларативно уникальные?


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

условия объединения - к таблице фактов привязываются таблицы справочников, справочники, разумеется, присоединяются по PK
Таблица фактов и таблицы справочников - довольно широкие, результирующий набор содержит 300+ довольно широких строковых и decimal колонок
15 мар 13, 17:15    [14054187]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
locky
результирующий набор содержит 300+ довольно широких строковых и decimal колонок

и 30 лямов......
locky - вы чего Байконур решили положить насмерть ?
15 мар 13, 17:17    [14054207]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Maxx
locky
результирующий набор содержит 300+ довольно широких строковых и decimal колонок

и 30 лямов......
locky - вы чего Байконур решили положить насмерть ?

А, это денормализация для скармливания данных в некий тул для аналитики
сам тул плохо делает джойны, вот смотрим на разные варианты
15 мар 13, 17:19    [14054221]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
не уверен, что поможет, но вдруг - option fast 1 / fastfirstrow не предлагать?
вам на этих объемах реально важнее чтобы выгрузка началась как можно раньше
15 мар 13, 17:21    [14054229]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Maxx
Member [скрыт]

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

да я уже понял,что ето не отчетная форма :)
15 мар 13, 17:21    [14054235]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Crimean
не уверен, что поможет, но вдруг - option fast 1 / fastfirstrow не предлагать?
вам на этих объемах реально важнее чтобы выгрузка началась как можно раньше

кстати, надо попробовать
при прошлых итерациях опыта пробовали поставить, но тамошний тул не позволил ставить опции
а при последующих итерациях я как-то забыл об этом
15 мар 13, 17:22    [14054238]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а with ( fastfirstrow ) можно и в представление упрятать в отличие от option( fast 1 ). правда, для 12 fastfirstrow уже не работает. а жаль. такой инструмент был.
15 мар 13, 17:50    [14054464]     Ответить | Цитировать Сообщить модератору
 Re: tempdb - на что уходит пространство?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
locky,

Можно попробовать дать больше памяти под этот запрос через Resource Governor. По умолчанию, на один запрос выделяется только 25% от примерно 75% доступных под запросы (Workspace Memory).

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

option fast 1 / fastfirstrow сведет все к тем же loop joins, так как это единственный способ избавится от блокирующих операций соединения (hash, sort+merge).
15 мар 13, 20:56    [14055434]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить