View Running SQL Statement, inside current processing Batches and Stored Procedures

Below SQL script can be used to view the current running (running, in runnable queue, rolling-back, and waiting for resources like locks) SQL statement, inside every current processing batches and stored procedures. Noted that the [text255] column represents the whole batch/stored procedure, but only shows the first 255 characters. Also, stored procedures are represented as "CREATE PROCEDURE" statement, it's just the definition, does NOT mean it's creating it.
This script is very useful for checking the SQL statement being stuck inside a batch and stored procedure.

SELECT
CASE WHEN (SELECT COUNT(*) FROM sys.sysprocesses WHERE spid = r.spid) > 1 THEN 'Multithread' ELSE '' END AS Multithread,
LEFT(t.[text], 255) AS [text255],
SUBSTRING(
t.[text],
(r.stmt_start / 2) + 1,
((
CASE
r.stmt_end
WHEN -1
THEN DATALENGTH(t.[text])
ELSE r.stmt_end
END - r.stmt_start) / 2) + 1) AS stmt,
DB_NAME(t.dbid) AS ObjectDB,
OBJECT_NAME(t.objectid, t.dbid) AS Object,
r.spid, r.ecid, r.blocked, r.waittime, r.lastwaittype, r.waitresource, DB_NAME(r.dbid) AS connectDB, r.cpu, r.physical_io, r.memusage, r.login_time,
r.last_batch, r.open_tran, r.status, r.hostname, r.program_name, r.loginame, r.cmd, r.net_library, r.login_time, r.stmt_start, r.stmt_end
FROM sys.sysprocesses AS r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.status IN ('runnable', 'suspended', 'running', 'rollback', 'pending', 'spinloop')
ORDER BY spid, ecid

Fixing SQL Server Login SID by mapping it to Database User

When you restore a database from another server, especially for setting Availability Group, Database Mirroring, and Log Shipping, the client application may not able to use database and getting login failed for user after failover. It's due to a well known problem called "SID mapping" between the SQL Server Login and the Database User. If you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password. But the SID in the Database User is brought from the original (primary) database, which only linked to the SID of the corresponding SQL Server Login in the original (primary) server.

In order to fix this problem permanently, you should create the SQL Server Logins in secondary server by explicitly specifying the SID, e.g.

1. In primary database, get the SID of the database user:
USE LoginTest;
GO
SELECT sid FROM sysusers WHERE name = 'GregTest';

2. In the secondary server, after you restored the database into it, you can take a look in the user mapping and see that the Database User is NOT linked to any Login:
USE LoginTest
GO
EXEC sp_change_users_login 'Report';

3. You should fix it by creating a new SQL Server Login in the secondary server with the same SID:
CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd', SID = 0x2261C43EFD53F240AA989A8FB9E084DC

4. Check again the SID mapping, you should see no missing anymore:

Checking Stored Procedure Performance Statistics

We can use the sys.dm_exec_procedure_stats DMV to check the last execution time, elapsed time, and other performance figures of stored procedures exist in the plan cache. Below is the SQL statement:

SELECT
DB_NAME(database_id) AS DB,
OBJECT_NAME(object_id, database_id)AS [OBJECT],
*
FROM sys.dm_exec_procedure_stats

@@TRANCOUNT = 2 During DML statement Executing

In SQL Server BOL, it said @@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. Also, it said the open_tran column of sys.sysprocesses returns the number of open transactions for the process. So you may intuitively think that if that transaction count value of a process is greater than 1, it should be an explicit nested transaction.
However, explicit nested transactions are not the only reason why the transaction count can be greater than 1 during execution of a DML statement. Consider the following example:

IF EXISTS (SELECT 1 FROM sys.objects WHERE [type] = 'U' AND name = 'T1') DROP TABLE T1;
GO
CREATE TABLE T1
(
Col1 int
);
GO

-- INSERT
INSERT INTO T1
--SELECT @@TRANCOUNT;
SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID;
-- returns 2
SELECT Col1 FROM T1;

-- UPDATE
UPDATE T1 SET
Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
WHERE Col1 = 2;
-- returns 2
SELECT Col1 FROM T1;

-- DELETE
DELETE
FROM T1
WHERE Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
-- returns nothing (as @@TRANCOUNT / sys.sysprocesses open_tran = 2)
SELECT Col1 FROM T1;

-- INSERT within Explicit Transaction
BEGIN TRAN
INSERT INTO T1
--SELECT @@TRANCOUNT;
SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID;
COMMIT
-- Still returns 2
SELECT Col1 FROM T1;

-- UPDATE within Explicit Transaction
BEGIN TRAN
UPDATE T1 SET
Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
WHERE Col1 = 2;
COMMIT
-- Still returns 2
SELECT Col1 FROM T1;

-- DELETE within Explicit Transaction
BEGIN TRAN
DELETE
FROM T1
WHERE Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
COMMIT
-- returns nothing (as @@TRANCOUNT / sys.sysprocesses open_tran = 2)
SELECT Col1 FROM T1;

This shows that during execution of a DML statement, there will be more than one opened transactions reported. The results are the same for @@TRANCOUNT, open_tran column from sys.sysprocesses, or the open_transaction_count column from the sys.dm_exec_requests.
Effectively, in addition to the one transaction always associated with any DML statement, there is another nested transaction opened internally by SQL Server, lasting for the duration of DML statement’s execution. To be clear, the second transaction is open only while a DML statement is executing. Such situation can be easily observed especially during Process Blocking.

CHECKDB on an Explicit Snapshot

Beginning with SQL 2005, DBCC CHECKDB creates a hidden snapshot on the same volume as the database – you have no control over where it’s placed. If you’re running CHECKDB at the same time that your server has a heavy workload the snapshot can run out of space and you’ll get an error showing that CHECKDB didn’t complete. In order to overcome this problem, you can create your own database snapshot on a drive that has enough space and run CHECKDB against that snapshot. CHECKDB will know that its running against a snapshot and won’t create another one. Below is an example:

CREATE DATABASE TestDB_Snapshot ON
(
    NAME = TestDB,
    FILENAME = 'C:\TestDB.ss'
) AS SNAPSHOT OF TestDB;
GO
DBCC CHECKDB (TestDB_Snapshot) WITH NO_INFOMSGS;
GO

Check Dependent Objects

In order to check the dependent objects being referenced by a specified object, e.g. to find the referenced tables of a stored procedure, previously we can used the sp_depends system builtin stored procedure. However, if the dependent objects (e.g. table) are created after the referencing object (e.g. stored procedure), sp_depends cannot find out such dependency. Started from SQL2008, there are two new DMFs (sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities) which overcome such problem. Let's have a try in the following example:

USE TempDB
GO

DROP TABLE TestTable
GO
CREATE TABLE dbo.TestTable
( ID INT,
Name VARCHAR(100))
GO

-- referencing usp1 NOT created yet
DROP PROC usp2
GO
CREATE PROCEDURE dbo.usp2
AS
EXEC dbo.usp1
GO

DROP PROC usp1
GO
CREATE PROCEDURE dbo.usp1
AS
SELECT ID, Name
FROM TestTable
GO

SELECT * FROM sys.dm_sql_referencing_entities ('dbo.usp1', 'OBJECT');
SELECT * FROM sys.dm_sql_referenced_entities ('dbo.usp1', 'OBJECT');
SELECT * FROM sys.dm_sql_referenced_entities ('dbo.usp2', 'OBJECT');

Get current stored proc name and params list

Inside a stored procedure, it can get the current stored procedure name and its parameters list by the follow statements:
DECLARE @names varchar(MAX) = OBJECT_NAME(@@PROCID) + ' ';
SELECT @names += name + ',' FROM sys.parameters WHERE [object_id] = @@PROCID ORDER BY parameter_id;