Run SQL Trace in background

Rather useful this if you need to monitor a trace for a period of time and can not leave the session logged in. Found how to do it here.

First use Profiler to define the events, columns, and filters needed. Some Events are : SQL:BatchCompleted and RPC:Completed, SP:StmtCompleted. Important columns are : Duration, CPU, Reads and Writes. Some advanced events are SP:Recompile and Scan:Started to check for table and index scans

Click the Run button. Immediately stop the trace

Click the File menu, expand the Export option, and then expand the Script Trace Definition option. Choose For SQL Server 2005 (or SQL 2000 if creating script for older SQL Server) and select a filename to save the script.

Once the script has been saved, open it for editing in SQL Server Management Studio.

The following line of the script must be edited, and a valid path must be specified, including a filename:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
@maxfilesize, NULL. The @maxfilesize is 5MB by default

Run the script. The generated script will also select back a @traceID

Once you are done use the @traceID to stop and close the trace:

EXEC sp_trace_setstatus @traceid=99, @status=0
EXEC sp_trace_setstatus @traceid=99, @status=2

The fn_trace_gettable function can be used to read the data from the trace file :

SELECT * FROM ::fn_trace_gettable('C:\Traces\myTrace.trc', 999) where 999 is the number of rollover trace files to read

Easy !

Benchmarking SQL Server

A very useful blog entry on Benchmarking SQL Server.

The article looks at the PAL tool found here

The process is straight forward. Create a performance log for a period of time on the SQL Server you need to benchmark. Typical metrics to record are :

Physical Disk
Logical Disk
Process (I will sometimes tweak this down to just the SQL Server process)
Memory
Network Interface (sometimes I will tweak down to just the counters needed and just the NICs involved)
Paging File
Processor
System
SQL Server: Access Methods
SQL Server: SQL Statistics
SQL Server: Buffer Manager
SQL Server: General Statistics
SQL Server: Latches (Often I just look at the two required counters rather than the object)
SQL Server: Locks
SQL Server: Memory Manager

Save this for a reasonable slice of time and then run it through PAL. This then generates a nice report, highlighting any issues. See the above blog link for a more indepth description.

Find sql usage in a database

The following searches all objects in a database for a particular piece of text:

DECLARE @searchString VARCHAR(128)

SET @searchString = 'xp_smtp'

SELECT DISTINCT so.name, so.type, so2.name as source
FROM sysobjects so
INNER JOIN syscomments sc ON so.id = sc.id
LEFT OUTER JOIN sysobjects so2 ON so2.ID = so.parent_obj
WHERE text LIKE '%' + @searchString + '%'
ORDER BY so.name

Collecting Query Stats

Interesting article here which provides a nice SQL to investigate stats of all statements executed on a server.

Get PID for a suspended or sleeping SQL Session

Quick Tip:

Profiler only works for sessions that are active, sp_who/wp_who2 do not give enough details, so to get any more information a quick check in sys.dm_exec_sessions will provide all the information needed.