My favorite part of Comprehensive Database Performance Health Check is to resolve slow issues which are often very complicated to resolve. Recently I was hired for a specific task where my client had a very important query running usually in just subsecond, running for over 60 seconds occasionally. This was a very interesting situation and at the end of we had found the culprit for the slow query and resolved it. This entire experience inspired me to write this blog post.
5 Steps to Speed Up Slow Queries
If you ever have a situation where query suddenly gets slower, you can try out following five steps.
Step 1: Identity Blocking for Slow Queries
The first step is to check for any blocking scenario. I will suggest you use the following SQL stored procedure and see if any session is blocking any other session.
EXEC sp_Who2
If there is any blocking, you can investigate those queries and find the reason for blocking and resolve it.
Step 2: Maintenance Tasks
It is important to make sure that there are no maintenance tasks are running when queries are suddenly getting slower. I have often seen when backup jobs, statistics update or index maintenance tasks start they create resource crunch of IO, which leads to temporarily slow down of queries.
Step 3: File growth
This database configuration setting is often ignored when a database is setup. I have seen this particular setting creating lots of performance issues. For your database, if the file growth is set up very small, you may occasionally face a performance problem. It is recommended that you set file growth to a higher value like your actual weekly file growth.
You can read more about this in this blog post about How to Track Autogrowth of Any Database? – Interview Question of the Week #205. If you want to identify when and who did auto-growth for the database, I strongly recommend you to read this blog post: SQL SERVER – Script – When/Who did Auto Grow for the Database? I have also recorded Two Free Videos about SQL Server Performance Tuning and Optimization which you can watch to learn similar other settings related to SQL Server Performance optimizations.
Step 4: TempDB Contention
Your TempDB is one of the most important part of your SQL Server instance. Quite a lots of people think that TempDB is not important as the name of the database is TempDB. Personally, I believe that TempDB should be renamed as ImportantDB or CriticalDB as if your TempDB has problems, it will impact pretty much every single database in your system as well as many of your queries.
There are quite lots of things we can do with the TempDB to keep it in the optimal health. Here are a few of the important resources:
- SQL SERVER – Script to Find and Monitoring TempDB Space Usage
- SQL SERVER – T-SQL Script to Find Details About TempDB Information
- What is the Initial Size of TempDB? – Interview Question of the Week #120
- SQL SERVER – Identifying Query Growing TempDB
- How to Shrink TempDB Without SQL Server Restart? – Interview Question of the Week #163
- SQL SERVER – How to Shrink tempDB Database?
In the future, I will write a dedicated post about TempDB optimization methods.
Step 5: ThreadPool Wait
One of my clients recently faced this particular wait type and the solution was a bit more complex. Wait Statistics helps you to diagnosis your SQL Server Performance issues. If your server is facing any particular issues, wait statistics will always show them. It is quite possible that your queries are still running fine but you have run out of the threads for your SQL Server to execute your query. Your query will wait for SQL Server engine to provide a thread to run your query and once it receives the thread, it will immediately run it.
Here is the script which I have written earlier can help you to identify What is your Topmost wait Statistics. You can run a query for your server and see what are the top waits and later on resolve them with the help of Wait Stats Resolution Guide. If you are not sure about your wait statistics, you can always send me an email and I will be happy to interpret it for you.
If you are facing an issue with the ThreadPool, here are a blog post which should help you resolve them: SQL SERVER – Changing Max Worker Count for Performance.
Slow Queries and Resolution
There are many reasons for the slow queries but I personally believe that the above solutions are a great starting point to resolve your slow queries. If you follow all the steps, I have listed above and still face slow performance with your queries you can always send me an email at pinal@sqlauthority.com and I will be happy to help you to resolve your slow performing application.
Please note that for this month there is a 33% discount on Comprehensive Database Performance Health Check. You can always purchase this service in advance and use it anytime during the next 12 months! This unique deal will not be available again.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
how much of this applies to azure?
Example:1 Select * From View where date=@date and Actcd IN(‘100′,’200’)
Example:2 Select * From View Where date=@date and Actcd IN(Select Actcd From tb1 where Id=2)
Select Actcd From tb1 where Id=2 gives values ‘100’,’200′
Hello sir,
In Above example:1 for Actcd literal values are passed and it runs fast but when i use example:-2 query get stuck and slow.
sir can you please give explain such behaviour of SQL server as i need to use dynamic query like in example-2.
Is this because of query plan?