-- Code and scripts from Chapter 2: Tracing


-- Using a trace ID you can retrieve a variety of metadata 
-- about the trace from the sys.traces catalog view, 
-- such as is done by the following query:

SELECT 
  status,
  path,
  max_size,
  buffer_count,
  buffer_size,
  event_count,
  dropped_event_count
FROM sys.traces
WHERE id = 2;




-- This function returns the numeric combinations of events 
-- and columns selected for the trace in a tabular format. 
-- The following T-SQL returns this data for trace ID 2:

SELECT *
FROM fn_trace_geteventinfo(2);



-- The sys.trace_events and sys.trace_columns contain not 
-- only text describing the events and columns respectively, 
-- but also other information such as data types for the 
-- columns and whether they are filterable. Combining these 
-- views with the above query against the 
-- fn_trace_geteventinfo function, we can get a much easier 
-- to read version of the same output:

SELECT
e.name AS Event_Name,
c.name AS Column_Name
FROM fn_trace_geteventinfo(2) ei
JOIN sys.trace_events e ON ei.eventid = e.trace_event_id
JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id;



-- To get information about which filter values were set for a 
-- trace, the fn_trace_getfilterinfo function can be used. This 
-- function returns the column ID being filtered (which can 
-- be joined to the sys.trace_columns view for more information),
-- the logical operator, comparison operator, and the value of 
-- the filter. Following is an example of its use:

SELECT
  columnid,
  logical_operator,
  comparison_operator,
  value
FROM fn_trace_getfilterinfo(2);



-- The following Transact-SQL code reads the trace file 
-- located at c:\inside_sql.trc:

SELECT *
FROM fn_trace_gettable('c:\inside_sql.trc', 1);



-- It’s probably a good idea to use SELECT INTO, in order to 
-- take advantage of minimal logging: 

SELECT *
INTO inside_sql_trace
FROM fn_trace_gettable('c:\inside_sql.trc', 1);



-- By modifying the previous file-based trace, we can
-- produce a rowset-based trace using the following Transact-SQL:

DECLARE @rc int
DECLARE @TraceID int
EXEC @rc = sp_trace_create
    @TraceID output,
    1,
    NULL,
    NULL,
   NULL
IF (@rc != 0) GOTO finish

-- Set the events
DECLARE @on bit
SET @on = 1
EXEC sp_trace_setevent @TraceID, 12, 15, @on
EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 9, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 6, @on
EXEC sp_trace_setevent @TraceID, 12, 10, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters
DECLARE @bigintfilter bigint
SET @bigintfilter = 10000
EXEC sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
SELECT TraceID=@TraceID
EXEC sp_executesql
    N'exec sp_trace_getdata @P1, 0',
    N'@P1 int',
    @TraceID
finish:
GO



-- To see only the top 10 percent of queries in a trace table, 
-- based on duration, use the following query:

SELECT *
FROM
(
  SELECT
    *,
    NTILE(10) OVER(ORDER BY Duration) Bucket
  FROM TraceTable
) x
WHERE Bucket = 10;



--To help solve this problem, and reduce these queries to a 
-- common form that can be grouped, Itzik Ben-Gan provided 
-- a CLR UDF in Inside SQL Server 2005: T-SQL Querying, 
-- a slightly modified version of which—that also handles 
-- NULLs—follows:

/* 

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true)]
public static SqlString sqlsig(SqlString querystring)
{
    return (SqlString)Regex.Replace(
       querystring.Value,
       @”([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?:(?# expression coming
       )(?:([N])?(')(?:[^']|'')*('))(?# character
       )|(?:0x[\da-fA-F]*)(?# binary
       )|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?# precise number
       )(?:[eE]?[\d]*)))(?# imprecise number
       )|(?:[~]?[-+]?(?:[\d]+))(?# integer
       )|(?:[nN][uU][lL][lL])(?# null
       ))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?# operators
       )))”,
       @”$1$2$3#$4”);
}

*/


-- To use this UDF to help with processing a trace table to 
-- find the top queries, you might start with something along 
-- the lines of the following query, which groups each common 
-- query form and finds average values for Duration, Reads, 
-- Writes, and CPU:

SELECT
  QueryForm,
  AVG(Duration),
  AVG(Reads),
  AVG(Writes),
  AVG(CPU)
FROM
(
  SELECT
    dbo.fn_sqlsig(TextData) AS QueryForm,
    1. * Duration AS Duration,
    1. * Reads AS Reads,
    1. * Writes AS Writes,
    1. * CPU AS CPU
  FROM TraceTable
  WHERE TextData IS NOT NULL
) x
GROUP BY QueryForm;



-- The following query uses this logic to find all user 
-- exceptions and disconnections, related error messages 
-- where appropriate, and the queries that caused the problems:

;WITH Exceptions AS
(
  SELECT
    T0.SPID,
    T0.EventSequence,
    COALESCE(T0.TextData, 'Attention') AS Exception,
    T1.TextData AS MessageText
  FROM TraceTable T0
    LEFT OUTER JOIN TraceTable T1 ON
       T1.EventSequence = T0.EventSequence + 1
       AND T1.EventClass = 162
  WHERE
    T0.EventClass IN (16, 33)
    AND (T0.EventClass = 16 OR T1.EventSequence IS NOT NULL)
)
SELECT *
FROM Exceptions
CROSS APPLY
(
  SELECT TOP(1)
    TextData AS QueryText
  FROM TraceTable Queries
  WHERE
    Queries.SPID = Exceptions.SPID
    AND Queries.EventSequence < Exceptions.EventSequence
    AND Queries.EventClass IN (10, 13)
  ORDER BY EventSequence DESC
) p;



-- To illustrate what is available, we’ll show you how to
-- force a deadlock in the tempdb database using the following 
-- code:

USE tempdb
GO
CREATE TABLE Deadlock_Table
(
  ColumnA int NOT NULL PRIMARY KEY
)
GO
INSERT Deadlock_Table
      SELECT 1 UNION ALL SELECT 2;
GO



-- Once the trace is started, return to the first query 
-- window (spid 52 in our test), and run the following batch:

BEGIN TRANSACTION
UPDATE Deadlock_Table
SET ColumnA = 3
WHERE ColumnA = 1;
GO



-- Next, run the following batch in the second query 
-- window (spid 53 in our test):

BEGIN TRANSACTION
UPDATE Deadlock_Table
SET ColumnA = 4
WHERE ColumnA = 2;
GO

-- Both of these queries should return, since their locks 
-- are compatible; they’re each taking locks on different 
-- rows of the Deadlock_Table table. Back in the first query 
-- window, start the following update, which will begin 
-- waiting on the second window’s session to release its lock:

UPDATE Deadlock_Table
SET ColumnA = 4
WHERE ColumnA = 2;
GO



-- Finally, return to the second window and run the following 
-- update, which will start waiting on the first window’s 
-- session to release its lock. Since both sessions will now 
-- be waiting for each other to release resources, a deadlock 
-- will occur:

UPDATE Deadlock_Table
SET ColumnA = 3
WHERE ColumnA = 1;
GO



-- To make this happen, we’ll have to employ SQL Server 2005’s 
-- module signing feature. The first step is to create a 
-- certificate in the master database:

USE master
GO
CREATE CERTIFICATE ALTER_TRACE_CERT
   ENCRYPTION BY PASSWORD='-UsE_a!sTr0Ng_PwD-or-3~'
      WITH
         SUBJECT='Certificate for ALTER TRACE',
         START_DATE='20000101',
         EXPIRY_DATE='99990101';
GO




-- The following code creates a certificate in tempdb from 
-- the backed-up version, then creates a simple wrapper over 
-- the sp_trace_generateevent stored procedure:

USE tempdb
GO
CREATE CERTIFICATE ALTER_TRACE_CERT
    FROM FILE='C:\ALTER_TRACE.cer'
    WITH PRIVATE KEY
  (
    FILE='C:\ALTER_TRACE.pvk',
    ENCRYPTION BY PASSWORD='-UsE_a!sTr0Ng_PwD-or-3~',
    DECRYPTION BY PASSWORD='-UsE_a!sTr0Ng_PwD-or-3~'
  );
GO

CREATE PROCEDURE ThrowEvent
  @eventid INT,
  @userinfo nvarchar(128),
  @userdata varbinary(8000)
AS
 BEGIN
  EXEC sp_trace_generateevent
    @eventid = @eventid,
    @userinfo = @userinfo,
    @userdata = @userdata
 END
GO




-- To complete the exercise, the stored procedure is signed 
-- with the certificate—which gives the procedure effectively 
-- all of the same permissions as the ALTER_TRACE_LOGIN login, 
-- and then permission is granted for any user to run the 
-- stored procedure:

ADD SIGNATURE TO ThrowEvent
  BY CERTIFICATE ALTER_TRACE_CERT
    WITH PASSWORD='-UsE_a!sTr0Ng_PwD-or-3~'
GO
GRANT EXEC ON ThrowEvent TO [public]
GO



-- To help debug this, you might insert the following code 
-- into your stored procedure, just after the update:

IF @@ROWCOUNT = 0
   EXEC ThrowEvent 82, N'No data inserted into MyTable', 0x0000



-- The following Transact-SQL can be used to start a 
-- blackbox trace:

DECLARE @TraceId INT
EXEC sp_trace_create
   @TraceId OUTPUT,
   @options = 8
EXEC sp_trace_setstatus @TraceId, 1



-- The following Transact-SQL code creates a blackbox trace 
-- with a 25-megabyte maximum size:
DECLARE @TraceId int;
DECLARE @maxfilesize bigint;
SET @maxfilesize = 25;
EXEC sp_trace_create
   @TraceId OUTPUT,
   @options = 8,
   @tracefile = NULL,
   @maxfilesize = @maxfilesize
EXEC sp_trace_setstatus @TraceId, 1



-- To accomplish this, you can set the blackbox trace to start 
-- automatically when SQL Server starts. First, wrap the trace 
-- definition in a stored procedure in the master database:

USE master
GO
CREATE PROCEDURE StartBlackBoxTrace
AS
BEGIN
  DECLARE @TraceId int
  DECLARE @maxfilesize bigint
  SET @maxfilesize = 25
  EXEC sp_trace_create
    @TraceId OUTPUT,
    @options = 8,
    @tracefile = NULL,
    @maxfilesize = @maxfilesize
  EXEC sp_trace_setstatus @TraceId, 1
END
GO



-- Next, set the procedure to start automatically when the 
-- SQL Server service is started:

EXEC sp_procoption 'StartBlackBoxTrace', 'STARTUP', 'ON'