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

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

Есть олвейс-он кластер.


На реплике разрастается темпдб.





А как посмотреть какие именно сессии ее используют?
22 ноя 21, 14:02    [22399096]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Gallemar
Member

Откуда:
Сообщений: 5675
+
-- space used in Tempdb:
select
    t1.session_id
    /*, t1.request_id*/
    , task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
    , task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
    , host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
    , s1.login_name
    , s1.status
    , s1.last_request_start_time
    , s1.last_request_end_time
    , s1.row_count
    , s1.transaction_isolation_level
    , query_text=
        coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1
              THEN LEN(CONVERT(nvarchar(max),text)) * 2
                   ELSE statement_end_offset
              END - t2.statement_start_offset)/2)
        FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
    , query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
    (Select session_id, request_id
    , task_alloc_pages=sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count)
    , task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
    from sys.dm_db_task_space_usage
    group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
    t1.session_id = t2.session_id
    and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
    t1.session_id=s1.session_id
where
    t1.session_id > 50 -- ignore system unless you suspect there's a problem there
    and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO

-- sessions which are using TempDB currently:
SELECT
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutSt_int_obj_p_count,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutSt_user_obj_p_count,
--st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
--st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id,
--dmv_tsu.request_id,
dmv_tsu.exec_context_id,
--(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
--(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC
22 ноя 21, 14:29    [22399118]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
Gallemar
+
-- space used in Tempdb:
select
    t1.session_id
    /*, t1.request_id*/
    , task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
    , task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
    , host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
    , s1.login_name
    , s1.status
    , s1.last_request_start_time
    , s1.last_request_end_time
    , s1.row_count
    , s1.transaction_isolation_level
    , query_text=
        coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1
              THEN LEN(CONVERT(nvarchar(max),text)) * 2
                   ELSE statement_end_offset
              END - t2.statement_start_offset)/2)
        FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
    , query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
    (Select session_id, request_id
    , task_alloc_pages=sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count)
    , task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
    from sys.dm_db_task_space_usage
    group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
    t1.session_id = t2.session_id
    and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
    t1.session_id=s1.session_id
where
    t1.session_id > 50 -- ignore system unless you suspect there's a problem there
    and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO

