Get SQL Server Trace automatically started at service startup

Although SQL Trace and SQL Server Profiler have been marked as deprecated and Microsoft recommends to use Extended Events instead, many of us still use SQL Profiler. You may run the Profiler for small traces or long traces but but if you need to make sure that it runs continuously even at the server restart, you need to consider following points;

• SQL Server Profiler is not recommended for long-term monitoring and large traces.
• Profiler cannot be automatically started when the server is restarted.

If you still need to implement it, you can consider;

• Use SQL Trace instead.
• Use sp_procoption to start the trace at the SQL Server Service start.

This makes sure that the trace configured is continuously running even when the server is restarted and the output is saved and queried anytime.

In order to implement this, you need to follow the steps below;

1. Start with SQL Server Profile and select all events you need with columns and filters for trace.
2. Get the definition of the created using the Profiler and use it for creating the SQL Trace.
3. Include the code of SQL Trace with a stored procedure and add the stored procedure as a startup procedure.

Here is an sample implementation with details.

First let's make a Trace using the Profiler. The following Trace is created with TSQL_Duration Template and All Columns is selected for both Events.

Let's click on Run and then immediately stop. In order to get the code generated for SQL Trace, click File -> Export -> Script Trace Definition -> For SQL Server 2005 - 2016... menu item.

Save it as Trace01.sql. Then open the saved script in Query Window for modification. This code has a place to modify which sets the trace output file path. Since we need to make sure that this code can be executed at each server restart, a unique output file name is required. Therefore, let's add a variable called @FilePath and set with sp_trace_create statement. This is how the first part of the code is changed now;

