EN: Long running queries – Sql Server – blogMigration

Oftenly relational database is the bottleneck or an IT project or an administrative burden. Diagnosing the root cause of a problem may take some time – especially if the number of integrations, stored procesures is significant. Each integration, stored procedure called numerous times takes a quantum of cpu time, a portion of IO, some RAM memory and bandwidth. If the CPU or IO hits the limit, the number of distinct procedures / queries is significant and there are licensing limitations then a developer or administrator is forced to dig into details.

Please find below an example of a query for diagnosing such issues under Sql Server.

Shortly: when you fetch data from database a connection and session are created. Within those run transactions, queries. Transactions constitute of multiple statements. Statements constitute of multiple tasks. Each task is assigned to a logical core or fiber. Running the query below may list currently established connections, sessions, queries, tasks (low level) that burden the database. Analyzing cpu time, number of reads, tasks may lead to disclosing bottlenecks in your instance of sqlServer. Alternatively you may create a simple monitoring script out of it.

SELECT getdate() dt, /* w.session_id, w.wait_duration_ms, w.wait_type, */
                              c.connection_id,
                              s.session_id,
                              DB_NAME(r.database_id) AS DatabaseName,
                              s.nt_domain,
                              s.nt_user_name,
                              user_name(r.user_id) user_name,
                              s.host_name,
                              c.client_net_address,
                              s.host_process_id,
                              s.client_interface_name,
                              s.client_version,
                              s.cpu_time session_cpu_time,
                              s.memory_usage session_memory_usage,
                              s.logical_reads session_logical_reads,
                              s.reads session_reads,
                              s.writes session_writes,
                              r.blocking_session_id,
                              r.cpu_time,
                              r.granted_query_memory,
                              r.logical_reads,
                              r.open_resultset_count,
                              r.open_transaction_count,
                              r.nest_level,
                              r.percent_complete,
                              r.plan_handle,
                              r.query_hash,
                              r.query_plan_hash,
                              r.reads,
                              r.row_count,
                              r.status,
                              r.scheduler_id,
                              r.sql_handle,
                              r.wait_resource,
                              r.writes,
                              -- hh.objectid query_object_id,
                              hh.text,
                              t.context_switches_count,
                              -- t.parent_task_address,
                              t.pending_io_byte_average,
                              t.pending_io_byte_count,
                              t.pending_io_count,
                              t.task_state,
                              sc.cpu_id,
                              sc.current_tasks_count,
                              sc.runnable_tasks_count,
                              sc.current_workers_count,
                              sc.active_workers_count,
                              sc.work_queue_count,
                              sc.pending_disk_io_count,
                              sc.preemptive_switches_count
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
inner join sys.dm_exec_connections c on c.session_id = s.session_id
-- left join sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
-- where host_name like ‚%STE%’
inner join sys.dm_os_tasks t on r.request_id = t.request_id
left join sys.dm_os_schedulers sc on r.scheduler_id = sc.scheduler_id
outer apply sys.dm_exec_sql_text(r.sql_handle) hh
order by r.cpu_time desc

In brief: we list currently executed requests (queries), connections, sessions and cpu time, io associated with them. Next we list blocking sessions (transaction isolation level, blocking statements, blocked by column) and low level tasks – depicting the number of tasks being able to handle by cpu plus the number of tasks waiting to be executed due to context switches or varia.

Please note that if sessions are etablished from remote desktop services / terminal / rds / RDP then host_name is usually equal to AD login of user that is logged on via RDS to fat client – that is executing the query, otherwise it is host_name of the client.

Was it helpfull?