Vision: SQL Homeostasis

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

Scenario: I was at a new client, with their previous and only DBA / DEVELOPER/ MASTER OF ALL of 8 years all suddenly gone with no documentation. Their business critical application was having data integrity issues. Some data were updated incorrectly while some others were not updated at all, showing old or incorrect data to their users/customers/vendors etc... After going through their stored procedures, triggers, view, ETLs, SQL trace etc... I was able to track down and fix most of the data issues. But there was one particular table that I could not figure out.  I thought maybe that table is never updated? How can I be sure what is going on with it? I decided I need to setup something to automatically monitor this table for few days.  Here, SQL server offers few options: Profiler trace Server side trace Extended events Audits Triggers CDC/CTC Service brokers .......... I knew that I only need to know how this particular table is being updated. I don't need to know the actual ...

You may and should have monitoring in place to monitor state of your servers, services, jobs, critical and not critical errors etc. Here I just wanted to share a quick script to create a SQL Server agent job to alert you whenever SQL Server service is started/restarted. If you have CMS setup, you could use it to deploy this across all SQL or most critical instances after tweaking it to meet your requirements -- For SQL 2005 or up -- Make sure it is running SQL 2005 or up IF ( @@microsoftversion / 0x1000000 ) & 0xff >= 9    PRINT 'This server is running SQL Server 2005 or up.' ELSE BEGIN    RAISERROR ( 'ERROR: This server is running SQL 2000 or older version, exiting...' , 16 , 1 )    return END   USE [msdb] GO EXEC msdb . dbo . sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1 GO   SELECT NEWID () USE [msdb] GO -- IF THE SERVER IS DEV OR TEST, CHANGE THE CRITICAL OPERATO FROM PAGE TO JUST EMAIL DECLARE @CriticalOperator v...