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