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

Powershell script to calculate folder size

Just published a PS Script to calculate the TOP 5 folders for a UNC share. The need for this script arose when I needed to find out which folders were consuming the largest amount of disk space on a File Server. I know there are graphical utilities like DiskMon, Space Monger etc. but this script provides me the flexibility of automation like identifying the folder owners or deleting based on some criteria etc.

Click here to download the script.

How to create a Profiler Trace during Server startup

I think I answered this question more than once on multiple different forums. So, I thought it was time to create a blog post on it.   

One of my previous post has the steps to create a .sql file containing the server side trace definition. Once you have the .sql file generated by the SQL Profiler Trace GUI. Use the script to convert that into the stored procedure with the following changes:   

declare @tracefilename nvarchar(255) 

set @tracefilename = N'D:\StartupTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, NULL

You will have to add the lines highlighted above and the make a change for the trace file name in the line that is not highlighted. This will ensure that every time your SQL instance starts up, you have a unique trace file name. If the filename is not unique, then the second time the service restarts you will get a trace creation error stating that the file already exists. You will see the following error in your SQL Errorlog:   

Launched startup procedure ‘usp_startup_trace’.

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(The file exists.).

Error: 19062, Severity: 16, State: 1.Could not create a trace file. 

After this is done, create the stored procedure to create the trace definition. Let’s say the proc name was “usp_startup_trace”.   

Now, you will need to mark this stored procedure as a startup procedure using the following:

EXEC sp_procoption 'usp_startup_trace','startup',true

 Once that is done, you will need to set the configuration options for the instance to scan for startup procedures. The default is set to 0.   

EXEC sp_configure 'scan for startup procs',1 RECONFIGURE

 When your SQL instance restarts the next time a new file will be created for the profiler trace and sys.traces will show you new trace active on the server.  

Reference: sp_procoption

How to find out the CPU usage information for the SQL Server process using Ring Buffers

SQL Server 2005 Ring Buffers stored information related to CPU usage. This information is updated every 1 minute. So at any given point of time you can trace back the CPU usage for 4 hours 15 minutes. A sample output below shows the SQL instance CPU utilization against the other processes (active on the server) CPU utilization. This will definitely help us identify whether the SQL Server process is actually driving CPU. 

Sample Output:

Event Time SQL Process Utilization System Idle Other Process Utilization
2009-11-19 20:00:49.947  0 96 4
2009-11-19 19:59:49.933  0 92 8
2009-11-19 19:58:49.910  0 91 9
2009-11-19 19:57:49.893  0 92 8

For SQL Server 2005: 

declare @ts_now bigint 

select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info 

select record_id, 

dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime, 

SQLProcessUtilization, 

SystemIdle, 

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization 

from ( 

select 

record.value('(./Record/@id)[1]', 'int') as record_id, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization, 

timestamp 

from ( 

select timestamp, convert(xml, record) as record 

from sys.dm_os_ring_buffers 

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 

and record like '%<SystemHealth>%') as x 

) as y 

order by record_id desc

For SQL Server 2008: 

declare @ts_now bigint 

select @ts_now = ms_ticks from 

sys.dm_os_sys_info 

select record_id, dateadd (ms, (y.[timestamp] -@ts_now), GETDATE()) as EventTime,




SQLProcessUtilization, 

SystemIdle, 

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization 

from ( 

select 

record.value('(./Record/@id)[1]', 'int') as record_id, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 

as SystemIdle, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 

'int') as SQLProcessUtilization, 

timestamp 

from ( 

select timestamp, convert(xml, record) as record 

from sys.dm_os_ring_buffers 

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 

and record like '%<SystemHealth>%') as x 

) as y 

order by record_id desc