Tools and Scripts

SQL Vulnerability Assessment Tool Reference List – Updated!

2020-11-11
Microsoft Azure, SQL Server Database Engine, Tools and Scripts
1 Comment

Way back at January 16, 2020, I published a blog post containing a reference list for the many rules checked by the SQL Vulnerability Assessment Tool. The next month, I created a separate, dedicated page for the reference list so that it’d be easier to find and maintain. Today I learned that a few months later, around the beginning of May, Microsoft themselves have also published such a reference list on the Microsoft Docs page.

How to Troubleshoot THREADPOOL Waits and Deadlocked Schedulers

2020-10-052022-12-31
SQL Server Performance, T-SQL, Tools and Scripts
6 Comments

In this blog post I’ll help you troubleshoot the rare but critical issues of Deadlocked Schedulers and high THREADPOOL waits – using practical means.

Also, very useful T-SQL script towards the end.

Feature Request – Expand MSX/TSX Capabilities for HADR

2020-09-16
Life, SQL Server Database Engine, Tools and Scripts

Following my presentation of “How to HADR Your SQL Server Jobs” at the HA/DR PASS Virtual Group yesterday, David Klee (@kleegeek) and I continued chatting for a bit longer after the recording was concluded. During which, the MSX/TSX feature of SQL Server came up, and David suggested that I’d utilize my newly earned MVP status to push a feature request to expand these capabilities.

Using Extended Events as a Buffer

Assume you have some kind of process outside the SQL Server which is supposed to record some kind of database activity. There are many ways to do it, most of which require constant and/or stable connection to the database. But what if that’s not good enough? Let’s consider our options.

Migrating from CosmosDB to SQL Server

Let’s be honest here, Microsoft doesn’t want you to read this. Microsoft doesn’t want you to have this. Microsoft doesn’t want this to be easy for you. Maybe with good reason. But most likely not really.

Control SQL Jobs based on HADR Role – Taking it to the Next Level

2020-05-262024-05-12
SQL Server Database Engine, T-SQL, Tools and Scripts
5 Comments

In one of my previous blog posts, Automatically Enable or Disable Jobs based on HADR Role, I provided a solution for automatically enabling or disabling scheduled jobs in combination with Availability Groups and Database Mirroring. In this blog post, I’m going to build upon my previous solution and improve it even further – taking it to the next level of automation!

Troubleshooting Long-Running SHRINK Operations

2020-04-072022-12-31
SQL Server Database Engine, Tools and Scripts
9 Comments

Ever had your SHRINK operation in SQL Server get “stuck” and didn’t know why, or how to solve it? It’s more common than you think. Check out this post for more info, tricks and tips.

Finding the Details Missing from the SQL Server Failed Logins Audit

2020-03-09
T-SQL, Tools and Scripts

We all know the error log used by SQL Server to audit failed logins by default. But is it good enough? For many scenarios, it isn’t. It’s actually missing a lot of important information. But, worry not, for there are better solutions!

The Ultimate Compression Savings Estimation Script for an Entire Database

2020-02-182024-02-06
Tools and Scripts
9 Comments

Data compression in SQL Server can definitely go a long way in saving storage space, as well as improve performance. It can especially be significant in very large databases. But in certain scenarios it can actually cause more harm than good, and it’s important to identify these scenarios.

I wrote this script which performs compression savings estimation checks for an ENTIRE database. Yeah there are plenty such scripts out there already, but there’s something in particular that they all seem to be missing, and that is the ability to actually generate the relevant remediation commands for you. After all, ain’t nobody got time for writing all those “ALTER TABLE/INDEX” commands themselves!

This is why I created the T-SQL script below. Check out the “messages” tab once the script completes execution, to find the resulting remediation commands. In addition, you will also get a query result with full details.

The script also performs various checks to determine whether PAGE or ROW compression type would be more beneficial (based on stuff such as range scans vs. updates percentage, difference in compression savings, etc.). It has several parameters at the top to control various thresholds and behaviors, so that you could fine tune it for your specific database.

SQL Vulnerability Assessment Tool – Rules Reference List

I never managed to find an online reference page for the rules run by the SQL Vulnerability Assessment tool. So I figured “why not do it myself?”. Cool script attached!