February 2, 2015 at 7:23 pm
Hi
Our DB was slow from yesterday.
(some times is good and some times long waits)
I Run sp_who2 And many Blocked spid found.
Such as : n spid blocked by 54
When I see 54 : in cmd column : "Awaiting Command"
1- What is meaning of this cmd? And What Do I Do about them ?
2- What do I do , for seeing current Blocked Transaction And a history of them ?
3- What about DBCC OpenTran ? What can we do with this command? It returns some cods and ids, no command ... and sql text and ... ?
thank you
February 3, 2015 at 5:52 am
Since you're working in SQL SErver 2014, I strongly recommend you start using Dynamic Management Views to access information about the state of your systems. Using sp_who2 and DBCC commands is very pre-SQL Server 2005. To get you started on being able to query the system and understand what's going on, there's a great book, Performance Tuning with SQL Server Dynamic Management Views (free to download at that link).
The basics to see a query would be:
SELECT *
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) as dest;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2015 at 12:17 pm
If you see a chain of blocks that hold waiters for a long time, you can kill the spid that is in the very beginning of this chain.
DBCC opentran will show you all open transactions, but only in current database. On a server level you can check open_tran in sys.sysprocesses. Open trans may cause your trans log to grow large. To find the command, copy SPID number and put it into DBCC inputbuffer().
February 3, 2015 at 12:22 pm
SQL Guy 1 (2/3/2015)
If you see a chain of blocks that hold waiters for a long time, you can kill the spid that is in the very beginning of this chain.DBCC opentran will show you all open transactions, but only in current database. On a server level you can check open_tran in sys.sysprocesses. Open trans may cause your trans log to grow large. To find the command, copy SPID number and put it into DBCC inputbuffer().
But, killing that spid will put the process into rollback. That could take as long or longer than the original set of blocks.
Also, why use DBCC opentran and inputbuffer when querying the DMVs is so much easier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2015 at 7:05 am
We use a script to zero in on the top blocking SPID then investigate that SPID. There are several good scripts on the net - below is one that we use:
--Best to output results to text rather than grid -- SQL 2005 and above only
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
DROP TABLE #Processes
DECLARE @results TABLE (
id INT IDENTITY(1,1),
DB_Name VARCHAR(500),
BlockingSPID INT, SPID INT,
BlockingStatement VARCHAR(MAX),
RowNo INT, LevelRow INT
)
SELECT
s.[spid],
BlockingSPID = s.[blocked],
DatabaseName = DB_NAME(s.dbid),
s.program_name,
s.loginame,
ObjectName = OBJECT_NAME(objectid, s.dbid),
Definition = CAST(text AS VARCHAR(MAX))
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50;
WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, DB_Name)
AS
(
SELECT
s.[spid],
s.[BlockingSPID],
s.Definition,
ROW_NUMBER() OVER(ORDER BY s.[spid]) AS RowNo,
0 AS LevelRow,
s.[DatabaseName] AS DB_Name
FROM #Processes s
INNER JOIN #Processes s1 ON s.[spid] = s1.BlockingSPID
WHERE s.BlockingSPID = 0
UNION ALL
SELECT
r.[spid],
r.BlockingSPID,
r.Definition,
d.RowNo,
d.LevelRow + 1,
r.DatabaseName AS DB_Name
FROM #Processes r
INNER JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE r.BlockingSPID > 0
)
INSERT INTO @results (
[DB_Name],
[BlockingSPID],
[SPID],
[BlockingStatement],
[RowNo],
[LevelRow]
)
SELECT
MIN(DB_Name) AS DB_Name,
BlockingSPID,
SPID,
BlockingStatement,
MIN(RowNo),
LevelRow
FROM Blocking
GROUP BY BlockingSPID, SPID, BlockingStatement, LevelRow
ORDER BY MIN(RowNo), LevelRow
SELECT
CASE
WHEN [BlockingSPID] = 0
THEN '
****Head of Blocking Chain SPID ' + CAST([SPID] AS VARCHAR(50)) + '...
'
+ 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
+ '
'
+ '...is blocking the following SPID(s):'
WHEN [LevelRow] > 1
THEN '
'
+ SPACE(LevelRow * 5) + 'SPID '
+ CAST(BlockingSPID AS VARCHAR(50))
+ ' is, in turn, blocking the following SPID:
'
+ SPACE((LevelRow + 1) * 5) + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
ELSE '
'
+ SPACE(LevelRow * 5) + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
+ '
'
END
FROM @results
IF NOT EXISTS (SELECT TOP 1 * FROM @results)
PRINT 'No Blocking Chains Were Found'
BEGIN TRY
DROP TABLE #Processes
END TRY
BEGIN CATCH
END CATCH
March 6, 2015 at 12:45 pm
sm_iransoftware (2/2/2015)
HiOur DB was slow from yesterday.
(some times is good and some times long waits)
I Run sp_who2 And many Blocked spid found.
Such as : n spid blocked by 54
When I see 54 : in cmd column : "Awaiting Command"
1- What is meaning of this cmd? And What Do I Do about them ?
2- What do I do , for seeing current Blocked Transaction And a history of them ?
3- What about DBCC OpenTran ? What can we do with this command? It returns some cods and ids, no command ... and sql text and ... ?
thank you
A lot of blocks (and even maybe deadlocks?) would prompt me to send the code back to the developers to have it written properly. What sort of changes have been made since it ran well and now?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply