Pablo Aizpiri

My experiences and thoughts on [mostly] technology.

Tag: Performance

SQL Troubleshooting Queries (2)

July 10, 2015 / / 0 Comments

Found this MSSQL query on StackOverflow for troubleshooting locks or not being able to access a database being taken offline.. posting for self-reference:

SELECT
        DB_NAME(l.resource_database_id) DB,
	l.resource_type,
	l.request_mode,
	l.request_status,
	l.request_session_id,
	r.command,
	r.status,
	r.blocking_session_id,
	r.wait_type,
	r.wait_time,
	r.wait_resource,
	request_sql_text = st.text,
	s.program_name,
	most_recent_sql_text = stc.text
FROM sys.dm_tran_locks l
LEFT JOIN sys.dm_exec_requests r
	ON l.request_session_id = r.session_id
LEFT JOIN sys.dm_exec_sessions s
	ON l.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c
	ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
--WHERE l.resource_database_id = DB_ID('[Database Name]')
ORDER BY request_session_id;

Some additional helpful links:
Lock Modes
Wait Types; Additional wait Type Info
Locks System Table Info

Also, connection by session:

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = [SESSION ID]

SQL Troubleshooting Queries (Locks)

July 2, 2015 / / 0 Comments

Some helpful queries used to do lock/running query diagnosis.

sp_who and sp_who2 system stored procedures returns some helpful information for diagnosing current connections; but it’s hard to filter. Here’s a quick query for dumping the results into a temp table for filtering. The key field is “status” to help you determine if the connection is hung.

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

DELETE FROM @Table
INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE DBName --...

In my case, we discovered it was a SQL Server Agent connection and it was running a job that was hanging. Some additional helpful queries to answer What is the Job Id? What are the current job statuses?

SELECT *
FROM msdb..sysjobs s 
LEFT JOIN master.sys.syslogins l 
      ONs.owner_sid = l.sid

EXEC msdb.dbo.sp_help_job @Job_name = '[..]'

Stop a job:

EXEC msdb.dbo.sp_stop_job @Job_name = '[..]'

Finally, a query to request last time a stored procedure was changed:

SELECT *
FROM sys.objects
WHERE type = 'P' 
AND NAME = '[Stored Procedure Name]'

© 2023 Pablo Aizpiri

Theme by Anders Noren — Up ↑