SQL Server view tables, stored procedures, time-consuming queries, current processes, expensive statements

--View the statements of tables in the database select S2.dbid, db_name (s2.dbid) as [database name],--s1.sql_handle, (select TOP 1 SUBSTRING (S2.text, STATEMENT_START_OFFSET/2 + 1, (case when Statement_end                                               _offset = 1 Then (LEN (CONVERT (NVARCHAR (MAX), S2.text))                                   * 2) ELSE Statement_end_offset END)-Statement_start_offset)/2 + 1) As [statement], execution_count as [number of executions], last_executio N_time as [time of last execution of plan], total_worker_time as [total CPU time (microseconds) used since compilation), last_worker_time as [last execution plan used CPU time (microseconds)], min_worker_time as [Minimum CPU time (microseconds) elapsed during a single execution), max_worker_time as [Maximum CPU time elapsed during a single execution (microseconds)]         , total_logical_reads as [total logic read], last_logical_reads as [last logical read], min_logical_reads as [minimum logical read], Max_logical_reads As [maximum logic read], total_logical_writes as [total logic write], last_logical_writes as [last logical write], min_logical_writes As [minimum logic write], max_logical_writes as [maximum logic write] from sys.dm_exec_query_stats as S1 cross APPLY sys.dm_exec_ Sql_text (sql_handle) as S2 WHERE S2.objectid is NULL ORDER by Last_worker_time DESC--View statements executed by the stored procedure SELECT db_ Name (ISNULL (eps.database_id, ')) [database name]--isnull (Dbs.name, ") as DatabaseName, object_name (eps.object_id, E  ps.database_id) [Stored procedure name]--as ObjectName, Eps.cached_time [added to cache time]--as Cachedtime, Eps.last_elapsed_time ' Recent execution time (microseconds) '--as lastelapsedtime, Eps.last_worker_time ' CPU time (in microseconds) ' of the last execution of the stored procedure, Eps.execution_count [since the last compilation        Number of executions]--as executioncount, Eps.total_worker_time/eps.execution_count [average amount of CPU time spent per execution (microseconds)]--as Avgworkertime , Eps.total_elapsed_time/eps.execution_count [Average time spent per execution (microseconds)]--as Avgelapsedtime, (Eps.total_logical_reads + Eps.total_logical_writes)/Eps.execution_count as Avglogicalio, b.text [Stored procedure content] from Sys.dm_exec_procedure_stats as EPS cross APPL    Y sys.dm_exec_sql_text (eps.sql_handle) b ORDER by Eps.last_elapsed_time DESC;  --Show time-consuming queries DECLARE @MinExecutions int; SET @MinExecutions = 5 SELECT eqs.total_worker_time as Totalworkertime, Eqs.total_logical_reads + eqs.total_log Ical_writes as Totallogicalio, Eqs.execution_count as execnt, eqs.last_execution_time as Lastusage, E  Qs.total_worker_time/eqs.execution_count as Avgcputimemis, (eqs.total_logical_reads + EQS.total_logical_writes)/                  Eqs.execution_count as Avglogicalio, db.name as DatabaseName, SUBSTRING (est.text , 1 + EQS.STATEMENT_START_OFFSET/2, (case when eqs.statement_end_offset =-1 TH                    EN LEN (convert (nvarchar (max), Est.text)) * 2 ELSE Eqs.statement_end_offset End -Eqs.statement_start_offSet)/2) as SQLStatement--Optional with Query plan; Remove comment to show, and then the query takes!!        Much longer time!! --, EQP. [Query_plan] As [Queryplan] from sys.dm_exec_query_stats as EQS cross APPLY sys.dm_exec_sql_text (eqs.sql_handle) as EST CR OSS APPLY sys.dm_exec_query_plan (eqs.plan_handle) as EQP left joins sys.databases as DB on est.dbid = Db.da tabase_id WHERE eqs.execution_count > @MinExecutions and Eqs.last_execution_time > DATEDIFF (MONTH,-1, GETDATE ()) ORDER by Avglogicalio Desc,avgcputimemis DESC-current process and its statements: SELECT Pro.loginame as LoginName, db.name as D Atabasename, PRO. [Status] as Processstatus, pro.cmd as Command, Pro.last_batch as Lastbatch, pro.cpu as CPU, PR O.physical_io as Physicalio, ses.row_count as [RowCount], STM. [Text] As SQLStatement from sys.sysprocesses as PRO INNER JOIN sys.databases as DB on pro.dbid = db.database_id INNER JOIN sys.dm_exec_sessions as SES on pro.spid = ses.session_id Cross APPLY sys.d M_exec_sql_text (Pro.sql_handle) as STM WHERE pro.spid >=-Exclude system processes ORDER by pro.physical_i o desc, pro.cpu desc; --5, cost-heavy query: SELECT SS. Sum_execution_count, T.text, SS. Sum_total_elapsed_time, Ss.sum_total_worker_time, Ss.sum_total_logical_reads, Ss.sum_total_logi                     Cal_writes from (SELECT s.plan_handle, SUM (s.execution_count) Sum_execution_count, SUM (s.total_elapsed_time) sum_total_elapsed_time, sum (s.total_worker_time) Sum_total_worker _time, sum (s.total_logical_reads) sum_total_logical_reads, sum (s.total_logical_w Rites) sum_total_logical_writes from Sys.dm_exec_query_stats s GROUP by S.plan_handle) As SS Cross APPLY sys.dm_exec_sql_text (ss.plan_handle) T ORDER by Sum_total_logical_reads DESC  

SQL Server view tables, stored procedures, time-consuming queries, current processes, expensive statements