Thursday, November 22, 2012

0 SQL Server Trace

SQL Server Trace gives us ability to see what is happening inside the SQL Server.  There are many events that we can defined what information we like to see from the trace. The more we enable and the more it may cost SQL Server to split those information.

Server Side Trace  Client  Trace

SQL Server Profiler is the GUI tool to create the trace event. By default, it is running the Client side trace, unless you explicitly want the server side trace. After SQL Server generate the trace, it would pass the trace to the IO Provider. SQL server Support 2 types of IO Providers.

  • Flat-file provider
  • Rowset provider

Because the Profiler use the Rowset provider by default in the Client side trace mode. It add more load on SQL Server .  Linchi Shea’s benchmarking also has elaborate more detail on this point.

The screenshot below show how to enable the Server Trace when use profiler.

image

How to see what Trace is currently running

select * from  sys.traces 

image

By default, you will at least see trace id 1 is enable. It is default trace that SQL Server defined.

How to load the trace to the table

Here is the SQL form James Rowland-Jones in the Book: “Professional SQL Server 2008: Internals and Performance Tuning”.

The SQL demo use fn_trace_gettable to load the trace file to the table. The fn_trace_gettable takes 2 parameters, filename and number of the files. Default means all the roll over traces files.

/*
Author: James Rowland-Jones
Title:  Professional SQL Server 2008: Internals and Performance Tuning
*/
SELECT data.*
INTO   DefaultTrace1 
FROM   sys.traces t
CROSS APPLY
       fn_trace_gettable(t.path,default) data
WHERE  is_default = 1
 
 
/*
Author: James Rowland-Jones
Title:  Professional SQL Server 2008: Internals and Performance Tuning
*/
 
CREATE TABLE dbo.DefaultTrace2
(       DefaultTraceID     int identity(1,1)   NOT NULL
       ,TextData           ntext                   NULL
    ,BinaryData         image                   NULL
    ,DatabaseID         int                     NULL
    ,TransactionID      bigint                  NULL
    ,LineNumber         int                     NULL
    ,NTUserName         nvarchar(256)           NULL
    ,NTDomainName       nvarchar(256)           NULL
    ,HostName           nvarchar(256)           NULL
    ,ClientProcessID    int                     NULL
    ,ApplicationName    nvarchar(256)           NULL
    ,LoginName          nvarchar(256)           NULL
    ,SPID               int                     NULL
    ,Duration           bigint                  NULL
    ,StartTime          datetime                NULL
    ,EndTime            datetime                NULL
    ,Reads              bigint                  NULL
    ,Writes             bigint                  NULL
    ,CPU                int                     NULL
    ,[Permissions]      bigint                  NULL
    ,Severity           int                     NULL
    ,EventSubClass      int                     NULL
    ,ObjectID           int                     NULL
    ,Success            int                     NULL
    ,IndexID            int                     NULL
    ,IntegerData        int                     NULL
    ,ServerName         nvarchar(256)           NULL
    ,EventClass         int                     NULL
    ,ObjectType         int                     NULL
    ,NestLevel          int                     NULL
    ,[State]            int                     NULL
    ,Error              int                     NULL
    ,Mode               int                     NULL
    ,Handle             int                     NULL
    ,ObjectName         nvarchar(256)           NULL
    ,DatabaseName       nvarchar(256)           NULL
    ,[FileName]         nvarchar(256)           NULL
    ,OwnerName          nvarchar(256)           NULL
    ,RoleName           nvarchar(256)           NULL
    ,TargetUserName     nvarchar(256)           NULL
    ,DBUserName         nvarchar(256)           NULL
    ,LoginSid           image                   NULL
    ,TargetLoginName    nvarchar(256)           NULL
    ,TargetLoginSid     image                   NULL
    ,ColumnPermissions  int                     NULL
    ,LinkedServerName   nvarchar(256)           NULL
    ,ProviderName       nvarchar(256)           NULL
    ,MethodName         nvarchar(256)           NULL
    ,RowCounts          bigint                  NULL
    ,RequestID          int                     NULL
    ,XactSequence       bigint                  NULL
    ,EventSequence      bigint                  NULL
    ,BigintData1        bigint                  NULL
    ,BigintData2        bigint                  NULL
    ,[GUID]             uniqueidentifier        NULL
    ,IntegerData2       int                     NULL
    ,ObjectID2          bigint                  NULL
    ,[Type]             int                     NULL
    ,OwnerID            int                     NULL
    ,ParentName         nvarchar(256)           NULL
    ,IsSystem           int                     NULL
    ,Offset             int                     NULL
    ,SourceDatabaseID   int                     NULL
    ,SqlHandle          image                   NULL
    ,SessionLoginName   nvarchar(256)           NULL
    ,PlanHandle         image                   NULL
    ,GroupID            int                     NULL
) ON [PRIMARY];
GO
 
