How to create a Profiler Trace during Server startup

I think I answered this question more than once on multiple different forums. So, I thought it was time to create a blog post on it.   

One of my previous post has the steps to create a .sql file containing the server side trace definition. Once you have the .sql file generated by the SQL Profiler Trace GUI. Use the script to convert that into the stored procedure with the following changes:   

declare @tracefilename nvarchar(255) 

set @tracefilename = N'D:\StartupTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, NULL

You will have to add the lines highlighted above and the make a change for the trace file name in the line that is not highlighted. This will ensure that every time your SQL instance starts up, you have a unique trace file name. If the filename is not unique, then the second time the service restarts you will get a trace creation error stating that the file already exists. You will see the following error in your SQL Errorlog:   

Launched startup procedure ‘usp_startup_trace’.

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).

Error: 19062, Severity: 16, State: 1.Could not create a trace file. 

After this is done, create the stored procedure to create the trace definition. Let’s say the proc name was “usp_startup_trace”.   

Now, you will need to mark this stored procedure as a startup procedure using the following:

EXEC sp_procoption 'usp_startup_trace','startup',true

 Once that is done, you will need to set the configuration options for the instance to scan for startup procedures. The default is set to 0.   

EXEC sp_configure 'scan for startup procs',1 RECONFIGURE

 When your SQL instance restarts the next time a new file will be created for the profiler trace and sys.traces will show you new trace active on the server.  

Reference: sp_procoption

WMI Tracing equivalent of SQL Server Profiler Traces

A lot of companies find the need to monitor all DDL and DML activity on the server for Compliance reasons. Though SQL Profiler Traces provides this functionality by using Stored Procedures and Functions. These system stored procedures can be used from within your own applications to create traces manually, instead of using SQL Server Profiler. This allows you to write custom applications specific to the needs of your enterprise. However, you might choose not to use this approach due to the overhead of running profiler traces on the server. You can use server side profiler traces which tend to have a lesser performance impact on the server. 

Another method is to use WMI Events to monitor SQL Server Events. Consider the following example, let’s say you want to monitor the SQL Server events for all Update Statistics Events. Then you can use script below to create the WMI Alert and also the job to put the event details into a SQL Server database table. 

<script> 

USE TestDB 

GO 

-- Creating the table to store the DDL Events 

CREATE TABLE [dbo].[UPD_STATS_Events]( 

[AlertTime] [datetime] NULL, 

[SPID] [int] NULL, 

[DBName] [nvarchar](100) NULL, 

[TextData] [nvarchar](max) NULL 

) ON [PRIMARY] 

GO 

-- Adding the job to run when the WMI Alert is raised 

EXEC msdb.dbo.sp_add_job @job_name=N'Capture Update Statistics Events', 

@enabled=1, 

@description=N'Job for responding to DDL events' ; 

GO 

-- Adding the job step 

EXEC msdb.dbo.sp_add_jobstep 

@job_name = N'Capture Update Statistics Events', 

@step_name=N'Insert data into Update Statistics Events table', 

@step_id=1, 

@on_success_action=1, 

@on_fail_action=2, 

@subsystem=N'TSQL', 

@command= N'INSERT INTO UPD_STATS_Events 

(AlertTime, Spid,DBName,TextData) 

VALUES (getdate(), $(ESCAPE_NONE(WMI(SPID))), 

''$(ESCAPE_SQUOTE(WMI(DatabaseName)))'', 

''$(ESCAPE_SQUOTE(WMI(TSQLCommand)))'')', 

@database_name=N'TestDB' ; 

GO 

-- Set the job server for the job to the current instance of SQL Server. 

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Update Stats Events' ; 

GO 

-- For the named instance you need to use \\.\root\Microsoft\SqlServer\ServerEvents\<INSTANCE NAME> 

EXEC msdb.dbo.sp_add_alert @name=N'Respond to Update Statistics Events', 

@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 

@wmi_query=N'SELECT * FROM UPDATE_STATISTICS', 

@job_name='Capture Update Statistics Events' ; 

GO

</script> 

You will have to give the SQL Agent account WMI Token replacement rights. You need to create a registry key in SQLAgent for this.
Add a Reg_DWORD HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.x\SQLServerAgent\AlertReplaceRuntimeTokens
and set it to 1.
and no SQLAgent restarts needed. 

Where x = the number of the SQL instance. This can be found out by looking under the regisry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL 

Whenever an update statistics task is fired against a database, the time when the command was executed, the SPID number, the database name and the T-SQL Command command associated with the event would be put into the table in the TestDB database. 

Service Broker should be enabled for the MSDB and the database in which you are storing the event details. 

Information about the namespaces can be found on MSDN. Also, you could use WMI Code Creator to browse through the namespace and the classes available and their properties. 

Furthermore, you can also use the WMI Event Watcher Task or the WMI Data Reader Task of SQL Server 2005 SSIS to perform the same.

How to find out the CPU usage information for the SQL Server process using Ring Buffers

SQL Server 2005 Ring Buffers stored information related to CPU usage. This information is updated every 1 minute. So at any given point of time you can trace back the CPU usage for 4 hours 15 minutes. A sample output below shows the SQL instance CPU utilization against the other processes (active on the server) CPU utilization. This will definitely help us identify whether the SQL Server process is actually driving CPU. 

Sample Output:

Event Time SQL Process Utilization System Idle Other Process Utilization
2009-11-19 20:00:49.947  0 96 4
2009-11-19 19:59:49.933  0 92 8
2009-11-19 19:58:49.910  0 91 9
2009-11-19 19:57:49.893  0 92 8

For SQL Server 2005: 

declare @ts_now bigint 

select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info 

select record_id, 

dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime, 

SQLProcessUtilization, 

SystemIdle, 

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization 

from ( 

select 

record.value('(./Record/@id)[1]', 'int') as record_id, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization, 

timestamp 

from ( 

select timestamp, convert(xml, record) as record 

from sys.dm_os_ring_buffers 

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 

and record like '%<SystemHealth>%') as x 

) as y 

order by record_id desc

For SQL Server 2008: 

declare @ts_now bigint 

select @ts_now = ms_ticks from 

sys.dm_os_sys_info 

select record_id, dateadd (ms, (y.[timestamp] -@ts_now), GETDATE()) as EventTime,




SQLProcessUtilization, 

SystemIdle, 

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization 

from ( 

select 

record.value('(./Record/@id)[1]', 'int') as record_id, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 

as SystemIdle, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 

'int') as SQLProcessUtilization, 

timestamp 

from ( 

select timestamp, convert(xml, record) as record 

from sys.dm_os_ring_buffers 

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 

and record like '%<SystemHealth>%') as x 

) as y 

order by record_id desc