Tools

version: April 2007

Please note that the provided zip files work only for the versions in the file title Do *not* use it in other versions. The code is implemented as an extended stored proc: which is a dll running in sqlserver’s address space: this means it can potentially damage your running SQLServer. It uses parts of Microsoft Research's Detours package that modifies in-memory running code (not the actual on disk .exe itself!) Use at own risk. I nor my employer can be held responsible for any damage caused by any of this software. Test it before using in a production environment, if at all.

November 13, ^th 2003 Documentation update: This Word document is updated and now contains detailed sqlstats trace descriptions. The read wait documentation is also updated (all for SQL Server 2000 only)

Current versions:

Sqlstats for SQL Server 2005 SP2 build 3042(updated April 19, 2007)

Sqlstats for SQL Server 2000 SP4 version 2039 (updated April 3, 2007 for PQ measurement bug)

Old SQL Server 2000 versions:

Sqlstats for SQL Server 2000 version 760

Sqlstats for SQL Server 2000 version 818

Sqlstats for SQL Server 2000 version 878

This is the documentation for all versions. It explain how install and use the tools and it explains the extended trace features. Check out this Word document to see what the readwait and readspin tools get from sqltrace.

Some screenshots:  xp_waitstats output, read wait output of a user performing parallel query, read spin output showing spinlock timing, latch and spin ‘where’ stats and latch acquire counts and times, and the extended sqlstats trace output.

The extra tools below all access SQL Server memory directly, they don't need a database client connection. All the extra tools can be downloaded in one zip file, or you can download each executable individually:

All tools are for build 878! (760 or 818 available on request)

waitmon        monitors by polling every 'x' milliseconds the PSS area of a sqlserver process (you can specify the spid), which holds (among many other interesting things) the waittype and time info. After 'y' loops, or ctrl-c (or immediate if sleep time >= 1000 ms) it will show you the waits it detected, and the corresponding wait times. Start the 'exe' without arguments to see the command format. (screenshot)

psswalk        'Walk' all PSS (Process Support Structures) and dumps SDES and DES info belonging to the spid's. This will show data on what a spid is doing against which objects, it includes row counts, lock counts etc. It's still highly experimental, and I need to find out more about certain memory locations, but it can show interesting information. (screenshot)

pssmon        Same as above but it looks monitors only one spid it, specify a cycle time in ms. (run without parameter to get exact syntax)

deswalk        Walks the descriptors (like dbcc des), show open objects and database(file) related statistics (as in ::fn_virtualfilestats)

bpool            Dumps the buffer header structures.

umsmon        Polls the UMS queues and dumps some information from it, can be nice to see queues building up when the system is too busy.

waitstats        Shows the exact same information as sqlperf(waitstats) (but without the database connection)

setspincount  Until SQL Server 7 it was possible to set the spin count manually, this little utility will let you do it in SQL Server 2000, note that it         will patch the running sqlservr.exe, and will only last for the life time of the running instance.

waitprint Sets an undocumented UMS wait event trace, run ‘waitprint 10’, and a trace will be printed to the console of all waits taking longer than 10ms

settimeslice Sets UMS timeslice value.This determines the length of time how long a UMS thread can be on an OS Thread until scheduled out. Only used for experiments.