Category Archives: sql

How to write T-SQL Geography data to a table

Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.

/*Demo of geo data*/
DECLARE @g GEOGRAPHY;

SET @g = GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

/*Geography data is in binary format*/
SELECT @g AS 'GeoBinaryFormat';

/*Convert binary data to a string*/
SELECT @g.ToString() AS 'ConvertingDataToString';


/*Inserting geo data into Table*/
CREATE TABLE #GeoTest ([CoordinateLocation] [geography] NULL);

INSERT INTO #GeoTest (CoordinateLocation)
SELECT GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

SELECT *
FROM #GeoTest;

DROP TABLE #GeoTest;

How to sum time with T-SQL

Time cannot be summed directly in T-SQL. In order to sum two times they first need to be assigned a date. When a time data type is cast as a datetime data type, as it does not have a date element, the value defaults to the date of 1900-01-01.

As T-SQL does have the functionality to sum datetime and as the date element will be the same only the time value will be summed. This functionality allows us to sum time.

Below is example T-SQL:

IF OBJECT_ID('tempdb..#TimeTable', 'U') IS NOT NULL
BEGIN
DROP TABLE #TimeTable
END

CREATE TABLE #TimeTable(
	id INT
	,TimeRecord TIME(0)
	);

INSERT INTO #TimeTable
VALUES (
	1
	,'00:00:10'
	);

INSERT INTO #TimeTable
VALUES (
	1
	,'00:14:00'
	);

INSERT INTO #TimeTable
VALUES (
	2
	,'00:00:10'
	);

INSERT INTO #TimeTable
VALUES (
	2
	,'00:35:10'
	);

SELECT id
,TimeRecord
FROM #TimeTable;

/*demo of time converted to datetime*/
SELECT CAST(TimeRecord AS DATETIME) AS DateTimeRecord
FROM #TimeTable

SELECT id
	,CAST(DATEADD(MILLISECOND, SUM(DATEDIFF(MILLISECOND, 0, CAST(TimeRecord AS DATETIME))), 0) AS TIME(0)) AS SummedTime
FROM #TimeTable
GROUP BY id;

How to get a substring between two characters with T-SQL

This is a very common activity in the data world, i.e. there’s some data in a text string you need and the rest of the data in the string is just in your way. Some use cases might be you have a reference in a filename you need to extract, or you may need a snippet of data to create a composite key, or there’s an order number surrounded by other data that is not relevant to your needs etc.

The following is some simple T-SQL that will extract the data you want from a text string providing the data has specific delimiting characters on each side of it.

/*Delimiter variables, first and second position*/
DECLARE @dfp AS CHAR(1);
DECLARE @dsp AS CHAR(1);
DECLARE @text VARCHAR(MAX);

SET @dfp = ';';
SET @dsp = '@';
SET @text = 'I want you to ;Extract this@ substring for me please.';

SELECT SUBSTRING(@text, (CHARINDEX(@dfp, @text) + 1), (CHARINDEX(@dsp, @text) - 2) - CHARINDEX(@dfp, @text) + Len(@dsp))

An icon depicting a calendar and clock

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.

How to list login details, such as name and role membership, for all logins in a SQL Server Instance

The script below will provide SQL Server Instance login details such as:

  • The login name
  • The role membership the login is part of such as sysadmin etc.,
  • Whether or not the login is enabled
  • Whether or not the login is a SQL Server Login or Windows Login etc.
  • Login created date
  • Login modified date
  • Etc.
SELECT SP.principal_id
	,SP.name
	,SUSER_NAME(role_principal_id) server_role
	,SP.is_disabled
	,SP.type_desc
	,SP.sid
	,SP.type
	,SP.create_date
	,SP.modify_date
	,SP.default_database_name
	,SP.default_language_name
	,SP.credential_id
	,SP.owning_principal_id
	,SP.is_fixed_role
FROM sys.server_principals AS SP
LEFT JOIN sys.server_role_members AS RM ON SP.principal_id = RM.member_principal_id;

 

How to get SQL Server Network Information using SSMS

The following code will work for a remote client request to SQL 2008 and newer.

Note: The local machine address (local_net_address) is that of the SQL Server while client_net_address is the address of the remote computer you have used to make the request. 

SELECT @@SERVERNAME AS ServerName
	,CONNECTIONPROPERTY('net_transport') AS net_transport
	,CONNECTIONPROPERTY('protocol_type') AS protocol_type
	,CONNECTIONPROPERTY('auth_scheme') AS auth_scheme
	,CONNECTIONPROPERTY('local_net_address') AS local_net_address
	,CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port
	,CONNECTIONPROPERTY('client_net_address') AS client_net_address
 

 

