По материалам статьи Rahul Sharma на SWYNK.COM «Scheduling
SQL Server Profiler Trace using extended stored
Procedures»
В этой статье Рауль рассматривает то, как можно наладить
контроль работы SQL сервера в удобное время, используя для
этого задания и расширенные хранимые процедуры.
Что же
такое расширенные хранимые процедуры? Это DLL, которые SQL
сервер может динамически загружать, и они выполняются
непосредственно в адресном пространстве SQL сервера, используя
Open Data Services API.
Коротко рассмотрим, как они
работают:
- Когда клиент выполняет расширенную хранимую
процедуру, запрос от приложения клиента в базу данных
передаётся в формате табличного потока данных (TDS) через
сетевые библиотеки и Open Data Services.
- SQL сервер ищет
DLL, связанную с расширенной хранимой процедурой, и загружает
её, если она не была уже загружена.
- SQL сервер вызывает
требуемую расширенную хранимую процедуру (существующую подобно
функциям внутри DLL).
- Расширенная хранимая процедура
анализирует параметры своего запуска и возвращает серверу
определённые для возврата параметры, используя Open Data
Services API.
Вы можете также написать собственные
расширенные хранимые процедуры, чтобы реализовать новые
функциональные возможности и можете добавить их к определённым
в SQL сервере, используя системную хранимую процедуру
sp_addextendedproc.
В этой статье Рауль демонстрирует, как
Вы можете запрограммировать трассировку, фиксирующую
информацию для отладки проблем базы данных. В статье
используются следующие расширенные хранимые
процедуры:
Xp_trace_addnewqueue:
Добавляет новую очередь
трассировки и устанавливает значения её конфигурации, включая
размер очереди трассировки (число элементов, которое будут
единовременно содержаться в очереди
трассировки).
Xp_trace_destroyqueue:
Уничтожает очередь
трассировки, но не удаляет определение очереди. Если очередь
трассировки сохраняется в таблицу, SQL сервер не уничтожает
очередь трассировки, пока в таблицу не будут занесены все
события из очереди
трассировки.
Xp_trace_geteventnames:
Показывает названия
как f всех event
классов.
Xp_trace_seteventclassrequired:
Определяет
какие event классы необходимо отслеживать для указанной
очереди трассировки.
Xp_trace_settextfilter:
Определяет
текстовый фильтр, или текст, который должен быть включён или
исключён для указанных типов
событий.
Xp_trace_setappfilter:
Определяет фильтр
прикладных программ, или сами прикладные программы, которые
включаются или исключаются в
трассировке.
Xp_trace_sethpidfilter:
Определяет фильтр
по host process identification number
(HPID).
Xp_trace_setdbidfilter:
Определяет фильтр по
database identification number
(DBID).
Xp_trace_setqueuedestination:
Определяет фильтр
по таблице, куда выводится очередь трассировки, или по
таблице, в которую сохраняются указанные
события.
Xp_trace_startconsumer:
Начинает загрузку
трассировки в указанную таблицу
(xp_trace_setqueuedestination).
Xp_trace_enumqueuehandles:
Нумерует
все активные дескрипторы очереди
трассировки.
Xp_trace_getqueuedestination:
Получает имя
текущей таблицы загрузки результатов очереди трассировки.
Далее представлен текст на T-SQL, использующий описанные
выше расширенные хранимые процедуры:
USE master
GO
-- Declare variables
DECLARE
@old_queue_handle int -- Queue handle of currently running
trace queue
DECLARE @queue_handle int -- Queue handle for
new running trace queue
DECLARE @column_value int -- Data
column bitmask
DECLARE @trace_name varchar(40) -- Name of
the trace definition
DECLARE @date datetime -- Date
component of file name
DECLARE @file_name varchar(80) --
Trace file name
-- Set up trace definition
SELECT @trace_name =
'auto_trace'
-- Set up the trace file name
-- This
script appends the datetime to the file name each time it
is
-- run to create a new, unique file name.
SELECT @file_name = 'c:\temp\auto_trace_'
-- Stop the trace queue if running
IF EXISTS( SELECT name FROM sysobjects WHERE name =
'trace_queue_table' )
BEGIN
IF EXISTS(
SELECT * FROM trace_queue_table WHERE queue_name = @trace_name
)
BEGIN
SELECT
@old_queue_handle = queue_handleyes">
FROM
trace_queue_table
WHERE
queue_name =
@trace_name
EXEC
xp_trace_destroyqueue
@old_queue_handle
PRINT'Deleted
trace queue ' + CONVERT(VARCHAR(20),@old_queue_handle
)
END
END
-- Set the column mask for the data columns to
capture.
-- Text data | Binary data | Database ID |
Transaction ID | Connection ID|
-- Username | Domain name |
Host name | Host process ID | Application name|
-- SQL
username | SPID | Duration | Start time | End time | Reads |
Writes|
-- CPU usage | Severity | Event Subclass | Object
ID | Index ID |
-- Integer data | Server name | Event Class
SET @column_value
= 1|2|4|8|16|32|64|128|256|512|1024|2048|4096|8192|16384|32768|
65536|131072|524288|1048576|2097152|8388608|16777216|33554432|67108864
-- Create the trace queue
EXEC xp_trace_addnewqueue 11000, 10000, 95, 90,
@column_value, @queue_handle output
-- Specify the event classes to trace
-- To list all the
event classes, execute the procedure xp_trace_geteventnames
using Query Analyzer
-- or look up for that procedure in
SQL Server BOL and add to the following list as needed
EXEC xp_trace_seteventclassrequired @queue_handle, 11 ,1 --
RPC:Starting
EXEC xp_trace_seteventclassrequired
@queue_handle, 13 ,1 -- SQL:BatchStarting
EXEC
xp_trace_seteventclassrequired @queue_handle, 14 ,1 --
Connect
EXEC xp_trace_seteventclassrequired @queue_handle,
15 ,1 -- Disconnect
EXEC xp_trace_seteventclassrequired
@queue_handle, 16 ,1 -- Attention
EXEC
xp_trace_seteventclassrequired @queue_handle, 17 ,1 --
Existing Connection
EXEC xp_trace_seteventclassrequired
@queue_handle, 40 ,1 -- SQL:StmtStarting
EXEC
xp_trace_seteventclassrequired @queue_handle, 43 ,1 --
SP:Completed
EXEC xp_trace_seteventclassrequired
@queue_handle, 45 ,1 -- SP:StmtCompleted
EXEC
xp_trace_seteventclassrequired @queue_handle, 54 ,1 --
Transaction Log
EXEC xp_trace_seteventclassrequired
@queue_handle, 59 ,1 -- Lock:Deadlock Chain
EXEC
xp_trace_seteventclassrequired @queue_handle, 60 ,1 --
Lock:Escalation
EXEC xp_trace_seteventclassrequired
@queue_handle, 79 ,1 -- Missing column statistics
EXEC
xp_trace_seteventclassrequired @queue_handle, 80 ,1 -- Missing
join predicate
-- Set filters (don't trace the trace activity
itself)
-- We can add other filters like application name
etc. by looking at the xp_trace_set% procedures --in SQL
Server BOL
-- I have added only a couple of them
EXEC xp_trace_settextfilter @queue_handle, NULL, 'EXEC
xp_trace%;SET ANSI%'
EXEC xp_trace_setappfilter
@queue_handle, NULL, 'SQL Server Profiler%'
EXEC
xp_trace_setappfilter @queue_handle, NULL, 'MS
SQLEM'
--EXEC xp_trace_setappfilter @queue_handle, NULL,
'MS SQL Query Analyzer'
--When debugging the problem, check the spid of a
particular process thru sp_who2 and then
--use
xp_trace_sethpidfilter to filter on the host process
--Exec
xp_trace_sethpidfilter @queue_handle, @Process_ID
--Set the
database ID filter to trace only for the PKMS001 database or
for whichever database we wish
DECLARE @DBID INT
SELECT @DBID = DBID FROM
MASTER..SYSDATABASES WHERE NAME = ''
EXEC
xp_trace_setdbidfilter @queue_handle, @DBID
-- Configure the queue to write to a file
SELECT @date = getdate()
SELECT @file_name = @file_name
+
CONVERT( varchar(4), datepart(yy,@date)) +
CONVERT(
varchar(2), datepart(mm,@date)) +
CONVERT( varchar(2),
datepart(dd,@date)) +
CONVERT( varchar(2),
datepart(hh,@date)) +
CONVERT( varchar(2),
datepart(mi,@date)) +
CONVERT( varchar(2),
datepart(ss,@date)) +'.trc'
PRINT 'The trace file name is :
'+ @file_name
EXEC xp_trace_setqueuedestination
@queue_handle, 2, 1, NULL, @file_name
-- Start the consumer that actually writes to the file
EXEC xp_trace_startconsumer @queue_handle
-- Record the trace queue handle for subsequent jobs.
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name =
'trace_queue_table' )
BEGIN
CREATE
TABLE trace_queue_table ( queue_handle int, queue_name
varchar(20) )
INSERT INTO
trace_queue_table VALUES( @queue_handle, @trace_name
)
PRINT 'Created table and inserted queue
handle ' + CONVERT( varchar(20), @queue_handle
)
END
ELSE
BEGIN
IF EXISTS (
SELECT * FROM trace_queue_table WHERE queue_name = @trace_name
)
BEGIN
UPDATE
trace_queue_table SET queue_handle = @queue_handle WHERE
queue_name =
@trace_name
PRINT
'Updated table with new queue handle ' + CONVERT(varchar(20),
@queue_handle
)
END
ELSE
BEGIN
INSERT
INTO trace_queue_table VALUES( @queue_handle, @trace_name
)
PRINT 'Inserted row
into table with new queue handle ' + CONVERT(
varchar(20), @queue_handle
)
END
END
-- Get a list of all the trace queues and their
corresponding queue handles
-- if entered before adding new
queues and choose a queue handle to destroy
SELECT * FROM trace_queue_table
-- If a queue is not entered into the trace table, you can
get a list of queues running on the Server by
-- executing
the following extended stored procedure and choose a queue
handle to destroy
EXEC xp_trace_enumqueuehandles
-- Destroy the queue
EXEC xp_trace_destroyqueue
--For Example,
EXEC xp_trace_destroyqueue 14
EXEC
xp_trace_getqueuedestination 12 --@queue_handle