How to troubleshoot Slow-running queries on SQL Server

Steps to troubleshoot SQL Server queries that are taking too long to execute :

When a user approached you and said his query was taking too long to complete, As a DBA, we perform all of the basic checks, such as checking CPU usage, memory usage, any blockings, deadlocks, locks, fragmentation, statistics update, and so on. If the user still experiences slowness after performing all of these checks, we must follow the steps below to troubleshoot or fix slow-running queries on SQL Server.

1 Check for Index Analysis :That means we need to check if correct indexes are exist. When a user requests that a DBA study a slow-running query with which he is having problems, we can utilise Database Tunning Advisor to analyse the query and provide recommendations for indexes that will help us improve query performance. We may check if right indexes are placed in a database this manner.

2 We need to search for join hints: HASH,LOOP,MERGE, and REMOTE are the four forms of join hints. I believe SQL Server mostly use these operators: LOOP, HASH, and MERGE. This hint will override SQL Server query optimization, preventing the query optimizer from selecting a fast execution plan. Because the optimizer changes in the current SQL Server version or builds may have a negative impact on performance or may have no impact at all.

So, if the query we’re looking at has any hints, try to decrease or delete them before rerunning the query to see how it performs.

3 Check the Execution Plan: SQL query Analyser may be used to check the query execution plan. We may achieve this by selecting Display Estimated Execution Plan from the query option in the query window.

4 Check the ShowPlan Output: The ShowPlan Output provides information about the query execution plan used by SQL Server. The basic checks we must observe from the execution plan to establish if it is using the correct plan are listed below :

–> Index Usage
–> Join Order Usage
–> Join Types
–> Parallel Execution

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.