Sleeping session ID: Where art thou?


This has been an age old confusion ever since the Dynamic Management Views changed the way SQL Performance monitoring was done.

A SPID/Session ID will show up as Sleeping/Awaiting Command when it has no active work request that it needs to execute on the Database Engine. Consider the following scenario:

1. You connect to a SQL instance using Management Studio

2. You execute: SELECT @@SPID

3. You then open another SSMS query window and try and lookup the Session ID returned by the output of Step #2 using the DMV: sys.dm_exec_requests.

If you didn’t know of this behavior, then you will be in for a surprise! This is because the Session ID associated with the query in Step #2 has no active work to perform which is why the DMV didn’t report a row for that particular session. However, when you look up this session id using sys.dm_exec_connections or sys.sysprocesses. The reason sys.sysprocesses reports this SPID is because it doesn’t differentiate between a session with/without an active work request. It will report all SPIDs currently connected to the database engine.

The above behavior is expected and by-design.

Bob Dorr has mentioned about this in his post on the CSS SQL Escalation blog and also talks about how this can affect concurrency if such a session has open transactions:

How It Works: What is a Sleeping / Awaiting Command Session

Advertisements

Initial Data Collection Script


Where would you be able to use it?

The cases where you can use this script effectively are:

1. Performance issues where a quick initial look at the general state of the server is very valuable.

2. Situations where issue is actually happening right now. However, that said, it is always good to collect post mortem data as well for further diagnostics. Performance Dashboard is a good option here but if you want a snapshot of the server and store it somewhere, then this script can help you.

3. Situations where the problem is happening and you need to quickly verify that you are not hitting some known issues.

How is this different from the other scripts that are already existing?

There are scripts already out there which do the same thing. The advantage here is the fact that the output is in HTML format with bookmarks which makes it easy to determine what information is available. Also, it is easy to get to the sections that are of interest in the current scenario.

Usage

  1. Download the script using the link given at the bottom of the page and save it to a file named INITIAL_DIAG.SQL. Open the script file in a SSMS Query Window.
  2. Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
  3. Execute the script and specify INITIAL_DIAG.html as the output file name so that we can get the output in the require HTML format.

Sample Output

********************************************************************************************
INITIAL MICROSOFT PSS DATA COLLECTION
********************************************************************************************

INDEX
1. General Server Configuration
a. Server Info
b. Non-default sp_configure settings
c. Server Settings
d. Active Trace Flags
e. Profiler Trace Information
2. Memory Diagnostics
a.DBCC MEMORYSTATUS output
b.Memory Clerks rollup output
3.Procedure Cache Statistics
4.Database Diagnostics
a.Replicated Databases
b.Database File Usage Stats
c.Index Statistics
d.sp_helpdb output
e.DBCC SQLPERF (LOGSPACE)
f.Hypothetical Indexes
5.Missing Indexes report
6.Blocking Information
7.Batch/Query Statistics

########################################

****************** SERVER INFO *********
########################################

—————————————————————–
Microsoft SQL Server 2005 – 9.00.3257.00 (X64)
Jun 12 2008 16:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

—————————————————————
SQL SERVER NAME: SERVER1

CLUSTERED: No

MACHINE NAME: SERVER1
NET BIOS NAME: SERVER1

SQL PID: 5076
FTS Installed: YES
Security Mode: MIXED
Current SPID: 51

Click here to download the script.

Modification: June 20, 2012: Added additional code to account for the following:

  • Added code to identify read-write workload ratios
  • Added code to find out non-Microsoft DLLs loaded in SQL address space using sys.dm_os_loaded_modules
  • Added code to get output for SQL Server 2012 instances