With the popularity and use cases of AI growing these days, I thought it would be fun to leverage ChatGPT for blog post ideas. This is part one of a seven part series (one post per day) where I blog about the topics ChatGPT gives me.
The Importance of Performance Tuning
I like to think of SQL Server as a living, breathing organism. Without continuous nourishment and attention, your beloved databases will slowly start to wither away. Well, maybe I’m being a little dramatic, but things can easily go south over time if your environment is not maintained. Performance tuning is one of many aspects of SQL Server database and server health. In most environments, you may have situations where things get slow, or maybe things have always been slow and you want to find a way to fix that. Performance tuning knowledge is essential for remediating those issues for the immediate, and long term.
Staying Away From the Rabbit Hole
Performance tuning is an art really. I could write down everything I’ve learned so far in my career in one post, but then you’d be reading for an absurd amount of time, and let’s face it, you have better things to do, and quite frankly, so do I. So, in the interest of time, and attention span (talking about mine of course, not yours because you are perfect in every way), I’ll limit this post to five key essential performance tuning tips.
Tip #1: Learn your Wait Types
When you’re either proactively working on performance tuning, or reactively responding to performance degradation, finding out what queries are waiting on will give you some immediate feedback of what you need to do to solve the issue. There are a plethora of wait types out there, some more common to see than others so I recommend creating yourself a little cheat sheet of the different types, and the ways you can go about troubleshooting them.
Tip #2: Get Good at Indexing
In my opinion, 90% of the time performance issues involve indexing improvements. You usually will find yourself in situations where there aren’t enough indexes on a table, too many indexes on a table, or the indexes being used need to be modified to better support existing queries hitting your SQL Server.
Tip #3: Leverage Monitoring Tools and Scripts
Monitoring tools are number one on my list for quick and clear visualization of what is happening on your SQL Server. Tools such as Solarwinds SQL Sentry, Solarwinds DPA, DataDog and others of the likes are great performance monitoring tools out there. Getting familiar with those will make you more prepared to handle performance issues. When monitoring tools are unavailable, or if you want to add some additional options for performance troubleshooting, scripts are also very useful. There are a lot of incredible free tools and scripts out there to help you troubleshoot performance issues. One being Brent Ozar’s First Responder Kit. It is jam packed with useful procedures that can be ran to help you diagnose issues when performance declines. Another is Adam Machanic’s sp_WhoIsActive procedure that is very helpful when you want to know what is running on your server. Erik Darling also comes to mind with his sp_HumanEvents stored procedure. One other item I’ll include here, because you could argue that it could be a monitoring tool of sorts is Query Store. Query Store is fantastic for finding regressed queries that are consuming a lot of resources. It’s helpful for getting a quick snapshot view if a query’s plan has changed and could give you the option to force a better plan, amongst other features. All of the above options give you the inventory of what is happening on your server and what the bottlenecks are so you can quickly jump in and diagnose your SQL Server.
Tip #4: Familiarize Yourself with Query Execution Plans
Whether you’re using monitoring tools, or native SQL Server scripts to troubleshoot performance issues, at some point sooner than later you’ll be faced with reading and analyzing query execution plans. Execution plans give you amazing insight into what a query is doing when it runs and what SQL is choosing to do when optimizing a plan for the query. When performance slows down or degrades for a query, looking at the query plan will give you a play-by-play summary of what the query did and will allow you the opportunity to identify where the bottleneck occurred. This way you can pinpoint your efforts on fixing that part of the query.
Tip #5: Look out for Query Anti-patterns
Have you ever heard of the phrase “There’s more than one way to skin a cat”? Well there’s more than one way to poorly write a query. There are many different ways to write a query that does the same thing. However, the way you write that query can dramatically impact performance. There are anti-patterns in query writing that can hinder a query from being as efficient as it could be. Learn what those are and that will help improve your skillset when it comes time to address performance issues.
Well, That’s Great but What Now?
The next steps I would recommend for you are to learn, practice and apply. Here are a few great resources I’ve used in my career that offer training in a lot of the areas I mentioned above:
Happy Tuning!
DBA Unfiltered 
Leave a comment