Monday, November 8, 2004
This script will capture the output from sp_who2 to a table. This allows you to,
a) perform trend analysis on server usage
b) easily track down heavy database users and processes for troubleshooting.
c) the script captures all output columns, but with a few simple modifications, you can specify which columns you view. eg: just the spid, dbname, DiskIO and CPUTime.
d) If you want to display activity in a web page, running the output to a table removes the overhead of executing sp_who on the ASP page. The sp_who2 procedure creates it's own temp table and does a lot of reformatting on the results. Reading from your own table is a very simple select statement.
NOTE: Before running this script ensure you have
1) Add your database and server name where indicated by << >>.
2) Make sure you have the server listed as a linked server not a remote server. By default each server should be its own linked server. This is important as the OPENQUERY statement only works for linked servers.
This script has three distinct parts,
Part 1 is a table to hold the output from sp_who2. The table is just a mirror of the sp_who2 output with an identity field added for the PK.
Part 2 is the stored procedure that updates the table. The stored procedure uses the OPENQUERY statement. This means that you don't need to worry about creating and dropping temp tables. It's also an easy way to query select columns in the stored procedure output. The use of 'SET FMTONLY OFF' was suggested by one of the guru's on www.sqlservercentral.com, can't recall who now. Also, before you get carried away, you can't use variables or parameters.
Part 3 is a scheduled job to run the procedure. Currently I have the job running every 7 mins from 2 mins past the hour. Running at this frequency means that the job will run at different times throughout the day. You can change the job to run more frequently, but be aware of the extra overhead on the server and adjust if necessary.
User Rating of Resource
8(58 votes with a rating total of 508)
| Number of Downloads: