_Way to Find Slow Queries in SQL Server

Ankit Aggarwal Ankit Aggarwal

Ankit Aggarwal

Oracle Database Administrator with 14 years IT experience

发布日期: 2019年6月28日
+ 关注

Way to Find Slow Queries in SQL Server

 

Find Slow Queries With SQL DMVs

One of the great features of SQL Server is all of the dynamic management views(DMVs) that are built into it. There are dozens of them and they can provide a wealth of information about a wide range of topics.

There are several DMVs that provide data about query stats, execution plans, recent queries and much more. These can be used together to provide some amazing insights.

For example, this query below can be used to find the queries that use the most reads, writes, worker time (CPU), etc.

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

Pros: Always available basic rollup statistics.

Cons: Doesn’t tell you what is calling the queries. Can’t visualize when the queries are being called over time.

要查看或添加评论，请登录

Ankit Aggarwal的更多文章

  • How to check RAC database status
    2020年7月15日

    How to check RAC database status

    simply answer is Oracle provides the crs and srvctl command utility for checking on the external status of RAC…

  • Difference between the Checkpoint and SCN
    2020年7月6日

    Difference between the Checkpoint and SCN

    Checkpoint ?Checkpoint in database is used to reduce the amount of the time for recovery. It is a background process in…

  • The difference between Cloning and Refreshing
    2020年7月4日

    The difference between Cloning and Refreshing

    The difference between Cloning and Refreshing is that cloning process includes Oracle Home + database Clone; where as…

  • Oracle Histograms
    2020年7月3日

    Oracle Histograms

    Gather stats for column level is called histogram. A histogram is a special type of column statistic that provides more…

    1 条评论
  • Oracle Memory Structure INSIDEs
    2020年7月2日

    Oracle Memory Structure INSIDEs

    ***This is my pick-up for oracle memory. thanks in advance pls share your tips on Oracle Memory Structure insides*** 1.

  • Interview inception @lockdown4 India:
    2020年5月24日

    Interview inception @lockdown4 India:

    - Don't illustrate something nearby, higher up concept to sound like an expert or related to the question Simply say I…

  • Lockdown .0 INDIA & logswitch per hour...cheers
    2020年5月17日

    Lockdown .0 INDIA & logswitch per hour...cheers

    5 log switches per hour (maximum) & 3 Log switches per hour (ideal),recommended by oracle In a production environment…

  • Lockdown 3.0 India & Is it possible to use OLR without OCR in RAC?
    2020年5月9日

    Lockdown 3.0 India & Is it possible to use OLR without OCR in RAC?

    Answer is No, interesting and thought provoking This is introduced in Oracle 11gR2 which will have all the resource…

  • Lockdown in INDIA & unlocking Oracle OCR & OLR meanwhile
    2020年5月4日

    Lockdown in INDIA & unlocking Oracle OCR & OLR meanwhile

    ORACLE LOCAL REGISTRY(OLR) contains node-specific information required by OHASD . Every node has its own dedicated OLR…

  • introduction : compression in oracle
    2020年3月1日

    introduction : compression in oracle

    Apart from reduced space consumption, the compressed data takes less time to go across the network, uses less space for…

社区洞察

其他会员也浏览了