Vision: SQL Homeostasis

We know that indexes are necessary for query performance but comes with overhead during DML operations. Usually, the overhead is worth the performance gain we get out of them. What I needed was to find and consider for removal indexes that incur heavy maintenance overhead, but are only lightly used for queries or enforcing constraints. Here is an a version of query to find unused or lesser used indexes. It looks for non-unique indexes with large number of DMLs with ratio of writes vs reads is relatively high (I am using 10 but at this point its an arbitrary starting point). WITH   cte       AS   ( SELECT   Db_name ( iu . database_id )                     db_name ,                  Object_name ( iu . object_id ,   iu . database_id )   ob...

If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is every approximately 10%. You can change that frequency interval by specifying STATS [ = percentage ] option. BACKUP DATABASE [AdminDBA] TO  DISK = N'O:\MSSQL13.SQL2016AG01\MSSQL\Backup\AdminDBA.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdminDBA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO But what if the backup/restore was started from a different session that you don't have access (another DBA, scheduled job etc.) or you need more information to troubleshoot issues? Here I should first mention that there are already couple options to track the progress. You could review or query the sql server error logs (unless trace flag 3226 is enabled). By default its disabled. You can if that trace flag is enabled using: DBCC TRACESTATUS ( 3226 ); DBCC TRACESTATUS ( 3226 ) If trace fla...

I realized yesterday I posted this quickly after testing only against SQL 2016 version. So I decided to also test against SQL 2014 and 2012 versions to make sure its compatible.  I made one tweak (removed the compression_delay field from the output) to make it possible.  So please give it a try and let me know your results, perspectives and feedback! As I had mentioned in my original post, I was looking to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. The query below uses two variables to make it little bit more flexible. If you have a need or just curious to find indexes having same columns but in any order, set value for the variable  @disregard_column_order  =  1 in the code. The query excludes identical indexes where one is clustered and the other one is non-clustered index.  But you can again toggle that by setting value for variable  @include_clustered_indexes = 1. /* whether to i...

I realized yesterday I posted this quickly after testing only against SQL 2016 version. So I decided to also test against SQL 2014 and 2012 versions to make sure its compatible.  I made one tweak (removed the compression_delay field from the output) to make it possible.  So please give it a try and let me know your results, perspectives and feedback! As I had mentioned in my original post, I was looking to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. The query below uses two variables to make it little bit more flexible. If you have a need or just curious to find indexes having same columns but in any order, set value for the variable  @disregard_column_order  =  1 in the code. The query excludes identical indexes where one is clustered and the other one is non-clustered index.  But you can again toggle that by setting value for variable  @include_clustered_indexes = 1. /* whether to i...