INSERT INTO DefaultTrace2 WITH (TABLOCKX)
SELECT data.*
FROM   sys.traces t
CROSS APPLY
       fn_trace_gettable(t.path,default) data
WHERE  is_default = 1
ORDER BY EventSequence 

Find what Event is enable for the Trace

/*
Author: James Rowland-Jones
Title:  Professional SQL Server 2008: Internals and Performance Tuning
*/
DECLARE @vTraceID INT;
SET     @vTraceID = 1;
 
WITH filter AS
(   SELECT   columnid
            ,CASE   logical_operator
                    WHEN 0 THEN 'AND'
                    WHEN 1 THEN 'OR'
                    ELSE 'err'
             END AS logical_operator
            ,CASE   comparison_operator
                    WHEN 0 THEN ' = '
                    WHEN 1 THEN ' <> '
                    WHEN 2 THEN ' > '
                    WHEN 3 THEN ' < '
                    WHEN 4 THEN ' >= '
                    WHEN 5 THEN ' <= '
                    WHEN 6 THEN ' LIKE '
                    WHEN 7 THEN ' NOT LIKE '
              END AS comparison_operator
            ,value
    FROM  ::fn_trace_getfilterinfo(@vTraceID)
)         
SELECT   cat.name AS CategoryName
        ,evt.name AS EventName
        ,col.name AS ColumnName
        ,STUFF  (   (   SELECT ' | ' + child_fil.logical_operator
                                     + child_fil.comparison_operator
                                     + CAST(child_fil.value AS VARCHAR(MAX)
                    )
                        FROM   filter child_fil
                        WHERE  parent_fil.columnid = child_fil.columnid
                        FOR XML PATH ('') 
                    ),1,1,''
                ) AS ColFilters
FROM    fn_trace_geteventinfo(@vTraceID) evi
JOIN    sys.trace_events evt                
ON      evi.eventid         = evt.trace_event_id
JOIN    sys.trace_categories cat            
ON      evt.category_id     = cat.category_id
JOIN    sys.trace_columns col               
ON      evi.columnid        = col.trace_column_id
LEFT JOIN filter parent_fil                 
ON      col.trace_column_id = parent_fil.columnid
GROUP BY cat.name
        ,evt.name
        ,col.name
        ,parent_fil.columnid
ORDER BY cat.name
        ,evt.name
        ,col.name

image

Find out What filter with the Trace

DECLARE  @trace_id  INT;
set @trace_id =1
 
SELECT   evt.name AS EventName
        ,col.name AS ColumnName 
FROM fn_trace_geteventinfo(@trace_id) inf
JOIN sys.trace_events evt   ON inf.eventid  = evt.trace_event_id
JOIN sys.trace_columns col  ON inf.columnid = col.trace_column_id
ORDER BY eventid
        ,columnid
image

Create the trace

Alter trace permission is required to create the trace.

Here is the sample script to create the trace, add event and add the filter to the trace.

-- Create Trace
DECLARE  @trace_id  INT
        ,@rc        INT = 0
        ,@maxFileSize bigint;
 
        SET @maxFileSize = 10
        
