How to capture deadlocks using system_health Extended Events

Find SQL Server deadlock details easily with Extended Events Read more →

How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

Question: How can I monitor SQL Server blocked processes with a SQL Server alert and capture the details for analysis. I’d like the alert to trigger when there is more than one blocked process. When the blocked process triggers the SQL alert, I want sp_whoisActive to execute , capture details and email the report. sp_whoisActive is one of the tools I use when troubleshooting SQL Server performance issues Read more on sp_WhoIsActive utility for SQL Server troubleshooting Answer: There are multiple ways of achieving this goal. Monitoring SQL blocked processes is an excellent way of identifying tuning opportunities. The method... Read more →

Number of deadlocks since last SQL Server restart

Question: How can I view the amount of deadlocks , which occurred on a SQL Server instance , since the last SQL Server restart? I’m looking to use this number as a sign to dig deeper into code running and identify details of the deadlock. I’ve read SQL Server - How to detect and troubleshoot blocking and deadlocks , which outlines the two main scenarios of blocking – i.e Blocking and permanent blocks aka deadlocks. In the article , there are some methods of troubleshooting the deadlock scenario and considerations for the application and how to manage the sql code... Read more →

SQL Server – Concurrency Control – Optimistic and Pessimistic

Concurrency control manages conflicts in data modification. Optimistic Locking and Pessimistic Locking have different consequences for data concurrency levels. Optimistic locking assumes the relevant data won’t change while the application reads and Pessimistic locking assumes changes may occur and locking is applied. There a two categories of concurrency control 1) Pessimistic 2) Optimistic Pessimistic is normally used in high contention environments. An example, is a financial transaction system where there are many writes and reads on the same data. When USER A applies a lock , no other user can commit a conflicting action until USER A releases the lock.... Read more →

How to detect and troubleshoot blocking and deadlocks

Welcome to the pain cave of SQL Server blocking & deadlock analysis Read more →