Vision: SQL Homeostasis

Suppose you want to or need to know anytime your SQL Server is experiencing blocking where queries are being blocked and it may or may not escalate to a deadlock situation. Chances are you might be using a comprehensive tool to get performance alerts. But I don't always have access to such tools at the clients. So here I have a SQL Server Agent alert to get automatic email when SQL Server detects queries being blocked. There are basically two key alert settings I am using: 1.     The minimum threshold value for sending the email alert is 4. That means if more than 4 processes are involved in blocking an email is sent out immediately. 2.     The alert email shows what the current number of process being blocked is (I highlighted it in yellow in below alert). That way we can assess how quickly or slowly the blocking is getting worse. After that first email alert, if the situation does not resolve itself, it will keep sending the email every 15 minutes with th...

Do you think you need to use commercial tools  like SQL Diagnostic Manager, SQL Sentry, Spotlight etc.. ?  Or do you think the built-in features are enough? There are complelling arguments in favor of each. In this blog I hope to touch on every built-in feature to gather the performance information, some give  real-time overviews of your databases whereas others provide detailed, on-demand data on SQL Server health and performance. I hope you learn something new. Using T-SQL There are several ways to use T-SQL queries to monitor SQL Server, using dynamic management views, built-in functions, stored procedures, or system data collections… Dynamic management views DMVs are virtual tables that you can query on adhoc or as part of your custom, automated monitoring.   Some shows sql server state at a particular instant whereas others, especially those that deal with performance counters, measures values on a regular internal and show you the difference between two samples...