System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command - Microsoft SQL Server 2012 Performance Tuning - page 64

The following is the prerequisite for this recipe:

F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.

How to do it...

Follow the steps provided here to perform this recipe:

1. Open SQL Server Management Studio and connect to an instance of SQL Server.

2. In a new query window, type and execute the query, as shown in following script, to

monitor the SQL Server processes and sessions:
USE tempdb
GO

--Check if the table exists. If it does,
--drop it first.
IF OBJECT_ID('tempdb.dbo.#tbl_SPWho') IS NOT NULL
BEGIN
DROP TABLE tempdb.dbo.#tbl_SPWho
END

--Creating table to store the output
--sp_who stored procedures.
CREATE TABLE dbo.#tbl_SPWho
(
spid SMALLINT
,ecid SMALLINT
,status NVARCHAR(30)
,loginame NVARCHAR(128)
,hostName NVARCHAR(128)
,blk CHAR(5)
,dbname NVARCHAR(128)
,cmd NVARCHAR(16)
,request_id INT
)

--Insert the result of sp_who stored procedure
--into table.
INSERT INTO dbo.#tbl_SPWho
EXECUTE sp_who
GO

--Check if the table exists. If it does,
--drop it first.