SSMS Tool Pack

A brilliant addon for SSMS 2005/2008, and it's free ! Features include:

•SQL Snippets
•Window Connection Coloring
•Query Execution History and Current Window History
•Format SQL
•Search Table, View or Database Data
•Run one script on multiple databases
•Copy execution plan bitmaps to clipboard or file
•Search Results in Grid Mode or Execution Plans
•Generate Insert statements from resultsets, tables or database
•Regions and Debug sections
•Running custom scripts from Object Explorer
•CRUD stored procedure generation
•New query template
•General options

Install now, if only for the colour coded connection windows !!

SQL Server Memory Settings 64bit

Rather interesting blog posting here talking about SQL Server 2005/2008 64bit and memory settings. The recommendation is to always set these as the following table outlines:

Physical RAM	MaxServerMem Setting
2GB	1500
4GB	3200
6GB	4800
8GB	6400
12GB	10000
16GB	13500
24GB	21500
32GB	29000
48GB	44000
64GB	60000
72GB	68000
96GB	92000
128GB	124000

What makes this interesting is the comments posted, where one poster mentions that they have seen instability when the memory is not set.

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.