Просмотр блокировок в SQL Server

После того как мы убедились, что серверу хватает ресурсов, можно переходить к просмотру блокировок.

Блокировки можно посмотреть через Activity Monitor в SSMS, но мы воспользуемся T-SQL, так как этот вариант более удобен и нагляден. Выполняем запрос:

SET NOCOUNT ONGOSELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ') AS BATCHINTO #TFROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) TGOWITHBLOCKERS (SPID, BLOCKED, LEVEL, BATCH)AS(SELECT SPID,BLOCKED,CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,BATCHFROM #T RWHERE (BLOCKED = 0 OR BLOCKED = SPID)AND EXISTS (SELECT *FROM #T R2WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)UNION ALLSELECT R.SPID,R.BLOCKED,CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,R.BATCHFROM #T AS RINNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPIDWHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID)SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +CASE WHEN (LEN(LEVEL)/4 - 1) = 0THEN 'HEAD - 'ELSE '|------ ' END+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREEFROM BLOCKERSORDER BY LEVEL ASCGODROP TABLE #TGO

Этот запрос возвращает список блокировок в виде дерева. Это удобно в работе, так как обычно, если возникает одна блокировка, она провоцирует за собой другие. Аналогично в Activity Monitor или в выводе sp_who2 можно увидеть поле “Blocked By”.

Если запрос ничего не вернул, то блокировок нет.

Если запрос вернул какие-то данные, то нужно проанализировать цепочку.

HEAD значит что этот запрос является причиной всех остальных блокировок ниже по дереву. 64 – это идентификатор процесса (SPID). После этого пишется тело запроса, который вызвал блокировку. Если у вас хватает ресурсов сервера, то скорее всего дело в самом запросе и во взаимном обращении к каким-то объектам. Для того чтобы сказать точнее, нужно анализировать конкретный запрос, который вызвал блокировку.

Политики SQL Server

Даже когда у вас всё работает хорошо и жалоб нет, на самом деле может быть много проблем, которые всплывут позже. Для этого в SQL Server есть политики.

Политика в SQL Server это, грубо говоря, проверка правила на соответствие заданному значению. Например, с помощью политик вы можете убедиться, что на всех базах на сервере выключен Auto Shrink. Рассмотрим пример импорта и выполнения политики

В SSMS, подключитесь к серверу, на котором хотите выполнять политики (Management -> раздел Policy Management).

Импортируем файл Database Auto Shrink.xml.

 

Нажмите Вычислить

Пусть на экземпляре node1 две базы данных, test1 и test2. На test2 включен autoshrink.

Посмотрим детали.

Политика определила включенный параметр AutoShrink, в описании обычно пишется объяснения к правилам. В данном случае дается объяснение почему auto shrink лучше отключать.

Политики могут выполняться либо по расписанию, либо по требованию (разово). Результаты выполнения политики можно посмотреть в журнале политик.

Монитор активности

 

При устранении проблем, связанных с производительностью, или отслеживании работы сервера в реальном времени администратор обычно запускает ряд скриптов или проверяет соответствующие источники сведений, чтобы собрать общие данные о выполняющихся процессах и выявить причину проблемы. Монитор активности SQL Server 2008 объединяет такие сведения, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам. Администратор БД может как просматривать высокоуровневые сведения, так и проанализировать любой из процессов более детально и ознакомиться со статистикой ожидания, что облегчает выявление и разрешение проблем.

Чтобы открыть монитор активности, щелкните правой кнопкой мыши на имени зарегистрированного сервера в обозревателе объектов, затем выберите Монитор активности или же воспользуйтесь стандартным значком на панели инструментов в среде SQL Server Management Studio. Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов.

Вид представления Монитора активности SQL Server 2008 в среде Management Studio

Примечание. Монитор активности использует параметр периодичности обновления данных, который можно изменить щелчком правой кнопки мыши. При выборе частого обновления данных (раз в менее чем 10 секунд) производительность высоконагруженной рабочей системы может понизиться.

С помощью монитора активности администратор также может выполнять следующие задачи:

· Приостанавливать и возобновлять работу монитора активности одним щелчком правой кнопки мыши. Это позволяет администратору «сохранить» сведения о состоянии на определенный момент времени, они не будут обновлены или перезаписаны. Но не забывайте, что при обновлении данных вручную, развертывании или сворачивании раздела старые данные будут обновлены и утеряны.

· Щелкните правой кнопкой мыши элемент строки, чтобы отобразить полный текст запроса или графический план выполнения с помощью пункта меню «Последние ресурсоемкие запросы».

· Выполнять трассировку приложением Profiler или завершать процессы в представлении «Процессы». События приложения Profiler включают события RPC:Completed, SQL:BatchStarting и SQL:BatchCompleted, а также Audit Login и AuditLogout.

Монитор активности также позволяет отслеживать активность любого локального или удаленного экземпляра SQL Server 2005, зарегистрированного в среде SQL Server Management Studio.