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

Откуда:
Сообщений: 118
предыстория: начались проблемы с производительностью; вдруг процедуры, работавшие 100-200 ms, замедлялись до минут. было замечено, что при замедлении работы сервера резко возрастало чтение/запись в mdf файл БД tempdb, (на два порядка по сравнению с обычной работой). также замечено, что временные таблицы при завершении сессии не удалялись из tempdb, а переименовывались, новые имена выглядили примерно так: dbo.[#000DE123]. предположительно, таблицы сохраняются для надобности execution plan. размер mdf/ndf файлов вырастал до десятков GB (рост файлов не ограничен).

в кэше для этих переименованных временных таблиц выделяются тысячи страниц, причём они сидят в кэше часами. несмотря на то, что хранимые процедуры, их использующие, работают минуты, и сессии завершаются сразу по окончании процедуры. dbcc dropcleanbuffers не помогает.

найдено следующее решение, которое сейчас и работает:
1. каждые несколько часов [dbcc freeproccache] стирает переименованные временные таблицы.
2. каждые несколько часов делается [dbcc shrinkfile] для mdf/ndf файлов БД tempdb (для того, чтобы была возможность shrink, в tempdb добавлено 7 ndf файлов (16-ядерный процессор))

решение неудачное, мешает.

вопросы:
есть какое-нибудь название у этих переименованных таблиц в tempdb, по которому можно найти информацию в msdn?
есть ли возможность влиять на кэш, объяснить, что ему следует хранить?
возможно, кто-нибудь сталкивался с подобной проблемой?

MS SQL Server 2008R2

спасибо
24 июл 12, 14:32    [12908523]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
step_ks
Member

Откуда:
Сообщений: 936
valv
временные таблицы при завершении сессии не удалялись из tempdb, а переименовывались, новые имена выглядили примерно так: dbo.[#000DE123]

Это вы как выясняли? То, что "переименовывались".
24 июл 12, 14:37    [12908562]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
valv
Member

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

выяснил просто:
запускаем процедуру, которая создаёт временную таблицу,
видим эту таблицу в tempdb с именем вроде [#temp_balance___________000DE123]
после окончания SP таблица исчезает и появляеся новая с именем вроде #000DE123
с теми же полями.
24 июл 12, 15:09    [12908826]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
step_ks
Member

Откуда:
Сообщений: 936
cчетчик General Statistics:Temp Tables For Destruction не смотрели ли?
24 июл 12, 15:36    [12909051]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
SomewhereSomehow
Member

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

Ситуация с временными таблицами воспроизводится. Насколько это можно понять сделано для оптимизации, чтобы не перестраивать план каждый раз, таблица не удаляется. Но вот ситуация с "зависшими страницами" у меня не воспроизводится. После выполнения, если посмотреть на dm_db_index_physical_stats, у меня результат 1 страница, если посмотреть в логе то видно что запускается функция FCheckAndCleanupCachedTempTable (послее работы выводится что-то вроде "Deallocated 1 extent(s) starting at page 0001:000003b8".) Т.е. вроде сохраняются только метаданные таблиц.
Если процедуру создавать так:
create proc proc1
with recompile
as
...

То план для нее в кэш не помещается, и никаких таблиц не остается, по завершению выполнения таблица удаляется вместе с метаданными.
Но интересно, в чем причина, действительно ли в том, что место не освобождается правильно или в чем-то еще. Это вот так резко началось и при этом ничего не менялось?
24 июл 12, 16:23    [12909440]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
valv
Member

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

спасибо большое за наводку, начал работать с этим счётчиком.
уничтожается в минуту примерно 50 таблиц, но в среднем создаётся немного больше (счётчик active temp tables это показывает).
похоже, что какие-то остаются. пока рано делать выводы, посмотрю статистику за день.
24 июл 12, 16:41    [12909575]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
valv
Member

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

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

процедура запускается 300 раз в час, за 8 часов создаётся 2400 комплектов временных таблиц, из них сохраняются надолго сотни. что нелогично, для создания плана достаточно одной.
и, главное, что эти таблицы делают в кэше?

SomewhereSomehow
Это вот так резко началось и при этом ничего не менялось?

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

SomewhereSomehow
create proc proc1
with recompile as

да, похоже, решает проблему. варианты типа [KEEPFIXED PLAN] тоже помогают.
к сожалению, переписать процедуры за разумное время невозможно, и совсем неприемлимо для процедур, для которых десятки милисекунд критичны;
крайне желательно не устранять последствия проблемы, а найти способ предотвратить, указать приоритет для таблиц, хранящихся в кэше.
чтобы не было дикого I/O на жёсткий диск для текущих таблиц, в то время как кэш забит чепухой.
24 июл 12, 17:06    [12909809]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
step_ks
Member

Откуда:
Сообщений: 936
Для кучи к счетчику
http://msdn.microsoft.com/en-us/library/cc966545.aspx#EDAA
24 июл 12, 17:14    [12909878]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
Это похоже на иллюстрацию к вопросу "надо ли очищать или удалять временные таблицы в конце процедуры".

У нас эти #000DE123-бла-бла-бла таблицы отъедают 1,6 Гб от буффер-пула.
24 июл 12, 17:22    [12909939]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
valv
процедура запускается 300 раз в час, за 8 часов создаётся 2400 комплектов временных таблиц, из них сохраняются надолго сотни. что нелогично, для создания плана достаточно одной.
и, главное, что эти таблицы делают в кэше?

Да, одной. Если например вызывать процедуру в бесконечном цикле хоть 100 раз в секунду, таблица и будет одна. Но вот если та же процедура одновременно начинает вызываться из другой сессии, таблиц уже становится две.
Далее могу только предполагать. По идее, когда сервер приступает к выполнению запроса, он уже должен знать с какими реально объектами ему работать, по-этому, видимо, где-то хранится связь "план выполнения-конкретная временная таблица-сессия", ведь подсчитывается же как-то число refcount в dmv кэша планов. И чем больше одновременных сессий выполнения процедуры создаешь, тем больше растет этот счетчик и тем больше растет кол-во этих "остатков" временных таблиц. Но что интересно, когда все сессии останавливаешь, счетчик ссылок на план приходит в норму, а "остатки" таблиц остаются.
Вот только у меня они каждая упорно продолжают занимать одну страницу. Это происходит вне зависимости от того, делаю ли я дроп явно или полагаюсь на сервер по завершению процедуры. Т.е. в моем случае, сервер успевает за собой "подчищать", видимо.
А шринкать файл зачем?
Кстати, ради интереса, скажите, о каких примерно объемах идет речь? Имеется ввиду в сравнении с объемом памяти доступной серверу.
24 июл 12, 19:29    [12910504]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
А шринкать файл зачем?
Наверное чтобы увеличить физическую фрагментацию файлов и замедлить работу с tempdb, других причин делать это я не вижу.
SomewhereSomehow
Кстати, ради интереса, скажите, о каких примерно объемах идет речь? Имеется ввиду в сравнении с объемом памяти доступной серверу.
Да, и еще интересно каких размеров у вас временные таблицы, и если они действительно такие большие и впрямь "съедают" приличный объем от кэша, то может стоит пересмотреть работу с временными таблицами, избавится от них где только возможно?

valv
предположительно, проблемы масштабирования. значительно увеличилось количество строк во временных таблицах и частота запуска. увеличение было плавным, а вот проблема началась вдруг. возможно, количество перешло в качество - на каком-то этапе не стало место в кэше, и вместо быстрой работы в RAM стал использоваться жёсткий диск.
Вы проверяли использование page file?

step_ks
Для кучи к счетчику
http://msdn.microsoft.com/en-us/library/cc966545.aspx
В этой же статье и написано про работу с временными таблицами:
There is now deferred drop in tempdb. This means, for example, that when a large temporary table is dropped by an application, it is handled by a background task and the application does not have to wait.
...
You can drop the associated stored procedure(s) or free the procedure cache to get rid of these temporary tables.
24 июл 12, 21:33    [12910850]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
step_ks
Member

Откуда:
Сообщений: 936
Гавриленко Сергей Алексеевич
Это похоже на иллюстрацию к вопросу "надо ли очищать или удалять временные таблицы в конце процедуры".

похоже, что наличие или отсутствие явного дропа в процедуре не имеет значения

use tempdb 
go
if object_id('dbo.temptable_test1','P') is not null drop proc dbo.temptable_test1
go
create proc dbo.temptable_test1
as
create table #temptable_test1 ([temptable_test1_E1AA6030-4465-40CF-9FB0-658B501489E4] varchar(8000))
insert #temptable_test1 select replicate('i',8000) from sysobjects so1, sysobjects so2
go

if object_id('dbo.temptable_test2','P') is not null drop proc dbo.temptable_test2
go
create proc dbo.temptable_test2
as
create table #temptable_test2([temptable_test2_F27CBCFB-751D-4A0F-AA30-4E889B54581C] varchar(8000))
insert #temptable_test2 select replicate('i',8000) from sysobjects so1, sysobjects so2
drop table #temptable_test2
go

--begin tran
exec dbo.temptable_test1
exec dbo.temptable_test2
select so.name,st.page_count 
   from  sys.dm_db_index_physical_stats (default, default, default, default, default) st 
         join tempdb.sys.objects so on so.object_id = st.object_id  
   where exists(select * from sys.columns sc where sc.object_id = so.object_id and sc.name in ('temptable_test1_E1AA6030-4465-40CF-9FB0-658B501489E4','temptable_test2_F27CBCFB-751D-4A0F-AA30-4E889B54581C') )
--rollback


без транзакции
namepage_count
#20E1DCB50
#21D600EE0


в транзакции
namepage_count
#temptable_test1____________________________________________________________________________________________________00000000011F3601
#temptable_test2____________________________________________________________________________________________________0000000001203722
24 июл 12, 21:47    [12910883]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
step_ks
похоже, что наличие или отсутствие явного дропа в процедуре не имеет значения
truncate точно должен выносить данные как из таблицы, так и из памяти.
24 июл 12, 21:51    [12910891]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
valv
Member

Откуда:
Сообщений: 118
SomewhereSomehow
А шринкать файл зачем?

эксперементально получено:
после запуска [dbcc freeproccache] некоторое время продолжается работа с диском вместо записи в RAM.
если же сразу после [dbcc freeproccache] запустить shrink файлов БД tempdb,
то повышенный I/O к жёсткому диску прекращается немедленно.
Mind
Наверное чтобы увеличить физическую фрагментацию файлов и замедлить работу с tempdb, других причин делать это я не вижу.

неважно, был ли размер файла реально уменьшен или остался без изменений (последнее проиходит гораздо чаще),
проблема исчезает немедленно после окончания работы dbcc.
SomewhereSomehow
Кстати, ради интереса, скажите, о каких примерно объемах идет речь? Имеется ввиду в сравнении с объемом памяти доступной серверу.

используется памяти 16 GB (test workaround, на production другие числа)
размер кэша - 5.6 GB
во время проблемы, из них 600,000 страниц заняты таблицами типа "#01234567" из tempdb
Mind
Да, и еще интересно каких размеров у вас временные таблицы, и если они действительно такие большие и впрямь "съедают" приличный объем от кэша, то может стоит пересмотреть работу с временными таблицами, избавится от них где только возможно?

на одну таблицу тратится от 5 до 20 тысяч страниц. мелкие таблицы на пару страниц не считаем.
всего около 20 разных таблиц, в 10 SP.
полагаю, что переписывать код, который разрабатывался много человеколет и успешно работал в production, всего лишь из-за проблем масштабирования, это в корне неверно. тем более таких ничтожных проблем, как плавное увеличение количества строк в таблицах.
это как раз тот случай, когда ms sql server обязан справиться самостоятельно.
Mind
Вы проверяли использование page file?

с RAM пробем нет, проверяли.
Mind
В этой же статье и написано про работу с временными таблицами:
...
You can ...or free the procedure cache to get rid of these temporary tables.

вот об этом и вопрос. как же, собственно, "to get rid of these temporary tables"??
25 июл 12, 12:45    [12913324]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
SomewhereSomehow
Member

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

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

Если у вас всего 10 хранимок, очень легко проверить, изменится ли что-то, если пересобрать их с with recompile, чтобы план не помещался в кэш и посмотреть, будет ли такая же нагрузка при аврале? Изменится ли что-то? Если существенно ничего не изменится, то вряд ли это проблема того что сервер сохраняет метаданные таблиц.

п.с.
valv
полагаю, что переписывать код, который разрабатывался много человеколет и успешно работал в production, всего лишь из-за проблем масштабирования, это в корне неверно.
Кстати вот с этим не согласен, иногда как раз с увеличением объема данных приходится пересматривать алгоритмы или даже структуру, если конечно не смотреть в сторону улучшения оборудования.
25 июл 12, 14:12    [12913939]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гавриленко Сергей Алексеевич
У нас эти #000DE123-бла-бла-бла таблицы отъедают 1,6 Гб от буффер-пула.
Так и не понял где/как это смотреть.
25 июл 12, 16:01    [12914738]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
Mnior
Гавриленко Сергей Алексеевич
У нас эти #000DE123-бла-бла-бла таблицы отъедают 1,6 Гб от буффер-пула.
Так и не понял где/как это смотреть.

Я смотрю содержимое BP этим:
select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind 
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc
25 июл 12, 16:03    [12914752]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
valv
используется памяти 16 GB (test workaround, на production другие числа)
размер кэша - 5.6 GB
во время проблемы, из них 600,000 страниц заняты таблицами типа "#01234567" из tempdb
А почему при 16 доступных, под кэш занято всего 5.6? Остальное под процедурным кэшем или под запросами? Под временными таблицами 80% кэша и сервер не пытается это место освободить, может быть серверу больше чем 5.6 и не нужно. Есть ли признаки memory pressure? Что показывает PLE?
valv
с RAM пробем нет, проверяли.
То есть проблема только с диском под temdb?
А что насчет другой активности с tempdb, что покажет?

select 
  SUM(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)/128. AS [tempdb_user MB],
  SUM(tsu.internal_objects_alloc_page_count- tsu.internal_objects_dealloc_page_count)/128 AS [tempdb_internal MB]
from sys.dm_db_task_space_usage tsu 

valv
полагаю, что переписывать код, который разрабатывался много человеколет и успешно работал в production, всего лишь из-за проблем масштабирования, это в корне неверно. тем более таких ничтожных проблем, как плавное увеличение количества строк в таблицах.
Если оно не масштабируемо, то люди порой не то что код, а всю систему меняют... Если что-то можно сделать без временных таблиц, то лучше это сделать без них. Плавное увеличение количества строк иногда приводит к тому что планы запросов внезапно меняются, и начинаются тормоза.
valv
Mind
В этой же статье и написано про работу с временными таблицами:
...
You can ...or free the procedure cache to get rid of these temporary tables.

вот об этом и вопрос. как же, собственно, "to get rid of these temporary tables"??
free the procedure cache :) Чем вам ваш текущий вариант не нравится? Я бы только шринк убрал.

Ну и еще совсем глупый вопрос, у вас все последние SP установлены?
25 июл 12, 21:35    [12916661]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гавриленко Сергей Алексеевич
Mnior
пропущено...
Так и не понял где/как это смотреть.

Я смотрю содержимое BP этим:

А еще можно поменять 
from sys.dm_os_buffer_descriptors as bd
    inner join
на
from sys.dm_os_buffer_descriptors as bd
    left join
и увидеть память которая все еще считается занятой под буфер для tempdb, но фактически объектов там уже нет.
25 июл 12, 21:54    [12916735]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гавриленко Сергей Алексеевич
Я смотрю содержимое BP этим
Спасиб.

Оказалось, что у нас есть похожий скрипт. Только в одном месте стояло:
WHERE	B.database_id != DB_ID('tempdb')
Посмотрел - 1% от общего размера - ужасно много

И ещё, а зачем это:
page_type in ('data_page', 'index_page')
Там же много всяких других типов.

А всякие спулы, хеши и т.п. они к tempdb не относятся?
26 июл 12, 11:17    [12918334]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
Ну, "мопед не мой". ;)
Да и я не в tempdb смотрел обычно.
26 июл 12, 12:33    [12918890]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
valv
Member

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

SomewhereSomehow
Но вот ситуация с "зависшими страницами" у меня не воспроизводится. После выполнения, если посмотреть на dm_db_index_physical_stats, у меня результат 1 страница, если посмотреть в логе то видно что запускается функция FCheckAndCleanupCachedTempTable (послее работы выводится что-то вроде "Deallocated 1 extent(s) starting at page 0001:000003b8".) Т.е. вроде сохраняются только метаданные таблиц.
...
когда все сессии останавливаешь, счетчик ссылок на план приходит в норму, а "остатки" таблиц остаются.
Вот только у меня они каждая упорно продолжают занимать одну страницу. Это происходит вне зависимости от того, делаю ли я дроп явно или полагаюсь на сервер по завершению процедуры. Т.е. в моем случае, сервер успевает за собой "подчищать", видимо.

всё отлично, удалось получить почти такую же картину (90% - вызов FCheckAndCleanupCachedTempTable и сохранение только одной страницы (метаданные), 10% - в кэше остаются заполненные таблицы, но хранится не больше одной таблицы каждого типа).

а сделали вот что: оказывается, хранимые процедуры суть чрезмерно универсальны и работают для нескольких случаев, различающихся заполнением временных таблиц; пример: либо 1 строка в таблице, либо 300-400 строк, либо 600,000 строк. планы выполнения совершенно разные.
по факту, вариант parameter sniffing, разве что не входные параметры определяют план выполнения, а вызывающий объект + состояние таблиц.
сделали по 3 копии процедур (sp1, sp300, sp600k), и запускаем свою копию для каждого случая.

проблема решена, SomewhereSomehow и всем отвечавшим - большое спасибо!
26 июл 12, 14:33    [12919802]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
SomewhereSomehow
Member

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

Ну и вам спасибо за интересный кейс, надо будет на будущее сразу иметь ввиду возможность таких граблей.
Сохранил себе в KB.
26 июл 12, 14:47    [12919905]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
-=Гость=-
Guest
valv
SomewhereSomehow,

SomewhereSomehow
...., если посмотреть в логе то видно что запускается функция FCheckAndCleanupCachedTempTable (послее работы выводится что-то вроде "Deallocated 1 extent(s) starting at page 0001:000003b8".) Т.е. вроде сохраняются только метаданные таблиц.
...

всё отлично, удалось получить почти такую же картину (90% - вызов FCheckAndCleanupCachedTempTable и сохранение только одной страницы (метаданные), 10% - в кэше остаются заполненные таблицы, но хранится не больше одной таблицы каждого типа).

....


Valv, SomewhereSomehow

Подскажите пожалуйста - где вы наблюдаете запуск функции FCheckAndCleanupCachedTempTable (послее работы выводится что-то вроде "Deallocated 1 extent(s) starting at page 0001:000003b8".) ?

Интересно с точки зрения самообучения.
26 июл 12, 19:21    [12921736]     Ответить | Цитировать Сообщить модератору
 Re: вопрос о tempdb  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
-=Гость=-,

В логе транзакций. sqlskills - fn_dblog
26 июл 12, 19:35    [12921786]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить