none
How to find Long running query & Slow running in query Sql Server RRS feed

All replies

  • Start reading

    https://www.sqlshack.com/how-to-identify-slow-running-queries-in-sql-server/

    Query Store is  your friend here


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 13, 2020 11:37 AM
  • Hello ajitkumar,

     

    There are many methods can find the slow queries in SQL Server, I think the most convenient method is to use T-SQL as bellow:

    SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

    ((CASE qs.statement_end_offset

    WHEN -1 THEN DATALENGTH(qt.TEXT)

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2)+1),

    qs.execution_count,

    qs.total_logical_reads, qs.last_logical_reads,

    qs.total_logical_writes, qs.last_logical_writes,

    qs.total_worker_time,

    qs.last_worker_time,

    qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

    qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

    qs.last_execution_time,

    qp.query_plan

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

    ORDER BY qs.total_logical_reads DESC -- logical reads

    -- ORDER BY qs.total_logical_writes DESC -- logical writes

    -- ORDER BY qs.total_worker_time DESC -- CPU time

    And here is an article introduce 7 ways to find slow SQL queries and performance tuning

     

    Hope it will help. If you have any question, please let’s me know.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 14, 2020 2:41 AM
  • Hello,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 16, 2020 5:50 AM
  • The difference in execution was due to the SET ARITHABORT parameter. For all queries executed in SSMS, this option is enabled, and for queries from the outside (from applications) – disabled. It cannot be enabled even by a simple query for applications:

    SET ARITHABORT ON;

    Refresh catch described here

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-ver15

    For the subsequent manual check,  need to write the following statement before the query in SSMS:

    SET ARITHABORT OFF;

    cleaning the entire procedural cache for the entire database every hour via SQL Agent:

    --cleaning the cache by database id
    DBCC FLUSHPROCINDB (@db_id);

     

    Thursday, January 16, 2020 11:18 AM
  • Sorry For finding bellow link .

    https://www.sqlservercentral.com/forums/topic/identifying-long-running-queries-sql-server

    The bellow reply is the solution

    Thursday, January 16, 2020 11:22 AM