How to find Missing Indexes for all databases in a SQL Server instance

This script is for SQL Server 2005 and up. The script will return all the missing indexes for a SQL Server instance, rating their impact and provide the T-SQL to create the missing indexes.

SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) and DMO (Database Management Objects) which this script requires to function.
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.

WARNING!
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.

/*Script to find Missing Indexes for all databases in SQL Server*/
/*
This script is for SQL Server 2005 and up. 
The script will return all the missing indexes for a SQL Server instance, rating their impact 
and provide the T-SQL to create the missing indexes.

SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) 
and DMO (Database Management Objects) which this script requires to function. 
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.

WARNING!
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) 
operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. 
INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. 
The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to 
the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.
*/
SELECT [EstIndexUses]
	,[EstIndexImpact%]
	,[EstAvgQueryCost]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[CreateIndex]
	,[EqualityColumns]
	,[InequalityColumns]
	,[IncludedColumns]
	,[UniqueCompiles]
	,[LastUserSeek]
FROM (
	SELECT migs.user_seeks AS [EstIndexUses]
		,migs.avg_user_impact AS [EstIndexImpact%]
		,migs.avg_total_user_cost AS [EstAvgQueryCost]
		,db_name(mid.database_id) AS [DbName]
		,OBJECT_SCHEMA_NAME(mid.OBJECT_ID, mid.database_id) AS [SchemaName]
		,OBJECT_NAME(mid.OBJECT_ID, mid.database_id) AS [TableName]
		,'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID, mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN '_'
			ELSE ''
			END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN ','
			ELSE ''
			END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [CreateIndex]
		,mid.equality_columns AS EqualityColumns
		,mid.inequality_columns AS InequalityColumns
		,mid.included_columns AS IncludedColumns
		,migs.unique_compiles AS UniqueCompiles
		,migs.last_user_seek AS LastUserSeek
	FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
	INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
	) AS a
WHERE 1 = 1
--AND [EstIndexUses] > 1000
--AND [EstIndexImpact%] > 10
--AND [EstAvgQueryCost] > 1
--AND DbName IN ('DatabaseName')
ORDER BY [EstIndexUses] DESC
	,[EstAvgQueryCost] DESC
	,[EstIndexImpact%] DESC
OPTION (RECOMPILE);

 

A do not use sign signified by a cartoon man showing his hand within a red circle with a red line dividing it in two

How to NOT write a T-SQL query

So the vendors for the call system software ran the code below on the live system on a Friday night. Thanks guys. I’ve changed the name of the tables in the code for security reasons if anyone would like to use this it as an example of how not to write T-SQL code!

The vendors claim the code came from the company that developed the call system and if this is the case I think someone in head office is trying to get fired or get someone fired.

Scroll down for a review of why the code is such a mess.

/*
TableOne a 312 million row table
TableTwo a 55 million row table 
TableThree a 22 million row table
*/

DELETE
FROM TableOne
WHERE SecurityPolicyId = @p_secPolId
	AND RecordingId NOT IN (
		SELECT c.RECORDINGID
		FROM TableThree c
		WHERE (
				EXISTS (
					SELECT *
					FROM TableTwo b0
					WHERE b0.RecordingID = c.RECORDINGID
						AND b0.Workgroup = N'CS EMAIL - [Cancellations]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b1
					WHERE b1.RecordingID = c.RECORDINGID
						AND b1.Workgroup = N'CS EMAIL - [Claims]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b2
					WHERE b2.RecordingID = c.RECORDINGID
						AND b2.Workgroup = N'CS EMAIL - [Contact Request]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b3
					WHERE b3.RecordingID = c.RECORDINGID
						AND b3.Workgroup = N'CS EMAIL - [DD Enquiries]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b4
					WHERE b4.RecordingID = c.RECORDINGID
						AND b4.Workgroup = N'CS EMAIL - [Feedback]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b5
					WHERE b5.RecordingID = c.RECORDINGID
						AND b5.Workgroup = N'CS EMAIL - [MTA]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b6
					WHERE b6.RecordingID = c.RECORDINGID
						AND b6.Workgroup = N'CS EMAIL - [NB Cover Query]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b7
					WHERE b7.RecordingID = c.RECORDINGID
						AND b7.Workgroup = N'CS EMAIL - [O/S Documents]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b8
					WHERE b8.RecordingID = c.RECORDINGID
						AND b8.Workgroup = N'CS EMAIL - [Otherl]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b9
					WHERE b9.RecordingID = c.RECORDINGID
						AND b9.Workgroup = N'CS EMAIL - [SME]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b10
					WHERE b10.RecordingID = c.RECORDINGID
						AND b10.Workgroup = N'CS WebChat'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b11
					WHERE b11.RecordingID = c.RECORDINGID
						AND b11.Workgroup = N'CUSTOMER SERVICE - [Cancellations]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b12
					WHERE b12.RecordingID = c.RECORDINGID
						AND b12.Workgroup = N'CUSTOMER SERVICE - [CBL enquiry]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b13
					WHERE b13.RecordingID = c.RECORDINGID
						AND b13.Workgroup = N'CUSTOMER SERVICE - [Claims]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b14
					WHERE b14.RecordingID = c.RECORDINGID
						AND b14.Workgroup = N'CUSTOMER SERVICE - [Commercial]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b15
					WHERE b15.RecordingID = c.RECORDINGID
						AND b15.Workgroup = N'CUSTOMER SERVICE - [DD Payment]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b16
					WHERE b16.RecordingID = c.RECORDINGID
						AND b16.Workgroup = N'CUSTOMER SERVICE - [Diary Team]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b17
					WHERE b17.RecordingID = c.RECORDINGID
						AND b17.Workgroup = N'CUSTOMER SERVICE - [Doc Request]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b18
					WHERE b18.RecordingID = c.RECORDINGID
						AND b18.Workgroup = N'CUSTOMER SERVICE - [DocChase CL]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b19
					WHERE b19.RecordingID = c.RECORDINGID
						AND b19.Workgroup = N'CUSTOMER SERVICE - [DocChase FN]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b20
					WHERE b20.RecordingID = c.RECORDINGID
						AND b20.Workgroup = N'CUSTOMER SERVICE - [DocChase IN]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b21
					WHERE b21.RecordingID = c.RECORDINGID
						AND b21.Workgroup = N'CUSTOMER SERVICE - [Life]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b22
					WHERE b22.RecordingID = c.RECORDINGID
						AND b22.Workgroup = N'CUSTOMER SERVICE - [MTA]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b23
					WHERE b23.RecordingID = c.RECORDINGID
						AND b23.Workgroup = N'CUSTOMER SERVICE - [NB Cover Query]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b24
					WHERE b24.RecordingID = c.RECORDINGID
						AND b24.Workgroup = N'CUSTOMER SERVICE - [Other Query]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b25
					WHERE b25.RecordingID = c.RECORDINGID
						AND b25.Workgroup = N'Customer Service Admin'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b26
					WHERE b26.RecordingID = c.RECORDINGID
						AND b26.Workgroup = N'OUTBOUND - [Welcome Calls]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b27
					WHERE b27.RecordingID = c.RECORDINGID
						AND b27.Workgroup = N'OUTBOUND CS - [Cancelations]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b28
					WHERE b28.RecordingID = c.RECORDINGID
						AND b28.Workgroup = N'OUTBOUND CS - [DocChase]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b29
					WHERE b29.RecordingID = c.RECORDINGID
						AND b29.Workgroup = N'OUTBOUND CS - [Final Notice]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b30
					WHERE b30.RecordingID = c.RECORDINGID
						AND b30.Workgroup = N'OUTBOUND CS - [Initial Chase]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b31
					WHERE b31.RecordingID = c.RECORDINGID
						AND b31.Workgroup = N'OVERFLOW - [Claims]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b32
					WHERE b32.RecordingID = c.RECORDINGID
						AND b32.Workgroup = N'OVERFLOW - [Customer Service - MTA]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b33
					WHERE b33.RecordingID = c.RECORDINGID
						AND b33.Workgroup = N'RENEWALS - [Personal Enquiries]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b34
					WHERE b34.RecordingID = c.RECORDINGID
						AND b34.Workgroup = N'RENEWALS - [Personal Outbound]'
					)
				OR EXISTS (
					SELECT *
					FROM TableTwo b35
					WHERE b35.RecordingID = c.RECORDINGID
						AND b35.Workgroup = N'RENEWALS - [Personal Payments]'
					)
				)
			AND c.RecordingDate >= @p_dateAfter
			AND c.RecordingDate < @p_dateBefore
			OR c.RecordingDate < @p_dateAfter
			OR c.RecordingDate >= @p_dateBefore
		)

 

Here are some of the issues with the above in no particular order.

The command is running against a 312 million row table, a 55 million row table and a 22 million row table. Although is was only planned to run once IT STILL NEEDS TO BE EFFICIENT!!!!!

It uses Select Star or (Select *) which raises the probability that SQL Server will query the whole table rather than accessing the data through indexes. Only an Id is needed to begin with.

There are subqueries used when a join would be much faster.

The subqueries are completely unnecessary when WHERE conditions could have been listed in an IN statement.

Correlated subquery!!! Literally designed to bring a server to its knees! Evaluated once for each row processed. See more on correlated sub queries here https://en.wikipedia.org/wiki/Correlated_subquery

There are EXISTS Statements used due to the poor design of the command.

A ridiculously giant case statement used! Typically the comparison data should be written to a temp table and assessed as a join when there are an excessive number of case statements.

Needless to say this query ballooned the tempdb and I’ll let you guess what happened next.

The lesson for today’s posting, trust no one!

A picture with garbled characters and the word password in the middle

How to generate a random password with T-SQL

The following script will generate a random 10 character password that meets the complexity requirement for Microsoft Windows. To generate a password just run the script in a new SQL Server Management Studio window. The logic can also be easily turned into a function.

The option of symbol characters is limited to what’s shown below as dealing with quotes and obscure characters in a password is often more trouble than it is worth. The password generated however should still be very secure as it will be 10 characters long with a guaranteed number, lowercase letter, uppercase letter and a symbol.

!
#
$
%
&
(
)
*
+

/*Declare Variables*/
DECLARE @i INT;
DECLARE @Pw VARCHAR(MAX);
DECLARE @Numbers TABLE (Characters CHAR(1));
DECLARE @LowerCase TABLE (Characters CHAR(1));
DECLARE @UpperCase TABLE (Characters CHAR(1));
DECLARE @Symbols TABLE (Characters CHAR(1));
DECLARE @BaseCharacters TABLE (Characters CHAR(1));
DECLARE @GuaranteedCharacters TABLE (Characters CHAR(1));
DECLARE @PwCharacters TABLE (Characters CHAR(1));

/*Generate Numbers*/
SET @i = 0;

WHILE @i <= 9
BEGIN
	INSERT INTO @Numbers
	SELECT @i

	SET @i = @i + 1
END;

/*Generate Lowercase Letters*/
SET @i = 97;

WHILE @i <= 122
BEGIN
	INSERT INTO @LowerCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Uppercase Letters*/
SET @i = 65;

WHILE @i <= 90
BEGIN
	INSERT INTO @UpperCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Symbols*/
SET @i = 33;

WHILE @i <= 43
BEGIN
	IF (
			@i = 34
			OR @i = 39
			)
	BEGIN
		SET @i = @i + 1
	END

	INSERT INTO @Symbols
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*
Randomly Select A Number, Lowercase Letter,
Uppercase Letter And A Symbol So Four Character Types
Are Guaranteed To Be Present Somewhere In The Password
*/
INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Numbers
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @LowerCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @UpperCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Symbols
ORDER BY NEWID();

/*
Randomly Select Another 6 Characters
*/
INSERT INTO @BaseCharacters
SELECT TOP 6 Characters
FROM (
	SELECT Characters
	FROM @Numbers
	
	UNION ALL
	
	SELECT Characters
	FROM @LowerCase
	
	UNION ALL
	
	SELECT Characters
	FROM @UpperCase
	
	UNION ALL
	
	SELECT Characters
	FROM @Symbols
	) AS Characters
ORDER BY NEWID()

/*Generate A 10 Character Password*/
INSERT INTO @PwCharacters (Characters)
SELECT Characters
FROM (
	SELECT Characters
	FROM @BaseCharacters
	
	UNION ALL
	
	SELECT Characters
	FROM @GuaranteedCharacters
	) AS Characters
ORDER BY NEWID()

/*Save The Password To A String*/
SELECT @Pw = COALESCE(@Pw + Characters, Characters)
FROM @PwCharacters

SELECT @Pw AS PW;

 

If you found this post helpful please like, comment and share.

How to determine why a T-SQL command is unreasonably slow

If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.

The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.

/* Queries Not Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status <> 'running';

/* Queries Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status = 'running';

 

This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.

Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.

  • status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
  • blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use EXEC sp_who2 to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session.
    KILL blocking_session_id /*replace by the actual Id*/

    NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.

  • wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
  • wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
  • last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.