Назад | Перейти на главную страницу

Как узнать, кто / что забивает SQL Server TempDB

Я использую SQL Server 2012 Enterprise. На сервере настроена одна группа доступности. На экземпляре есть несколько баз данных для разных инструментов. Все инструменты принадлежат друг другу и поэтому были развернуты вместе. Я заметил рост счетчика Perfmon Transaction / sec для TempDB после развертывания. До развертывания счетчик в среднем составлял около 30, а сейчас - около 300. Раньше у меня уже была похожая проблема, и я обнаружил, что в новой версии одного инструмента реализован курсор, который вызывается все время. Но на этот раз проблема не в этом. Я использовал профилировщик SQL для отслеживания событий курсора, но ничего интересного не нашел. Я также пытался отследить с помощью фильтра на самом tempDB, но похоже, что там ничего не происходит.

Есть ли у кого-нибудь идеи, как я могу узнать, кто или что вызывает эти высокие скорости транзакций в TempDB?

Я не уверен, может ли это быть проблемой производительности, но я хочу выяснить, почему эта цифра так сильно изменилась.

Раньше я использовал этот скрипт, чтобы узнать, у какого spid много страниц в tempdb:

;WITH s AS
(
    SELECT 
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count 
          + s.internal_objects_alloc_page_count) 
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count 
      + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text], 
  [statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text], 
        r.statement_start_offset / 2, 
        CASE WHEN r.statement_end_offset < r.statement_start_offset 
        THEN 0 
        ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
      ), ''
    ), t.[text])
FROM s
LEFT OUTER JOIN 
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

(не могу вспомнить, откуда взял, извините). Это не укажет вам на виновника (потому что он может писать одни и те же страницы снова и снова).

Используя этот сценарий, вы узнаете, кто выполняет много операций ввода-вывода:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

(Спасибо Дэйв!)

Объединение этих двух должно дать вам хорошее представление.