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

Откуда:
Сообщений: 76
 		Доброе утро.

Скажите, почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?

Стал мониторить рост tempdb, и записываю в табличку каждые 3 минуты значение



SELECT 
		(SELECT (SUM(unallocated_extent_page_count)*1.0/128) FROM sys.dm_db_file_space_usage) free_space_in_MB,
(SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files) total_space_in_MB



Скрипт выше показывает свободного места столько же сколько и этот скрипт:
use tempdb
go
sp_spaceused @updateusage = 'TRUE'



Однако заметил что рост tempdb начинается даже когда unallocated space около 20Гб (это 50% от общего объема tempdb).



Почему так?


Может быть я размер свободного места неправельно определяю?
9 авг 17, 10:37    [20710146]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
 		Причем, даже когда tempdb заполонила все место на диске и выдает ошибку что не может больше рости -

unallocated space все равно показывает 15 Гб в запросе.
9 авг 17, 10:49    [20710182]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
 		Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64) Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
9 авг 17, 10:50    [20710185]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
 		Этот мониторинг неточный. Он не пересчитывает после каждого чиха все цифры.
9 авг 17, 10:57    [20710199]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
LSV
Этот мониторинг неточный. Он не пересчитывает после каждого чиха все цифры.



А как сделать точный?

updateusage?
9 авг 17, 10:58    [20710205]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
 		Неужели ни кто не знает?