-- sessions which are using TempDB currently:
SELECT
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutSt_int_obj_p_count,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutSt_user_obj_p_count,
--st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
--st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id,
--dmv_tsu.request_id,
dmv_tsu.exec_context_id,
--(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
--(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC




Да в том-то и дело....


The target database ('Prod') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
22 ноя 21, 15:01    [22399134]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Gallemar
Member

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

добавьте use master;
22 ноя 21, 15:11    [22399138]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
Gallemar
qqq_2,

добавьте use master;


ошибка та же.
22 ноя 21, 15:17    [22399139]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Gallemar
Member

Откуда:
Сообщений: 5675
qqq_2, не верю. Покажи целиком какой запрос ты выполняешь.
22 ноя 21, 15:24    [22399142]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
Gallemar
qqq_2, не верю. Покажи целиком какой запрос ты выполняешь.


Я бы хотел тоже не верить, но факт есть факт.

К сообщению приложен файл. Размер - 108Kb
22 ноя 21, 15:32    [22399145]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Gallemar
Member

Откуда:
Сообщений: 5675
а такой если выполнить?
+
-- space used in Tempdb:
use master;
select
t1.session_id
/*, t1.request_id*/
, task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
, task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
, host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
, s1.login_name
, s1.status
, s1.last_request_start_time
, s1.last_request_end_time
, s1.row_count
, s1.transaction_isolation_level
, query_text=
coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
, query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
(Select session_id, request_id
, task_alloc_pages=sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)
, task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
t1.session_id = t2.session_id
and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
t1.session_id=s1.session_id
where
t1.session_id > 50 -- ignore system unless you suspect there's a problem there
and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO
22 ноя 21, 15:43    [22399150]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
Gallemar
а такой если выполнить?
+
-- space used in Tempdb:
use master;
select
t1.session_id
/*, t1.request_id*/
, task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
, task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
, host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
, s1.login_name
, s1.status
, s1.last_request_start_time
, s1.last_request_end_time
, s1.row_count
, s1.transaction_isolation_level
, query_text=
coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
, query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
(Select session_id, request_id
, task_alloc_pages=sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)
, task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
t1.session_id = t2.session_id
and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
t1.session_id=s1.session_id
where
t1.session_id > 50 -- ignore system unless you suspect there's a problem there
and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO



Точно такая же ошибка.
22 ноя 21, 18:00    [22399265]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
В SQL Server наверное редко используется кластер и ошибку подобную мало кто встречал (((
22 ноя 21, 18:01    [22399268]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Gallemar
Member

Откуда:
Сообщений: 5675
qqq_2
Gallemar
а такой если выполнить?
+
-- space used in Tempdb:
use master;
select
t1.session_id
/*, t1.request_id*/
, task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
, task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
, host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
, s1.login_name
, s1.status
, s1.last_request_start_time
, s1.last_request_end_time
, s1.row_count
, s1.transaction_isolation_level
, query_text=
coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
, query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
(Select session_id, request_id
, task_alloc_pages=sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)
, task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
t1.session_id = t2.session_id
and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
t1.session_id=s1.session_id
where
t1.session_id > 50 -- ignore system unless you suspect there's a problem there
and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO



Точно такая же ошибка.


Не верю. Проверяю в аналогичных условиях. Такая ошибка при попытке сделать запрос к неслужебной бд
22 ноя 21, 18:46    [22399303]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
ПалЪ СанычЪ
Member

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

Tak moget poprobovat ustanovit context "intent is set to read only" v connect SSMS?
22 ноя 21, 19:43    [22399334]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Gallemar
Member

Откуда:
Сообщений: 5675
ПалЪ СанычЪ
qqq_2,

Tak moget poprobovat ustanovit context "intent is set to read only" v connect SSMS?

Как вариант, хотя должно работать и без этого.
22 ноя 21, 20:00    [22399344]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Рядом стоял
Member

Откуда:
Сообщений: 4
ПалЪ СанычЪ
qqq_2,

Tak moget poprobovat ustanovit context "intent is set to read only" v connect SSMS?

либо так.

либо в запросе вытереть кусок про получение плана запроса
22 ноя 21, 20:09    [22399355]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
ПалЪ СанычЪ
qqq_2,

Tak moget poprobovat ustanovit context "intent is set to read only" v connect SSMS?


Спасибо, так сработало.
22 ноя 21, 21:19    [22399430]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
Gallemar
ПалЪ СанычЪ
qqq_2,

Tak moget poprobovat ustanovit context "intent is set to read only" v connect SSMS?

Как вариант, хотя должно работать и без этого.


Без этого не хочет.


А подскажите, как этот параметр в SQLAgent вставить?
22 ноя 21, 21:20    [22399432]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
Подскажите как

в sqlcmd

впихнуть context intent is set to read only

?
24 ноя 21, 19:30    [22400381]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
qqq_2
Подскажите как

в sqlcmd

впихнуть context intent is set to read only

?



Нашел


-K ReadOnly
24 ноя 21, 19:35    [22400384]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
В общем, мне надо сохранить куда-то резальтат этого скрипта, а как не пойму, ведь БД на реплики в реадонли и туда ничего не пишется :(
24 ноя 21, 19:36    [22400385]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
Gallemar
Member

Откуда:
Сообщений: 5675
qqq_2
В общем, мне надо сохранить куда-то резальтат этого скрипта, а как не пойму, ведь БД на реплики в реадонли и туда ничего не пишется :(

сделай экспорт результата в csv
24 ноя 21, 20:53    [22400407]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
komrad
Member

Откуда:
Сообщений: 5910
qqq_2
В общем, мне надо сохранить куда-то резальтат этого скрипта, а как не пойму, ведь БД на реплики в реадонли и туда ничего не пишется :(

сохранить можно
- в темпдб (до рестарта)
- в master/msdb
- через линкед-сервер куда-то еще
24 ноя 21, 21:07    [22400412]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
Gallemar
qqq_2
В общем, мне надо сохранить куда-то резальтат этого скрипта, а как не пойму, ведь БД на реплики в реадонли и туда ничего не пишется :(

сделай экспорт результата в csv


Так и сделал.
26 ноя 21, 15:57    [22401362]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
komrad
qqq_2
В общем, мне надо сохранить куда-то резальтат этого скрипта, а как не пойму, ведь БД на реплики в реадонли и туда ничего не пишется :(

сохранить можно
- в темпдб (до рестарта)
- в master/msdb
- через линкед-сервер куда-то еще


Через линкед не получилось.



Но вопрос уже не в этом.
26 ноя 21, 15:58    [22401363]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
qqq_2
Member

Откуда:
Сообщений: 88
В общем, проблема такая:


Когда дико начинает рости tempdb (с 1 Гб до 200Гб за пол часа),


этот скрипт:

22399145


Не показывает ни одной сессии, которая бы как-либо сильно использовала tempdb.

Т.е. я его выполнил во время роста раз 10 и он ни разу не показал использование темп БД более 100мб.

Суммарно по всем сессия мож 200Мб и было, но не больше.


Скажите, как увидеть кто же использует tempdb в действительности?

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

но в сессия везде рид коммитет показывает (
26 ноя 21, 16:02    [22401369]     Ответить | Цитировать Сообщить модератору
 Re: А как посмотреть кто использует темпдб на read-only ноде в кластере?  [new]
komrad
Member

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

посмотрите, может у вас version store место отъедает
https://thesurfingdba.weebly.com/my-version-store-is-huge.html
26 ноя 21, 16:13    [22401383]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить