SQL Server Bulk Trace .

One of my many roles at Sagittarius is to manage our clients databases on SQL Server 2012. Keeping everything running in tip top performance is always a battle. One of the many tools I use for analysing the databases performance at a server level is a custom tracing database set up.

You can use the SQL Profiler tool for watching the data on the server in real time. But sometimes it’s good to collect a ton of data over a period. It’s nice to do this by recording that data to disk without using SQL Profilers UI hogging system resources.

So what I did is to create a database called TraceImporter. We would then record data to disk for a time period and then import this data into the TraceImporter database for mining. To build this I create a table called TraceResults.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TraceResults](

      [TextData] [varchar](max) NULL,

      [Duration] [int] NULL,

      [Reads] [int] NULL,

      [Writes] [int] NULL,

      [CPU] [int] NULL,

      [StartTime] [datetime] NULL,

      [ProcedureName] [varchar](max) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

This is going to be used as a store for the data to be added to so we can analyse it. Then a stored procedure called ImportTraceResults whose job it is to read a trace file and put the data into the above TraceResults table.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Richard Brisley

-- Create date: 20/11/2014

-- Description:   Imports a sql server trace file into a table for analysis

-- =============================================

CREATE PROCEDURE [dbo].[ImportTraceResults]

      @TraceFile VARCHAR(8000)

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      -- Import the trace results to the database

    INSERT INTO TraceResults

      (TextData, Duration, Reads, Writes, CPU, StartTime)

      SELECT TextData, Duration/1000, Reads, Writes, CPU, StartTime

      FROM fn_trace_gettable(@TraceFile,1)

      -- Populate the procedure name column where applicable

      UPDATE TraceResults

      SET ProcedureName =

      LEFT

      (

        RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))),

        CHARINDEX(' ', RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))) + ' ')

      )

      WHERE TextData like '%exec%' AND ProcedureName IS NULL

END

When we are running traces its good to have a quick and simple way to return a list of them. So this is a simple wrapper for fn_trace_getinfo called RunningTraces.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Richard Brisley

-- Create date: 20/11/2014

-- Description:   Returns a list of running traces

-- =============================================

CREATE PROCEDURE [dbo].[RunningTraces]

AS

BEGIN

      SET NOCOUNT ON;

      SELECT * FROM :: fn_trace_getinfo(default)

END

This is the big piece of the puzzle, it uses a stored procedure to start a new trace. You simply pass it the Output file name, so the default will create C:\Databases\Data\Traces\Trace20141120-1847.trc using the current date and time. The second parameter is the amount of time you want the trace to run. If no parameter is provided the default is 30 minutes. On timeout this procedure will automatically call ImportTraceResults to import the data into the table for analysis.

-- =============================================

-- Author:        Richard Brisley

-- Create date: 20/11/2014

-- Description:   Record a trace file to disk for analysing later on

-- =============================================

CREATE PROCEDURE [dbo].[StartTrace]

      @OutputFileName NVARCHAR(256) = 'C:\Databases\Data\Traces\Trace',

      @EndTime DATETIME = NULL

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      IF @EndTime IS NULL BEGIN

            SET @EndTime = DATEADD(mi,30,getdate())

      END

      PRINT 'Ending at'

      PRINT @EndTime

      DECLARE @rc INT

      DECLARE @TraceID INT

      DECLARE @MaxFileSize BIGINT

      SET @MaxFileSize = 500

      SET @OutputFileName = @OutputFileName +

            CONVERT(VARCHAR(20), GETDATE(),112) +

            REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

      exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime

      -- Set the events and columns

      declare @on bit

      set @on = 1

      exec sp_trace_setevent @TraceID, 10, 16, @on

      exec sp_trace_setevent @TraceID, 10, 1,  @on

      exec sp_trace_setevent @TraceID, 10, 17, @on

      exec sp_trace_setevent @TraceID, 10, 18, @on

      exec sp_trace_setevent @TraceID, 10, 12, @on

      exec sp_trace_setevent @TraceID, 10, 13, @on

      exec sp_trace_setevent @TraceID, 10, 14, @on

      exec sp_trace_setevent @TraceID, 12, 16, @on

      exec sp_trace_setevent @TraceID, 12, 1,  @on

      exec sp_trace_setevent @TraceID, 12, 17, @on

      exec sp_trace_setevent @TraceID, 12, 14, @on

      exec sp_trace_setevent @TraceID, 12, 18, @on

      exec sp_trace_setevent @TraceID, 12, 12, @on

      exec sp_trace_setevent @TraceID, 12, 13, @on

      -- Set the trace status to start

      exec sp_trace_setstatus @TraceID, 1

      PRINT 'TraceID'

      PRINT @TraceID

      SET @OutputFileName = @OutputFileName + '.trc'

      PRINT 'Trace File'

      PRINT @OutputFileName

      DECLARE @Count INT

      SELECT @Count = COUNT(*) FROM :: fn_trace_getinfo(default) WHERE TraceID = @TraceID

      WHILE @Count > 0

      BEGIN

            WaitFor Delay '00:00:10'

            SELECT @Count = COUNT(*) FROM :: fn_trace_getinfo(default) WHERE TraceID = @TraceID

      END

      PRINT 'Importing Results'

      EXEC dbo.ImportTraceResults @TraceFile = @OutputFileName

END

Here at Sagittarius everything is run through stored procedures. So these two Views let us separate out the stored procedures from the ad hoc sql. Which makes it easier to spot the major speed and resource offenders.

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATEVIEW[dbo].[v_NonStoredProcedures]

AS

SELECTTextData,SUM(DURATION)ASTimeImpact,SUM(reads)ASIOImpact,SUM(CPU)ASCpuImpact,COUNT(*)ASExecutionCount

FROMTraceResults

WHEREProcedureNameISNULL