Recently, I blogged about the Top 10 SQL Server Performance Tuning Tips, the things we typically see when we healthcheck SQL Servers here at Sequallity. Now, we have developed a simple script to find the top 10 SQL Server Performance Problems.

T-SQL Select GoFaster From SQLServer

The script can take between 20 seconds and 42 minutes to run, depending on the number of databases present on the SQL server. It looks at the usual suspects with poor SQL performance. It has been coded for low impact but there are a couple of things to talk about here…

Firstly, it checks for blocking. Over 20 seconds it runs 10 queries finding the number of blocks. If you have a high number of blocks or executing queries, this may take longer than 20 seconds. Run this on a test system first.

Secondly, it shells out to dos to find the Power Options settings and enables show advanced options. You may want to edit this part out. It’s a clever piece of code, sourced from Randolph West’s blog, Balanced Power Saving T-SQL script. What I love about this self-aware piece of SQL code is that is remembers the starting settings and sets them back.

This is specifically for on-premises SQL Server, virtual SQL Servers and SQL virtual machines. With this I wanted a quick sanity check on key settings and configuration settings for SQL Server, with a quick explanation of the issues. As a consultant, I have limited time to make the most impact and with scripts like this, I can pinpoint the problems immediately.

Please check our warranty page before running this query.

Find SQL Performance Problems Version History

V1-0 – May 14th 2020

OK, so this was the first release. In IT I’ve always found it easier to progress something when it’s built so I got it done and I can keep improving it over time.

V1-1 – June 17th 2020

I noticed the performance checking script didn’t do anything with minimum memory settings on SQL Server. So I added something a check so if the Max Memory matches the Min Memory to report on this.

I also fixed problems whereby the Out-of-Date statistics was picking up tables that couldn’t have their statistics updated, and changed it to 14 days and rowmodctr >0 for stale statistics.

I also added more descriptive information to some of the GoodorBadPoints. I run this to quickly find config issues when I run a Sequallity SQL Server Health Check. I’d like to expand it to include additional performance topics to it.

Leave a Reply