Long Running Queries is one of the most common issue in every clients.To troubleshoot Long running Queries we have to identify first.Run below query to find the Long Running Queries in SQL Server.
SELECT DISTINCT TOP 10 tq.TEXT AS QUERY , Db_name(dbid), lq.execution_count AS EXEC_CNT, lq.max_elapsed_time AS MAX_ELAPSED_TIME, ISNULL(lq.total_elapsed_time / NULLIF(lq.execution_count,0), 0) AS AVG_ELAPSED_TIME, lq.creation_time AS CREATION_TIME, ISNULL(lq.execution_count / NULLIF(DATEDIFF(s, lq.creation_time, GETDATE()),0), 0) AS EXEC_PER_SECOND, total_physical_reads AS AGG_PHYSICAL_READS FROM sys.dm_exec_query_stats lq CROSS APPLY sys.dm_exec_sql_text( lq.sql_handle ) tq ORDER BY lq.max_elapsed_time DESC |
Backup and Restore Interview Question and Answers
SQL Server installation interview Questions and Answers