Finding Long Running Queries (SQL)

Details

Category: Scripts and Code

Published on Monday, 01 December 2014 23:44

Written by Christian Dunn

A common challenge for database administrators is identifying queries from an application or user that are impacting the performance of a SQL server. These are usually long running queries that can either slow down performance of the SQL server or cause the TEMPDB (the temporary database used by SQL for processing) to grow in size dramatically. Either of these is likely to have an impact on the user experience. These scripts will help you identify these queries.

These scripts have been tested on SQL 2008 R2 and SQL 2012.

The first step is to identify what queries are running and for how long they have been running. The following SQL statement allows you to identify those processes and excludes system functions. The SPID for each process is shown to assist in identifying the process so it can be terminated.

-- Current Long Running Database Queries

SELECT 

         [spid] = session_Id

       , ecid

       , [blockedBy] = blocking_session_id 

       , [database] = DB_NAME(sp.dbid)

       , [user] = nt_username

       , [status] = er.status

       , [wait] = wait_type

       , [current stmt] = 

              SUBSTRING (

                     qt.text, 

               er.statement_start_offset/2,

                     (CASE 

                           WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)    

                           ELSE er.statement_end_offset 

                     END - er.statement_start_offset)/2)

       ,[current batch] = qt.text

       , reads

       , logical_reads

       , cpu

       , [time elapsed (min)] = DATEDIFF(mi, start_time,getdate())

       , program = program_name

       , hostname

       --, nt_domain

       , start_time

       , qt.objectid 

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

WHERE session_Id > 50              -- Ignore system spids.

AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.

ORDER BY 1, 2

If these queries have been running for a long time it is likely the TEMPDB database contains a lot of data or running threads related to these processes. This could be consuming disk space rapidly. The following SQL statements allow you to identify the processes that are using TEMPDB, the current configuration of this database and any impact on drive space. This SQL statement comprises 5 queries to achieve this. It calculates (in order) the size of the TEMPDB, freespace inside the files, free space on the drives, the size and growth of the TEMPDB files, what is currently using the TEMPDB (by SPID) and the database snapshots running.

-- Size of TEMPDB

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

-- Free Space in TEMPDB

SELECT SUM(unallocated_extent_page_count) AS [free pages], 

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

-- Drive Space on SQL Server

EXEC master..xp_fixeddrives

-- Size and Growth Type of TEMPDB

SELECT

name AS FileName,

size*1.0/128 AS FileSizeinMB,

CASE max_size

WHEN 0 THEN 'Autogrowth is off.'

WHEN -1 THEN 'Autogrowth is on.'

ELSE 'Log file will grow to a maximum size of 2 TB.'

END AutogrowthStatus,

growth AS 'GrowthValue',

'GrowthIncrement' =

CASE

WHEN growth = 0 THEN 'Size is fixed and will not grow.'

WHEN growth > 0

AND is_percent_growth = 0

THEN 'Growth value is in 8-KB pages.'

ELSE 'Growth value is a percentage.'

END

FROM tempdb.sys.database_files;

GO

-- Current Active SPIDs using TEMPDB

SELECT session_id, 

SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,

SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 

FROM sys.dm_db_task_space_usage WHERE internal_objects_alloc_page_count > 0 OR internal_objects_dealloc_page_count > 0

GROUP BY session_id;

-- Current Running Database Snapshots

SELECT transaction_id

FROM sys.dm_tran_active_snapshot_database_transactions 

ORDER BY elapsed_time_seconds DESC;

With this information you can now retrieve the queries that are causing either a performance impact or are using drive space on the SQL server. If you identify a query that is causing an impact you can investigate that query to identify why it is so large or you could kill a query by running the simple SQL statement of "Kill <SPID>".