9 авг 17, 11:26    [20710257]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
o-o
Guest 		я вообще не смотрю на свободное место, у нас у темпдб потолок 100Гб.
а переполнятелей темпдб и даже просто тех, кто чересчур расходует, я ловлю через такое
select session_id, 
	   cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
	   cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
	   cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
	   cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
       cast(sum(internal_objects_alloc_page_count - 
                internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
       cast(sum(user_objects_alloc_page_count - 
                user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
from sys.dm_db_task_space_usage 
group by session_id
having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count + 
      user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 1

этот код засунут во вью, а к нему уже джойню sys.dm_exec_sessions, etc.
раз в 5 минут превышатели гига логируются в таблицу со стэйтментом и планом
9 авг 17, 11:44    [20710310]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
o-o
Guest 		ну и да, никто еще не смог переполнить темпдб незамеченным,
ибо 5 минут на переполнение 100Гб маловато
9 авг 17, 11:45    [20710314]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
o-o
я вообще не смотрю на свободное место, у нас у темпдб потолок 100Гб.
а переполнятелей темпдб и даже просто тех, кто чересчур расходует, я ловлю через такое
select session_id, 
	   cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
	   cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
	   cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
	   cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
       cast(sum(internal_objects_alloc_page_count - 
                internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
       cast(sum(user_objects_alloc_page_count - 
                user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
from sys.dm_db_task_space_usage 
group by session_id
having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count + 
      user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 1

этот код засунут во вью, а к нему уже джойню sys.dm_exec_sessions, etc.
раз в 5 минут превышатели гига логируются в таблицу со стэйтментом и планом



О, круто!
9 авг 17, 12:25    [20710460]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9160
 		Использую такой запрос:
select
 t.session_id,
 sum(t.user_objects_alloc_page_count - t.user_objects_dealloc_page_count + t.internal_objects_alloc_page_count - t.internal_objects_dealloc_page_count) * 8192/1024/1024 as [tempdb usage, MB],
 q.[text] sql_text,
 s.[host_name],
 s.[program_name],
 s.login_name,
 db_name(s.database_id) from_db
from sys.dm_db_task_space_usage t
left join sys.dm_exec_requests z on z.session_id=t.session_id 
left join sys.dm_exec_sessions s on s.session_id = t.session_id
outer apply sys.dm_exec_sql_text(z.[sql_handle]) q
where t.database_id = db_id('tempdb')
group by t.session_id, q.[text], s.[host_name], s.[program_name], s.login_name, db_name(s.database_id)
having sum(t.user_objects_alloc_page_count - t.user_objects_dealloc_page_count + t.internal_objects_alloc_page_count - t.internal_objects_dealloc_page_count) * 8192/1024/1024 > 0;
9 авг 17, 12:29    [20710472]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
o-o
этот код засунут во вью, а к нему уже джойню sys.dm_exec_sessions, etc.




Кстати, не знаете как получить корректно текст запроса?

SELECT text FROM sys.dm_exec_sql_text(sp.sql_handle)


Обычные DML и SELECT пишено нормально.

Однако, при запуске процедуры, пишет не вызов процедуры с параметрами, а DDL создания процедуры.
9 авг 17, 12:30    [20710475]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
Владислав Колосов
Использую такой запрос:
select
 t.session_id,
 sum(t.user_objects_alloc_page_count - t.user_objects_dealloc_page_count + t.internal_objects_alloc_page_count - t.internal_objects_dealloc_page_count) * 8192/1024/1024 as [tempdb usage, MB],
 q.[text] sql_text,
 s.[host_name],
 s.[program_name],
 s.login_name,
 db_name(s.database_id) from_db
from sys.dm_db_task_space_usage t
left join sys.dm_exec_requests z on z.session_id=t.session_id 
left join sys.dm_exec_sessions s on s.session_id = t.session_id
outer apply sys.dm_exec_sql_text(z.[sql_handle]) q
where t.database_id = db_id('tempdb')
group by t.session_id, q.[text], s.[host_name], s.[program_name], s.login_name, db_name(s.database_id)
having sum(t.user_objects_alloc_page_count - t.user_objects_dealloc_page_count + t.internal_objects_alloc_page_count - t.internal_objects_dealloc_page_count) * 8192/1024/1024 > 0;


Вау.

Круто.

Однако процедуры показывает тоже как CREATE :)
9 авг 17, 12:33    [20710482]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9160
 		люблю скл,

DBCC INPUTBUFFER(SPID)
9 авг 17, 12:34    [20710486]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

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

DBCC INPUTBUFFER(SPID)



А скажите, его как-нибудь можно в запрос вставить, чтобы он в селекте показывал?
9 авг 17, 12:38    [20710494]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
o-o
Guest
люблю скл
Кстати, не знаете как получить корректно текст запроса?

select...
	   [individual query] = substring(t.text , r.statement_start_offset / 2 + 1, (
								case
									when r.statement_end_offset = - 1
									then len(convert(nvarchar(max), t.text)) * 2
									else r.statement_end_offset
								end - r.statement_start_offset
								) / 2)
from ...sys.dm_exec_requests r
        on s.session_id = r.session_id  
     outer apply sys.dm_exec_sql_text(r.sql_handle) t

s.session_id у вас есть во вью
9 авг 17, 12:41    [20710499]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
o-o
люблю скл
Кстати, не знаете как получить корректно текст запроса?

select...
	   [individual query] = substring(t.text , r.statement_start_offset / 2 + 1, (
								case
									when r.statement_end_offset = - 1
									then len(convert(nvarchar(max), t.text)) * 2
									else r.statement_end_offset
								end - r.statement_start_offset
								) / 2)
from ...sys.dm_exec_requests r
        on s.session_id = r.session_id  
     outer apply sys.dm_exec_sql_text(r.sql_handle) t

s.session_id у вас есть во вью



Большое спасибо!
9 авг 17, 12:46    [20710516]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
 		Получается по свободному месту бесполезно в нагиос-мониторинг темпДБ ставить.

Надо по 

sum(t.user_objects_alloc_page_count - t.user_objects_dealloc_page_count + t.internal_objects_alloc_page_count - t.internal_objects_dealloc_page_count)
9 авг 17, 12:58    [20710552]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
 		Еще раз спасибо.
9 авг 17, 12:58    [20710556]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
 		В продолжение темы вопрос:

Кончилось место в tempdb.

На момент, когда оно кончилось, запрос:

SELECT session_id, SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count 
FROM sys.dm_db_task_space_usage 
WHERE internal_objects_alloc_page_count>0
GROUP BY session_id, request_id



Показывал всего несколько небольших значений, самое большое было 10 000, это примерно 78Мб.

При этом темпдб выросла до 50Гб и в приложении была ошибка что она не может расти дальше.




Подскажите, как такое может быть что sys.dm_db_task_space_usage показывает загрузку tempdb около 100Mb, а в реальности загрузка в 500 раз больше?
9 авг 17, 15:47    [20711191]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
o-o
Guest 		еще бы: когда место закончилось, кому-то вывалилась об этом ошибка и его транзакция была откачена.
вернее, так: всем попавшим под раздачу в тот момент ошибка вывалилась.
их запросы с посяганием на темпдб были откачены.
что же можно увидеть, когда уже ничего нет?
надо в джоб сложить логирование в таблицу всех тех, кто перерасходует темпдб.
и вот когда место закончилось, читать лог, а не смотреть в темпдб, в котором уже места полно
9 авг 17, 15:52    [20711216]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

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



Совершенно верно, информация о заполнении логируется каждую минуту.

Именно оттуда я взял эти данные.


Т.е. за пол часа до переполнения, и во время переполнения sys.dm_db_task_space_usage показывает что сесии в общей сложности занимают около 100Мб из 50Гб.
9 авг 17, 16:10    [20711289]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
o-o
Guest
люблю скл
Т.е. за пол часа до переполнения, и во время переполнения sys.dm_db_task_space_usage показывает что сесии в общей сложности занимают около 100Мб из 50Гб.

так раз в минуту или раз в полчаса?
9 авг 17, 16:18    [20711319]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
o-o
Guest 		ну и ошибку покажите.
а то вдруг ищете переполнение дата файла,
а переполняется лог
9 авг 17, 16:21    [20711334]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
люблю скл
Member

Откуда:
Сообщений: 76
o-o
люблю скл
Т.е. за пол часа до переполнения, и во время переполнения sys.dm_db_task_space_usage показывает что сесии в общей сложности занимают около 100Мб из 50Гб.

так раз в минуту или раз в полчаса?


раз в минуту.
9 авг 17, 16:28    [20711359]     Ответить | Цитировать Сообщить модератору
 Re: Почему tempdb начинает рости, когда в ней unallocated space около 20 Гб запас?  [new]
o-o
Guest 		вот например был замечательный запрос с group by.
он полчаса сортировал,
затем отвалился с ожибкой переполнения.
но не за минуту ж он 100Гб переполнил

К сообщению приложен файл. Размер - 26Kb
9 авг 17, 16:31    [20711366]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить