Home > SQL Tips > SQL Queries Executed, Running & Blocking

SQL Queries Executed, Running & Blocking


Here are some TIPS for checking what all SQL statements:
– were executed recently.
– currently running in background.
– blocking or deadlocking.

USE [AdventureWorks]
GO

--// Which SQL query or statement was executed recently?

-- Execute the Stored Proc with different parameters & some SQL queries or statements.
EXEC uspGetEmployeeManagers 1
GO
EXEC uspGetEmployeeManagers 2
GO
SELECT * FROM Person.Contact WHERE ContactID = 100
GO
SELECT * FROM HumanResources.Employee WHERE EmployeeID = 200
GO

-- Following SQL query will get you which SQL statement or Object was executed/used at what time and how many times.
SELECT object_name(b.objectid) AS obj_name, a.execution_count, a.last_execution_time, b.text
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
--where text like '%uspGetEmployeeManagers%'
ORDER BY 2 DESC
GO

--// Who's executing what SQL statements.
sp_who2
GO
-- You can check any SPID that is greater than 50. This will give you the SQL statement executing against that SPID.

-- Also check if any process is blocking any other process, the BlkBy column will give you the process SPID thats blocking the current process. Put that blocking SPID as paremeter in DBCC INPUTBUFFER() statement and get the SQL statement which has blocked your process.
DBCC INPUTBUFFER (51)
GO

-- You can also Kill the process by providing the blocking SPID as parameter with the KILL statement.
KILL 51
GO

More about:
- UDF(TVF) sys.dm_exec_sql_text
View sys.dm_exec_query_stats

About these ads
  1. Saurabh
    April 15, 2011 at 7:51 am

    Like It Sir Thanks for sharing :-)

    –Saurabh

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 396 other followers

%d bloggers like this: