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

Откуда:
Сообщений: 58
Доброе время суток!
У меня такая проблема: Нужно что бы все блокировки логировались в отдельный файл, с указанием времени и продолжительности блокировки, а также запроса и пользователя вызвавшего блокировку.
Подскажите, хоть в какую сторону копать, спасибо.
13 мар 14, 13:35    [15717082]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Вы, наверное, просто не представляете их количество. Или имеете в виду какие-то особые блокировки.
13 мар 14, 13:44    [15717184]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Рабинович
Member

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

Имею ввиду, deadlock`и и блокировки которые длятся дольше определенного периода времени, например.
13 мар 14, 13:48    [15717245]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Если Вы преследуете цель быстрого реагирования на проблемы, то создайте alert. Агент SQL server - предупреждения.
13 мар 14, 13:56    [15717329]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Рабинович
Member

Откуда:
Сообщений: 58
Владислав Колосов,
Да, спасибо дельный совет.

Но нужен именно лог, за определенной период: день, неделя, месяц и т.п.
13 мар 14, 14:02    [15717406]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Рабинович
Владислав Колосов,
Да, спасибо дельный совет.

Но нужен именно лог, за определенной период: день, неделя, месяц и т.п.
Профайлером собирайте.

Сообщение было отредактировано: 13 мар 14, 14:08
13 мар 14, 14:07    [15717485]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Рабинович
Нужно что бы все блокировки логировались в отдельный файл, с указанием времени и продолжительности блокировки, а также запроса и пользователя вызвавшего блокировку.
Подскажите, хоть в какую сторону копать, спасибо.
SQL Server Profiler
13 мар 14, 14:08    [15717490]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Рабинович,

Пользуюсь этой утилитой. Она как раз логирует блокировки и отображает их в понятном виде, утилита бесплатная, но есть минус. она создает определенную нагрузку на базу
13 мар 14, 14:24    [15717695]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Рабинович
Member

Откуда:
Сообщений: 58
Гавриленко Сергей Алексеевич,

Извиняюсь за нубство, это получается профайлер должен будет запущен 24/7? А если система перезагрузится?

P.S.

А в принципе реально два таких триггера создать которые реагировали на появление и снятие блокировки?
13 мар 14, 15:28    [15718291]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Рабинович
Гавриленко Сергей Алексеевич,
Извиняюсь за нубство, это получается профайлер должен будет запущен 24/7? А если система перезагрузится?
Extended Events / трассу запускать процедурой в джобе при старте сервера.
13 мар 14, 15:48    [15718431]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
Рабинович,

не фонтан конечно, но мож. подойдёт для Ваших нужд:

CREATE TABLE [dbo].[Blocking](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Datum] [datetime] NULL,
	[WaitingSessionID] [smallint] NULL,
	[BlockingSessionID] [smallint] NULL,
	[WaitingUserSessionLogin] [nvarchar](128) NOT NULL,
	[BlockingUserSessionLogin] [nvarchar](128) NOT NULL,
	[WaitingUserConnectionLogin] [nvarchar](128) NOT NULL,
	[BlockingSessionConnectionLogin] [nvarchar](128) NOT NULL,
	[WaitDuration_Sec] [decimal](14, 2) NULL,
	[WaitType] [nvarchar](60) NULL,
	[WaitRequestMode] [nvarchar](60) NOT NULL,
	[WaitingProcessStatus] [nvarchar](30) NULL,
	[BlockingSessionStatus] [nvarchar](30) NULL,
	[WaitResource] [nvarchar](256) NOT NULL,
	[WaitResourceType] [nvarchar](60) NOT NULL,
	[WaitResourceDatabaseID] [int] NOT NULL,
	[WaitResourceDatabaseName] [nvarchar](128) NULL,
	[WaitResourceDescription] [nvarchar](1024) NULL,
	[WaitingSessionProgramName] [nvarchar](128) NULL,
	[BlockingSessionProgramName] [nvarchar](128) NULL,
	[WaitingHost] [nvarchar](128) NULL,
	[BlockingHost] [nvarchar](128) NULL,
	[WaitingCommandType] [nvarchar](16) NOT NULL,
	[WaitingCommandText] [nvarchar](max) NULL,
	[BlockingCommanText] [nvarchar](max) NULL,
	[WaitingCommandRowCount] [bigint] NOT NULL,
	[WaitingCommandPercentComplete] [real] NOT NULL,
	[WaitingCommandCPUTime] [int] NOT NULL,
	[WaitingCommandTotalElapsedTime] [int] NOT NULL,
	[WaitingCommandReads] [bigint] NOT NULL,
	[WaitingCommandWrites] [bigint] NOT NULL,
	[WaitingCommandLogicalReads] [bigint] NOT NULL,
	[WaitingCommandQueryPlan] [xml] NULL,
	[WaitingCommandPlanHandle] [varbinary](64) NULL,
   PRIMARY KEY CLUSTERED ([ID] ASC) 
)  


create view [dbo].[v_Blocking]
as
WITH [Blocking]
AS 
(
   SELECT w.[session_id]
   ,s.[original_login_name]
   ,s.[login_name]
   ,w.[wait_duration_ms]
   ,w.[wait_type]
   ,r.[status]
   ,r.[wait_resource]
   ,w.[resource_description]
   ,s.[program_name]
   ,w.[blocking_session_id]
   ,s.[host_name]
   ,r.[command]
   ,r.[percent_complete]
   ,r.[cpu_time]
   ,r.[total_elapsed_time]
   ,r.[reads]
   ,r.[writes]
   ,r.[logical_reads]
   ,r.[row_count]
   ,q.[text]
   ,q.[dbid]
   ,p.[query_plan]
   ,r.[plan_handle]
 FROM 
             [sys].[dm_os_waiting_tasks] w
 INNER JOIN  [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
 INNER JOIN  [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
 CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
 CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
 WHERE 1=1
 and w.[session_id] > 50
 and w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT','ASYNC_NETWORK_IO')
)

SELECT 
      b.[session_id] AS [WaitingSessionID]
      ,b.[blocking_session_id] AS [BlockingSessionID]                     
      ,b.[login_name] AS [WaitingUserSessionLogin]
      ,s1.[login_name] AS [BlockingUserSessionLogin]
      ,b.[original_login_name] AS [WaitingUserConnectionLogin] 
      ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
      ,cast(b.[wait_duration_ms] / 1000.0 as decimal(14,2)) AS [WaitDuration_Sec]
      ,b.[wait_type] AS [WaitType]
      ,t.[request_mode] AS [WaitRequestMode]
      ,UPPER(b.[status]) AS [WaitingProcessStatus]
      ,UPPER(s1.[status]) AS [BlockingSessionStatus]
      ,b.[wait_resource] AS [WaitResource]
      ,t.[resource_type] AS [WaitResourceType]
      ,t.[resource_database_id] AS [WaitResourceDatabaseID]
      ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
      ,b.[resource_description] AS [WaitResourceDescription]
      ,b.[program_name] AS [WaitingSessionProgramName]
      ,s1.[program_name] AS [BlockingSessionProgramName]
      ,b.[host_name] AS [WaitingHost]
      ,s1.[host_name] AS [BlockingHost]
      ,b.[command] AS [WaitingCommandType]
      ,b.[text] AS [WaitingCommandText]
      ,(SELECT  [text] FROM  sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) where r.session_id = b.[blocking_session_id]) as BlockingCommanText
      ,b.[row_count] AS [WaitingCommandRowCount]
      ,b.[percent_complete] AS [WaitingCommandPercentComplete]
      ,b.[cpu_time] AS [WaitingCommandCPUTime]
      ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
      ,b.[reads] AS [WaitingCommandReads]
      ,b.[writes] AS [WaitingCommandWrites]
      ,b.[logical_reads] AS [WaitingCommandLogicalReads]
      ,b.[query_plan] AS [WaitingCommandQueryPlan]
      ,b.[plan_handle] AS [WaitingCommandPlanHandle]
      
 
FROM
            [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1 ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t     ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'

GO



А это из джоба:

set nocount on;

----------------------------
declare @MailTo varchar(100), @bodytext varchar(500)
set  @MailTo   = 'abc@xyc.com'
set  @bodytext = 'new Blocking' 
----------------------------

insert tools..Blocking
(WaitingSessionID, BlockingSessionID, WaitingUserSessionLogin, BlockingUserSessionLogin, WaitingUserConnectionLogin, BlockingSessionConnectionLogin, WaitDuration_Sec, WaitType, WaitRequestMode, WaitingProcessStatus, BlockingSessionStatus, WaitResource, WaitResourceType, WaitResourceDatabaseID, WaitResourceDatabaseName, WaitResourceDescription, WaitingSessionProgramName, BlockingSessionProgramName, WaitingHost, BlockingHost, WaitingCommandType, WaitingCommandText, BlockingCommanText, WaitingCommandRowCount, WaitingCommandPercentComplete, WaitingCommandCPUTime, WaitingCommandTotalElapsedTime, WaitingCommandReads, WaitingCommandWrites, WaitingCommandLogicalReads, WaitingCommandQueryPlan, WaitingCommandPlanHandle)
select 
WaitingSessionID, BlockingSessionID, WaitingUserSessionLogin, BlockingUserSessionLogin, WaitingUserConnectionLogin, BlockingSessionConnectionLogin, WaitDuration_Sec, WaitType, WaitRequestMode, WaitingProcessStatus, BlockingSessionStatus, WaitResource, WaitResourceType, WaitResourceDatabaseID, WaitResourceDatabaseName, WaitResourceDescription, WaitingSessionProgramName, BlockingSessionProgramName, WaitingHost, BlockingHost, WaitingCommandType, WaitingCommandText, BlockingCommanText, WaitingCommandRowCount, WaitingCommandPercentComplete, WaitingCommandCPUTime, WaitingCommandTotalElapsedTime, WaitingCommandReads, WaitingCommandWrites, WaitingCommandLogicalReads, WaitingCommandQueryPlan, WaitingCommandPlanHandle
from Tools..v_Blocking
where WaitDuration_Sec > 0.25

if @@rowcount > 0
  EXEC msdb.dbo.sp_send_dbmail    
    @recipients = @MailTo,
    @body =       @bodytext,
    @subject =    'neue Blocking' ;
14 мар 14, 17:00    [15725731]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Рабинович
Member

Откуда:
Сообщений: 58
Alexander Us,

Огромное Вам спасибо.
То что нужно.
17 мар 14, 16:24    [15739623]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
Рабинович
Member

Откуда:
Сообщений: 58
Alexander Us,

Огромное Вам спасибо.
То что нужно.
17 мар 14, 16:24    [15739626]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование блокировок  [new]
SERG1257
Member

Откуда:
Сообщений: 2873
Гавриленко Сергей Алексеевич
Extended Events / трассу запускать процедурой в джобе при старте сервера.
Как бы нет необходимости запускать из джоба. У сессии есть галка "стартовать при старте сервера"
http://technet.microsoft.com/en-us/library/gg471585.aspx
17 мар 14, 18:46    [15740718]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить