Monitoring MSSQL servers.

I recently posed a question on the here about monitoring MSSQL servers in a way similar to MySQL’s processlist commands. I needed a way to find out what users, databases and queries were being run to prevent server slow-down. It turns out that there isn’t an easy way of doing it, and instead requires that you query the database itself to figure out who/what/when/why.

Here are the queries that I’ve composed that use a temporary table from the output of sp_who2. They’re very simple, but will give you a head-start in developing your own queries using sp_who2.

CREATE TABLE #sp_who2
    (SPID INT, 
    STATUS VARCHAR(1000) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(1000) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(1000) NULL, 
    ProgramName VARCHAR(1000) NULL, 
    SPID2 INT,
    RequestID INT)  
GO
 
INSERT INTO #sp_who2
EXEC sp_who2
GO
 
SELECT Login, DBName, COUNT(Login) AS Connections
FROM #sp_who2
GROUP BY Login, DBName
ORDER BY Connections DESC
GO
 
DROP TABLE #sp_who2
GO

This doesn’t show the type of query or the query itself. In order to get this information you’ll have to get the SPID of a process and use the following code. Using this, you can obtain the entire query.

DBCC INPUTBUFFER(spid)