Welcome to DBA for Dummies!

I wanted to create this blog to share simple scripts and strategies I use daily as a SQL Server database administrator.  My hope is that it will be beneficial to people just starting out with SQL Server as I was years ago.  In our inaugural post I will be going over the first thing I usually do when getting onto a MSSQL box that is displaying any problems.

It is the first step in my four step troubleshooting methodology:

1. What is currently running?
2. Check Job Activity Monitor and correlate to above
3. Blocking, Deadlocks? To kill or not to kill
4. General DB server health

So onto #1, what is running?

Most people usually start with the well known command:

exec sp_who2

While this gives us some handy information on SPIDs (Server Process ID) unfortunately sometimes it is difficult to parse through and identify any problematic ones as it will list all of them.  That can easily be over 200 SPIDs to go through though you should generally ignore the first 50 as those are SQL Server internal running processes.

The below script is what I usually run over and above sp_who2 as it will show you active SPIDs.  What I like about this script is the sql_text field showing you the sql command running and the blocking_session_id column will show you if you have any blocking in your database.  Even though it is focused on identifying blocking in your databases it is a great script to see any active sessions.  We will cover if you need to kill blocking or deadlocked sessions in our third post.
Also don't be intimidated by any scripts you see here if you are just new to T-SQL code, all you need to do is copy-paste and hit F5.

Here is the code:

--BLOCKING SCRIPT
SELECT
    x.session_id,
    x.host_name,
    x.login_name,
    x.start_time,
    x.totalReads,
    x.totalWrites,
    x.totalCPU,
    x.writes_in_tempdb,
    (
        SELECT 
            text AS [text()]
        FROM sys.dm_exec_sql_text(x.sql_handle)
        FOR XML PATH(''), TYPE
    ) AS sql_text,
    COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
    (
        SELECT 
            p.text
        FROM 
        (
            SELECT 
                MIN(sql_handle) AS sql_handle
            FROM sys.dm_exec_requests r2
            WHERE 
                r2.session_id = x.blocking_session_id
        ) AS r_blocking
        CROSS APPLY
        (
            SELECT 
                text AS [text()]
            FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
            FOR XML PATH(''), TYPE
        ) p (text)
    ) AS blocking_text
FROM
(
    SELECT
        r.session_id,
        s.host_name,
        s.login_name,
        r.start_time,
        r.sql_handle,
        r.blocking_session_id,
        SUM(r.reads) AS totalReads,
        SUM(r.writes) AS totalWrites,
        SUM(r.cpu_time) AS totalCPU,
        SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
    FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
    JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
    WHERE r.status IN ('running', 'runnable', 'suspended')
    GROUP BY
        r.session_id,
        s.host_name,
        s.login_name,
        r.start_time,
        r.sql_handle,
        r.blocking_session_id
) x