/****************************************************/
/* Created by: SQL Server 2016 Profiler          */
/* Date: 12/21/2016  10:28:14 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

DECLARE @FilePath nvarchar(200);
SET @FilePath = N'E:\TraceOutput\SQLDurationTrace_' 
 + CONVERT(nvarchar(20), GetDate(), 112) + N'_' + REPLACE(CONVERT(nvarchar(20), GetDate(), 108), ':', N'')

exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL 
if (@rc != 0) goto error

Note that, this creates a file named as SQLDurationTrace_YYYYMMDD_HHMMSS.

It is better to record the TraceID generated from this because it is required to stop and close the trace if required. Since this is going start automatically, the generated TraceID cannot be taken out as it is written. Therefore we need to change it as well. Let's create a table for holding generated trace ids. Let's create this table in master database.

CREATE TABLE dbo.TraceID
(
 ServerStartDateTime datetime NOT NULL 
  PRIMARY KEY DEFAULT(GetDate())
 , TraceID int  NULL
);
GO

Scroll down the Trace code generated and comment the select TraceID=@TraceID statement and add the following instead.

-- display trace id for future references
--select TraceID=@TraceID
INSERT INTO dbo.TraceID
 (ServerStartDateTime, TraceID)
VALUES
 (DEFAULT, @TraceID);

goto finish

We have done the necessary changes to the Trace code. Let's place the entire code into a stored procedure and name it as StartTrace. Here is the entire code now;

USE master;
GO

CREATE OR ALTER PROC dbo.StartTrace
AS
BEGIN

 -- Create a Queue
 declare @rc int
 declare @TraceID int
 declare @maxfilesize bigint
 set @maxfilesize = 5 

 DECLARE @FilePath nvarchar(200);
 SET @FilePath = N'E:\TraceOutput\SQLDurationTrace_' 
  + CONVERT(nvarchar(20), GetDate(), 112) + N'_' + REPLACE(CONVERT(nvarchar(20), GetDate(), 108), ':', N'')

 exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL 
 if (@rc != 0) goto error

 -- Client side File and Table cannot be scripted

 -- Set the events
 declare @on bit
 set @on = 1
 exec sp_trace_setevent @TraceID, 10, 1, @on
 exec sp_trace_setevent @TraceID, 10, 9, @on
 exec sp_trace_setevent @TraceID, 10, 2, @on
 exec sp_trace_setevent @TraceID, 10, 66, @on
 exec sp_trace_setevent @TraceID, 10, 10, @on
 exec sp_trace_setevent @TraceID, 10, 3, @on
 exec sp_trace_setevent @TraceID, 10, 4, @on
 exec sp_trace_setevent @TraceID, 10, 6, @on
 exec sp_trace_setevent @TraceID, 10, 7, @on
 exec sp_trace_setevent @TraceID, 10, 8, @on
 exec sp_trace_setevent @TraceID, 10, 11, @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, 10, 15, @on
 exec sp_trace_setevent @TraceID, 10, 16, @on
 exec sp_trace_setevent @TraceID, 10, 17, @on
 exec sp_trace_setevent @TraceID, 10, 18, @on
 exec sp_trace_setevent @TraceID, 10, 25, @on
 exec sp_trace_setevent @TraceID, 10, 26, @on
 exec sp_trace_setevent @TraceID, 10, 31, @on
 exec sp_trace_setevent @TraceID, 10, 34, @on
 exec sp_trace_setevent @TraceID, 10, 35, @on
 exec sp_trace_setevent @TraceID, 10, 41, @on
 exec sp_trace_setevent @TraceID, 10, 48, @on
 exec sp_trace_setevent @TraceID, 10, 49, @on
 exec sp_trace_setevent @TraceID, 10, 50, @on
 exec sp_trace_setevent @TraceID, 10, 51, @on
 exec sp_trace_setevent @TraceID, 10, 60, @on
 exec sp_trace_setevent @TraceID, 10, 64, @on
 exec sp_trace_setevent @TraceID, 12, 1, @on
 exec sp_trace_setevent @TraceID, 12, 9, @on
 exec sp_trace_setevent @TraceID, 12, 3, @on
 exec sp_trace_setevent @TraceID, 12, 11, @on
 exec sp_trace_setevent @TraceID, 12, 4, @on
 exec sp_trace_setevent @TraceID, 12, 6, @on
 exec sp_trace_setevent @TraceID, 12, 7, @on
 exec sp_trace_setevent @TraceID, 12, 8, @on
 exec sp_trace_setevent @TraceID, 12, 10, @on
 exec sp_trace_setevent @TraceID, 12, 12, @on
 exec sp_trace_setevent @TraceID, 12, 13, @on
 exec sp_trace_setevent @TraceID, 12, 14, @on
 exec sp_trace_setevent @TraceID, 12, 15, @on
 exec sp_trace_setevent @TraceID, 12, 16, @on
 exec sp_trace_setevent @TraceID, 12, 17, @on
 exec sp_trace_setevent @TraceID, 12, 18, @on
 exec sp_trace_setevent @TraceID, 12, 26, @on
 exec sp_trace_setevent @TraceID, 12, 31, @on
 exec sp_trace_setevent @TraceID, 12, 35, @on
 exec sp_trace_setevent @TraceID, 12, 41, @on
 exec sp_trace_setevent @TraceID, 12, 48, @on
 exec sp_trace_setevent @TraceID, 12, 49, @on
 exec sp_trace_setevent @TraceID, 12, 50, @on
 exec sp_trace_setevent @TraceID, 12, 51, @on
 exec sp_trace_setevent @TraceID, 12, 60, @on
 exec sp_trace_setevent @TraceID, 12, 64, @on
 exec sp_trace_setevent @TraceID, 12, 66, @on


 -- Set the Filters
 declare @intfilter int
 declare @bigintfilter bigint

 exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks2014'
 -- Set the trace status to start
 exec sp_trace_setstatus @TraceID, 1

 -- display trace id for future references
 --select TraceID=@TraceID
 INSERT INTO dbo.TraceID
  (ServerStartDateTime, TraceID)
 VALUES
  (DEFAULT, @TraceID);

 goto finish

 error: 
 select ErrorCode=@rc

 finish: 
END
GO

Now the last step. We need to add this stored procedure as a startup procedure. Use the following code to add StartTrace as a Startup procedure;

USE master;
GO

EXEC sp_procoption StartTrace, 'STARTUP', 'ON';
GO

Note that, in order get this work, we need to make sure scan for startup procs server setting is enabled. Generally, when the sp_procoption is execute, this is getting enabled but it is always better to get it enabled before using sp_configure. Done. Let's restart the SQL Server Service and check the dbo.TraceID table.

As you see, a record is inserted with an ID. This means that trace has been started. Let's check the folder.

File is created. For testing purposes, let's run some queries in AdventureWorks2014 database and restarted the service.

USE AdventureWorks2014;
GO

SELECT * FROM Production.Product;
SELECT * FROM Sales.Customer;

Once restarted, we should see a new record in the table and new file in the folder.

This means that our Trace is getting automatically started when the service is started and trace continues. You can query the trace, current or older ones using fn_trace_gettable function and if you need, you can stop the current Trace using value 1 and lose the current Trace using value 2 with sp_trace_setstatus stored procedure.

SQL Server CPU usage: Recording and monitoring with many more server statistics

There are many ways of collecting statistics of SQL Server such as CPU usage, IO made, reads and writes. One facility given by SQL Server is, an Extended Stored Procedure, sp_monitor. This procedure can be used for seeing statistics related to resource use as SQL Server keeps these information using system statistical functions. This procedure shows values since SQL Server was restarted and values since last run sp_monitor was run.

For more details: https://msdn.microsoft.com/en-us/library/ms188912.aspx

Here is the result of the procedure;

This has been mainly given to DBAs to have a quick look. Because of that, collecting them regularly for future analysis is bit difficult. Format is not much user-friendly and returns multiple resultsets, hence calling it using TSQL and saving it in a table is not much easy. However, it can be called and save the result using simple c#.net code. Let's make a table for collecting them first.

-- create a database for holding data
CREATE DATABASE ServerMain;
GO

-- create a table for holding data
USE ServerMain;
GO

CREATE TABLE dbo.ServerStatistics
(
 DateRun datetime primary key
 , CPU_Busy int not null
 , IO_Busy int not null
 , Packet_Received int not null
 , Packet_Sent int not null
 , Packet_Errors int not null
 , Total_Reads int not null
 , Total_Writes int not null
 , Total_Errors int not null
 , Connections int
);
GO
-- Procedure for inserting data
CREATE PROCEDURE dbo.AddServerStatistics @DateRun datetime, @CPU_Busy int, @IO_Busy int
     , @Packet_Received int, @Packet_Sent int, @Packet_Errors int, @Total_Reads int
     , @Total_Writes int, @Total_Errors int, @Connections int
AS
BEGIN

 INSERT INTO dbo.ServerStatistics
  (DateRun, CPU_Busy, IO_Busy, Packet_Received, Packet_Sent
  , Packet_Errors, Total_Reads, Total_Writes, Total_Errors, Connections)
 VALUES
  (@DateRun, @CPU_Busy, @IO_Busy, @Packet_Received, @Packet_Sent
  , @Packet_Errors, @Total_Reads, @Total_Writes, @Total_Errors, @Connections)
END
GO

and here is the c#.net code for calling this procedure and saving the result.

DateTime DateRun = DateTime.Now;
            int CPU_Busy = 0;
            int IO_Busy = 0;
            int Packet_Received = 0;
            int Packet_Sent = 0;
            int Packet_Errors = 0;
            int Total_Reads = 0;
            int Total_Writes = 0;
            int Total_Errors = 0;
            int Connections = 0;
            string commandText = "sp_monitor";

            using (SqlConnection connection = new SqlConnection(@"Data Source=(local)\SQL2014;Database=ServerMain;Integrated Security=true;"))
            {
                using (SqlCommand command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    connection.Open();

                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        DateRun = Convert.ToDateTime(reader[1]);

                    }

                    reader.NextResult();
                    while (reader.Read())
                    {
                        CPU_Busy = Convert.ToInt32(reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).Substring(0, reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).IndexOf(")")));
                        IO_Busy = Convert.ToInt32(reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).Substring(0, reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).IndexOf(")")));
                        
                    }

                    reader.NextResult();
                    while (reader.Read())
                    {
                        Packet_Received = Convert.ToInt32(reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).Substring(0, reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).IndexOf(")")));
                        Packet_Sent = Convert.ToInt32(reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).Substring(0, reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).IndexOf(")")));
                        Packet_Errors = Convert.ToInt32(reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).Substring(0, reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).IndexOf(")")));
                        
                    }

                    reader.NextResult();
                    while (reader.Read())
                    {
                        Total_Reads = Convert.ToInt32(reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).Substring(0, reader[0].ToString().Substring(reader[0].ToString().IndexOf("(") + 1).IndexOf(")")));
                        Total_Writes = Convert.ToInt32(reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).Substring(0, reader[1].ToString().Substring(reader[1].ToString().IndexOf("(") + 1).IndexOf(")")));
                        Total_Errors = Convert.ToInt32(reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).Substring(0, reader[2].ToString().Substring(reader[2].ToString().IndexOf("(") + 1).IndexOf(")")));
                        Connections = Convert.ToInt32(reader[3].ToString().Substring(reader[3].ToString().IndexOf("(") + 1).Substring(0, reader[3].ToString().Substring(reader[3].ToString().IndexOf("(") + 1).IndexOf(")")));
                        
                    }

                    connection.Close();
                }

                commandText = "AddServerStatistics";
                using (SqlCommand command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    SqlParameter parameterDateRun = new SqlParameter("DateRun", SqlDbType.DateTime);
                    parameterDateRun.Value = DateRun;
                    command.Parameters.Add(parameterDateRun);

                    SqlParameter parameterCPU_Busy = new SqlParameter("CPU_Busy", SqlDbType.Int);
                    parameterCPU_Busy.Value = CPU_Busy;
                    command.Parameters.Add(parameterCPU_Busy);

                    SqlParameter parameterIO_Busy = new SqlParameter("IO_Busy", SqlDbType.Int);
                    parameterIO_Busy.Value = IO_Busy;
                    command.Parameters.Add(parameterIO_Busy);

                    SqlParameter parameterPacket_Received = new SqlParameter("Packet_Received", SqlDbType.Int);
                    parameterPacket_Received.Value = Packet_Received;
                    command.Parameters.Add(parameterPacket_Received);

                    SqlParameter parameterPacket_Sent = new SqlParameter("Packet_Sent", SqlDbType.Int);
                    parameterPacket_Sent.Value = Packet_Sent;
                    command.Parameters.Add(parameterPacket_Sent);

                    SqlParameter parameterPacket_Errors = new SqlParameter("Packet_Errors", SqlDbType.Int);
                    parameterPacket_Errors.Value = Packet_Errors;
                    command.Parameters.Add(parameterPacket_Errors);

                    SqlParameter parameterTotal_Reads = new SqlParameter("Total_Reads", SqlDbType.Int);
                    parameterTotal_Reads.Value = Total_Reads;
                    command.Parameters.Add(parameterTotal_Reads);

                    SqlParameter parameterTotal_Writes = new SqlParameter("Total_Writes", SqlDbType.Int);
                    parameterTotal_Writes.Value = Total_Writes;
                    command.Parameters.Add(parameterTotal_Writes);

                    SqlParameter parameterTotal_Errors = new SqlParameter("Total_Errors", SqlDbType.Int);
                    parameterTotal_Errors.Value = Total_Errors;
                    command.Parameters.Add(parameterTotal_Errors);

                    SqlParameter parameterConnections = new SqlParameter("Connections", SqlDbType.Int);
                    parameterConnections.Value = Connections;
                    command.Parameters.Add(parameterConnections);

                    connection.Open();

                    command.ExecuteNonQuery();

                    
                    connection.Close();
                }
            }

If you continuously run this code or schedule for every one hour, you have enough of data to see how busy is your SQL Server and whether it needs more resources or not. Here is a simple chart created using SSRS, based on the saved data.