This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 19%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAN%3C/text%3E%3C/svg%3E)
How to identify slow-running queries in SQL Server and How to solve it(same t sql query runs in one ssms high and other ssms it runs slow)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 37%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN%3C/text%3E%3C/svg%3E)
Identifying and solving slow-running queries in SQL Server can involve various factors. Here's a general approach to help you identify and troubleshoot slow queries:
1. Query Performance Monitoring:
Execution Plan: Check the query execution plan to identify any inefficiencies. You can use the SQL Server Management Studio (SSMS) to view the execution plan for the query. Look for missing indexes, key lookups, and scans that might indicate performance issues.
Dynamic Management Views (DMVs): Utilize DMVs like sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_query_stats to monitor the current running queries, their execution plans, and resource utilization.
2. Query Tuning:
Indexes: Ensure that your tables have appropriate indexes. Missing or incorrect indexes can significantly impact query performance. Analyze the execution plan to identify missing indexes and consider adding them.
Parameter Sniffing: Sometimes, queries run well with certain parameters but poorly with others. This could be due to parameter sniffing. Use OPTION (RECOMPILE) or OPTIMIZE FOR query hints to address this issue.
Table Statistics: Outdated statistics can lead to suboptimal query plans. Regularly update statistics on your tables to provide the query optimizer with accurate information.
Query Rewriting: Rewrite complex queries to be more efficient. Break down complex queries into smaller, simpler subqueries or use common table expressions (CTEs) to improve readability and performance.
3. Environment Factors:
Server Load: Check the server's CPU, memory, and disk usage. High resource utilization can lead to slower query performance.
Blocking and Deadlocks: Analyze if blocking or deadlocks are occurring. These can impact query performance and overall system responsiveness.
Network Latency: Network issues can affect query performance, especially if you're accessing the database remotely.
4. Version and Compatibility:
5. Execution Context:
SET options can lead to variations in query performance. Ensure that both SSMS instances are using the same SET options.6. Query Execution Plan:
DBCC FREEPROCCACHE to clear the plan cache before testing.7. Resource Competition:
8. Query Optimization Tools:
Remember, query optimization can be complex and involve trial and error. It's recommended to test changes in a controlled environment before implementing them in a production environment. If the same query runs fine in one SSMS instance but not in another, the discrepancies could be due to various factors mentioned above. Investigate each potential issue to pinpoint the exact cause and address it accordingly.
Hi,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Hi @A ngel
You can refer to these two links.
https://www.sqlshack.com/how-to-identify-slow-running-queries-in-sql-server/
Best regards,
Percy Tang