SQL with Manoj

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