Long Running queries

-

How do I find a long running query with process ID, process name , login time, user , start time and duration?

Long running queries have been found in the plan cache. These may be ETL, reports, or other queries that should run long. Or it could be that someone is searching for all users whose names are LIKE '%%'. Either way, these queries bear investigating.

In some cases, this is the total clock time that the query took to execute and in others this is the total CPU time that the query took to execute. Queries with a high max_elapsed_time just take a lot of time to run – they could be slow single threaded queries. Queries with a high max_worker_time (CPU time) may be highly parallel queries.

CODE:

SELECT  creation_time
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

Slow Running Query Checklists:


Queries or updates that take longer than expected to execute can be caused by a variety of reasons. Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running. Slow-running queries can also be caused by problems with your physical database design.
  • There are a number of common reasons for slow-running queries and updates.
  • Slow network communication.
  • Inadequate memory in the server computer, or not enough memory available for SQL Server..
  • Lack of useful statistics .
  • Lack of useful indexes.
  • Lack of useful indexed views.
  • Lack of useful data striping.
  • Lack of useful partitioning.
  • Find if query is running slow because of Blockings
  • Check the load on the system, current connections.
     by executing this select query sys.dm_exec_requests
  • Verify if any Application or Database specific jobs are running or not.
  • Check disk space availability for that database and also for system databases.
    • sp_spaceused
    • dbcc sqlperf(logspace)
    • xp_fixeddrives

Comments

Post a Comment

Popular posts from this blog

AlwaysOn Overview

-
Image
OVERVIEW An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases. Secondary databases are not backups. Continue to back up your databases and their transaction logs on a regular basis. Each set of availability database is hosted by an availability replica. Two types of availability replicas exist: a single primary replica. which hosts the primary databases, and one to eight secondary replicas, each of which hosts a set of secondary databases and serves as a potential failover targets for the availability group. The primary replica makes the primary databases available for read-write connections from clients. Also, in a process known as data synchronization, which occurs at the database level. The primary replica sends transaction log records of each primary databa...
Read more

Database Developer vs Database Administrator

-
Image
Hi Geeks, Hope you're all doing good!! Have you ever asked someone or yourself to know who is a database developer and database administrator ? Or Have you ever been confused as to the difference between a DBA and a SQL Developer? Well, then you are not alone. There are number of blurred lines about exactly what is a DBA role, what is a SQL developer role. And thousands of people are still looking for the exact definitions. In this post, I will try my level best to clear all of your doubts about these two simple yet complicated things.  On a lighter note, The database administrator performs the day-to-day task(s) of maintaining the database environment to ensure its availability. Make sure it runs smoothly using database  tools to monitor, fix and maintain the database software, also the hardware on which it runs. On the other hand, the database developer designs and creates new ways of using a software  application. The database developer does this b...
Read more