EXEC     @rc = SP_TRACE_CREATE  @trace_id  OUTPUT
                                ,0
                                ,N'C:\tmp\TraceTest1.xml'
                                ,@maxFileSize
                                ,NULL;
                                
SELECT   @trace_id as TraceID
        ,@rc as ReturnCode;
        
select   id 
        ,[path]
        ,max_size
        ,max_files
        ,start_time
        ,last_event_time
FROM    sys.traces;
 
 
-- Create Event
declare @on bit;
set @on = 1
exec sp_trace_setevent @trace_id,20,NULL,@on;
 
--- Create filter 
declare @value BIGINT;
set     @trace_id = 3
 
SET @value = 100;
exec sp_trace_setfilter  @trace_id               = @trace_id
                        ,@columnid              = 13        -- Duration
                        ,@logical_operator      = 0         -- AND
                        ,@comparison_operator   = 2         -- Greater Than
                        ,@value                 = @value;
SET @value = 1000;
exec sp_trace_setfilter  @trace_id             = @trace_id
                        ,@columnid              = 13        -- Duration
                        ,@logical_operator      = 0         -- AND
                        ,@comparison_operator   = 3         -- Less Than
                        ,@value                 = @value;
                
 
 

Use Profiler to create the Server side trace template

As you can see, use the  sp_trace_setevent and sp_trace_setfilter to create the trace is tedious work. You need to know exactly what the event id to pass. We can use Profiler to simplify this work.

Start the new trace in the profiler

image

Configure the trace name, enable the stop time.

image

Click Run the start the Trace and click the STOP to stop the trace right away.

image

File –> Export –> Script Trace definition –> For SQL Server 2005 - 2008R2

image

Open the output SQL. It looks like below. We can then update it.

Option 2 is for TRACE_FILE_ROLLOVER when the trace file reach the maxFilesize.

DateTime is when the Trace would stop. ( How long the trace would run)

Then we need to decide where is the trace file output location.

image

We can then execute the script to create the trace. The script would also output the trace ID.

Start/stop/remove the trace

Sp_trace_setstatus is to start/stop/remove the trace. The option id 0 is remove. 1 is start. 2 is stop.
sp_trace_setstatus @trace_id,@option;
 

Here is the sample script to start/stop/remove the trace.

-- start up trace
DECLARE  @trace_id  INT;
set @trace_id =3
sp_trace_setstatus @trace_id,1;
 
select   id 
        ,[path]
        ,max_size
        ,max_files
        ,start_time
        ,last_event_time
FROM    sys.traces;
-- stop trace
sp_trace_setstatus @trace_id,0;
 
select   id 
        ,[path]
        ,max_size
        ,max_files
        ,start_time
        ,last_event_time
FROM    sys.traces;
-- remove trace
sp_trace_setstatus @trace_id,2;
 
select   id 
        ,[path]
        ,max_size
        ,max_files
        ,start_time
        ,last_event_time
FROM    sys.traces;

The default trace can not be stop by using sp_trace_setstatus

image

sp_configure "show advanced options",1;
reconfigure;
sp_configure "default trace enabled",0;

Even we use sp_trace_setstatus with status id 0 to remove the trace. The trace file still on the disk. It has to be removed manually. If we try to create another trace on the same location, we would get the Msg 19067 error.

image

Security Issue with Trace

  • The one with the alter trace permission can have control on all the traces on the sql server. even it is not created by itself.
  • The one has the alter trace permission can see all the object definition, parameter pass through the trace.
  • If the trace file is write into the files. The files may contents the sensitive information pass to the trace. the file also needs to be secure. 

Exploring server side trace

Unfortunately, once the trace is created in the SQL Server. It is difficult to script out the original definition. James Rowland-Jones in the Book: “Professional SQL Server 2008: Internals and Performance Tuning” has the handy script “CH10 Trace Hydrate.sql” that can do the job.

 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates