SQL Server – Monitor Blocking

\ by

Here’s how to write blocking messages out to the SQL Errorlog. It won’t look pretty but has everything you should need.


/*
Set up a trace to monitor blocking on a server.

*/

-- Trace locks into the Errolog file
DBCC TRACEON(1222, -1);
-- Check the status
DBCC TRACESTATUS (1222, -1)
-- and turn it off
DBCC TRACEOFF (1222,-1)

Here is a link which tells how to set up the SQL Profiler to monitor deadlocks using a Deadlock Graph. Choose “Save Deadlock XML Separately” in the new tab and “Each deadlock XML graph in a distinct file” as options and give it a path and filename. This way you can capture and read each lock easily, later.

Find any current locking … blocking for a particular database. I haven’t tried this.


/*

http://stackoverflow.com/questions/12422986/sql-query-to-get-the-deadlocks-in-sql-server-2008

*/

SELECT L.request_session_id AS SPID,
 DB_NAME(L.resource_database_id) AS DatabaseName,
 O.Name AS LockedObjectName,
 P.object_id AS LockedObjectId,
 L.resource_type AS LockedResource,
 L.request_mode AS LockType,
 ST.text AS SqlStatementText,
 ES.login_name AS LoginName,
 ES.host_name AS HostName,
 TST.is_user_transaction AS IsUserTransaction,
 AT.name AS TransactionName,
 CN.auth_scheme AS AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P
ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O
ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES
ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST
ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT
ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN
ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = DB_ID()
ORDER BY L.request_session_id
Posted in: Performance, SQL, SQL Server

SQL Server – Disk Monitoring

\ by

On a SAN, don’t worry about “OS Average Disk Queue Length Per Disk”. Explained.

What counters in Windows Performance Monitor show the physical disk latency? 
“Physical disk performance object -> Avg. Disk sec/Read counter” -  Shows the average read latency. 
“Physical disk performance object -> Avg. Disk sec/Write counter” -  Shows the average write latency. 
“Physical disk performance object -> Avg. Disk sec/Transfer counter” - Shows the combined averages for both read and writes. 
The “_Total” instance is an average of the latencies for all physical disks in the computer. 
Each other instance represents an individual Physical Disk.

Within SQL:

 /*
Quickly see where the read and write hot spots are and then drill into a database to see what’s going on, and if nothing out of the ordinary, ask the SAN admin to move those hot spot files to dedicated and/or faster storage.

Filter on read or write latencies and it joins with sys.master_files to get database names and file paths.
*/
SELECT
 --virtual file latency
 [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0
 ELSE ([io_stall_write_ms] / [num_of_writes])
 END,
 [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0
 ELSE ([io_stall_read_ms] / [num_of_reads])
 END,
 [Latency] = CASE WHEN ([num_of_reads] = 0
 AND [num_of_writes] = 0
 ) THEN 0
 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes]))
 END,
 --avg bytes per IOP
 [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0
 ELSE ([num_of_bytes_read] / [num_of_reads])
 END,
 [AvgBPerWrite] = CASE WHEN [io_stall_write_ms] = 0 THEN 0
 ELSE ([num_of_bytes_written] / [num_of_writes])
 END,
 [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0
 AND [num_of_writes] = 0
 ) THEN 0
 ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes]))
 END,
 LEFT([mf].[physical_name], 2) AS [Drive],
 DB_NAME([vfs].[database_id]) AS [DB],
 --[vfs].*,
 [mf].[physical_name]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
 AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO
Posted in: SQL Server

SQL Server – Fix – Error: 17204 and FCB::Open failed

\ by

1. First error in Windows Application log file
FCB::Open failed: Could not open file D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDB.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).

2. Second error.
Error: 17204, Severity: 16, State: 1.

The fix (for me)

ALTER DATABASE MyDB SET ONLINE

See also this.
And this and this command “alter database set emergency dbcc checkdb (repair_allow_data_loss)”.

Posted in: SQL Server