Have your SQL Servers ever gone mental on you all of a sudden, and you wish you could quickly see an overview of what was running at the time?

I just found these useful queries on a Spiceworks forum.

The first one will show you what is currently being executed on your server:

SELECT s.session_id AS SessionID,
 s.login_time AS LoginTime,
 s.[host_name] AS HostName,
 s.[program_name] AS ProgramName,
 s.login_name AS LoginName,
 s.[status] AS SessionStatus,
 st. AS SQLText,
 (s.cpu_time / 1000) AS CPUTimeInSec,
 (s.memory_usage * 8) AS MemoryUsageKB,
 (CAST(s.total_scheduled_time AS FLOAT) / 1000) AS TotalScheduledTimeInSec,
 (CAST(s.total_elapsed_time AS FLOAT) / 1000) AS ElapsedTimeInSec,
 s.reads AS ReadsThisSession,
 s.writes AS WritesThisSession,
 s.logical_reads AS LogicalReads,
 CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'ReadUncommitted'
 WHEN 2 THEN 'ReadCommitted'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END AS TransactionIsolationLevel,
 s.row_count AS RowsReturnedSoFar,
 c.net_transport AS ConnectionProtocol,
 c.num_reads AS PacketReadsThisConnection,
 c.num_writes AS PacketWritesThisConnection,
 c.client_net_address AS RemoteHostIP,
 c.local_net_address AS LocalConnectionIP
 FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c
 ON s.session_id = c.session_id
 CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS st
 WHERE s.is_user_process = 1
 ORDER BY ElapsedTimeInSec DESC

This next one can show blocking processes… you can use the blocking session ID to track down the other processes:

SELECT dm_ws.wait_duration_ms,
 dm_ws.wait_type,
 dm_es.status,
 dm_t.TEXT,
 dm_ws.session_ID,
 dm_es.cpu_time,
 dm_es.memory_usage,
 dm_es.logical_reads,
 dm_es.total_elapsed_time,
 dm_es.program_name,
 DB_NAME(dm_r.database_id) DatabaseName,
 dm_ws.blocking_session_id,
 dm_r.wait_resource,
 dm_es.login_name,
 dm_r.command,
 dm_r.last_wait_type
 FROM sys.dm_os_waiting_tasks dm_ws
 INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
 INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
 CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
 WHERE dm_es.is_user_process = 1

Reference

• http://community.spiceworks.com/topic/254470-sql-database-intermittently-fails-load-from-asp-net-website

Whilst troubleshooting an issue with backups, I wanted to check the owner of all Maintenance Plans.

I found the following query that does just that:

SELECT sj.name AS JobName,
CASE WHEN sj.[Enabled] = 1
THEN 'Yes'
ELSE 'No'
END AS JobIsEnabled,
sl.name AS OwnerName
FROM msdb.dbo.sysjobs AS sj INNER JOIN master.dbo.syslogins AS sl
ON sj.owner_sid = sl.sid
ORDER BY JobName

I was just checking the Service Pack versions on our SQL servers. Here’s the query I used:

SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion

The Community Technology Preview (CTP) of SP2 for SQL Server 2008 is already out, so it shouldn’t be too long for general release, so in the meantime we’re up-to-date using SP1 🙂

SQL Server 2005 is at its latest Service Pack on version 4:

Standard Edition (64-bit) – SP4 – 9.00.5057.00