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...

I was entrusted with a new database for a critical application that was having general performance issue and the CPU usage constantly over 70%. It turned out that I did not have to sweat much to quickly and significantly improve the database performance and also bring down CPU % way below the threshold. The solution? For one thing, the database was missing many much needed indexes.  After making sure that it was the right thing to do, I added the indexes that would give the biggest performance boost. It instantly and greatly improved the performance. I was the hero! Secondly, the foreign key columns were not indexed and because of their relative small size, they would not easily be captured by the the famous sys.dm_db_missing_index_details DMV. Lack of indexes in foreign keys was not only causing minutely noticeable  performance impact but also leading to lock escalations that during heavy loads cause dead locks and exacerbating the situation. So I wrote the below query to get...

GUID or Integer Primary Key? If you are designing a new data model or maybe you are looking to improve an existing one.  One of the key decision you would be making is what should be the primary key in each table. Now first rule or best practice is that it should be a surrogate key i.e. arbitrary/meaningless unique value. In other words, it should not be based on a business key. Globally Unique Identifier aka GUID (also known as UUID) and auto generated Integer  are two of the popular methods used for a surrogate primary key. You may know that using GUID has certain advantages, its natural and virtually guaranteed to be globally unique, meaning across all tables, databases, servers... basically the whole universe. Whereas Integer is only guaranteed in a single table and only when defined as a primary or unique key.  And, even then you could end up having duplicates in bizarre and rare circumstances. Crazy things happens and I am speaking from a painful experience! So is ...