When a database is running slow, the first diagnostic step I do is try to determine if there is anything that is slowing it down by using the SP_WHO or SP_WHO2. These commands will show all the sessions that are currently established in the database which can possibly point out the one that is making everything else run slow.
SP_WHO Definition
Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
SP_WHO vs SP_WHO2
SP_WHO will provide the most important summary: status, user login info, database name
SP_WHO2 is an extended version of SP_WHO, which includes CPU Time, Disk I/O and Program Name which is very important in determining the source of slow down
HOW:
You literally just have to type in SP_WHO and SP_WHO2 in your script area. A link under the reference, located at the bottom of this blog, will show you how to execute, but it's literally just what it is. You want to pay special attention to status, command, CPU time, disk I/O and program name as it will give you a hint of which process is making it slow?
But wait.. What if I tell you that you can also find out the script being executed?
But why.. Because when you know what script is running, then you can investigate why it is running slow. Is someone updating a table? Is someone doing some indexing? Is someone's code not efficient that it takes so much space? Maybe it's you? Maybe it's someone else? I don't know about you, but I am especially more concerned about if I am the reason why the database is running slow, and which of my queries are possible culprits.
To add the script I have added sys tables: sys.sysprocesses and sys.dm_exec_sql_text.
CODE:
/*---------------------------------------------------------------------------------------
Project Name: SSMS - Slow database...
Created By: LearnwithGlezy.com
Purpose: Initial diagnostic step to see why the database is running slow
---------------------------------------------------------------------------------------*/
DROP TABLE IF EXISTS #Whatever
CREATE TABLE #Whatever
(SPID INT,
[STATUS] VARCHAR(255),
[LOGIN] VARCHAR(255),
HOSTNAME VARCHAR(255),
BLKBY VARCHAR(255),
DBNAME VARCHAR(255),
COMMAND VARCHAR(255),
CPUTIME NUMERIC,
DISKIO NUMERIC,
LASTBATCH VARCHAR(255),
PROGRAMNAME VARCHAR(255),
SPID2 INT,
REQUESTID INT)
INSERT INTO #Whatever EXEC sp_who2
SELECT a.SPID
, a.STATUS
, a.LOGIN
, a.DBNAME
, a.COMMAND
, a.CPUTIME
, a.DISKIO
, a.LASTBATCH
, a.PROGRAMNAME
, SCRIPT = TEXT
FROM #Whatever a
JOIN sys.sysprocesses b ON a.SPID = b.spid
CROSS APPLY sys.dm_exec_sql_text(b.sql_handle)
I hope you had the chance to read about sys object catalog views from reference based off on my other blogs. If not, that's okay. The link to study it can be found in the reference section.
Reference:
Script Area:
How to execute:
SYS OBJECT CATALOG VIEWS:
Comments