SQL several statements to view performance

1. Find the SQL syntax currently executed by SQL Server and show the resource situation:

SELECTS2.dbid,db_name(s2.dbid) as [Database name] ,         --S1.sql_handle,(SELECT TOP 1                     SUBSTRING(S2.text, Statement_start_offset/ 2 + 1,                               ( (  Case  whenStatement_end_offset= -1                                         Then(LEN(CONVERT(NVARCHAR(MAX), S2.text))                                               * 2 )                                        ELSEStatement_end_offsetEND)-Statement_start_offset)/ 2 + 1)         )  as [Statement], Execution_count as [Number of executions], Last_execution_time as [time when execution of the plan was last started], Total_worker_time as [Total CPU time spent executing since compilation (microseconds)], Last_worker_time as [CPU time spent in last execution of the plan (microseconds)], Min_worker_time as [minimum CPU time (microseconds) elapsed during a single execution], Max_worker_time as [Maximum CPU time (microseconds) that was consumed during a single execution], Total_logical_reads as [Total Logical Read], Last_logical_reads as [last logical Read], Min_logical_reads as [minimum logical Read], Max_logical_reads as [Maximum logical Read], Total_logical_writes as [Total Logical Write], Last_logical_writes as [Last Logical Write], Min_logical_writes as [Minimum logical Write], Max_logical_writes as [Maximum Logical Write]  fromSys.dm_exec_query_stats asS1 CrossAPPLY sys.dm_exec_sql_text (sql_handle) asS2WHERES2.objectid is NULL ORDER  byLast_worker_timeDESC

2, display time-consuming query:

--List Expensive queries DECLARE @MinExecutions int; SET @MinExecutions = 5     SELECTEqs.total_worker_time asTotalworkertime, Eqs.total_logical_reads+Eqs.total_logical_writes asTotallogicalio, Eqs.execution_count asexecnt, Eqs.last_execution_time asLastusage, Eqs.total_worker_time/Eqs.execution_count asAvgcputimemis, (eqs.total_logical_reads+Eqs.total_logical_writes)/Eqs.execution_count asAvglogicalio, Db.name asDatabaseName,SUBSTRING(EST.text                  ,1 +Eqs.statement_start_offset/ 2                  ,( Case  whenEqs.statement_end_offset= -1                           Then LEN(Convert(nvarchar(Max), EST.text))* 2                          ELSEEqs.statement_end_offsetEND                    -Eqs.statement_start_offset)/ 2                  )  asSQLStatement--Optional with query plan; Remove comment to show, and then the Query takes!! Much longer time!!        --, EQP. [Query_plan] As [Queryplan]  fromSys.dm_exec_query_stats asEQS CrossAPPLY sys.dm_exec_sql_text (Eqs.sql_handle) asEST CrossAPPLY sys.dm_exec_query_plan (Eqs.plan_handle) asEQP Left JOINsys.databases asDB onEst.dbid=db.database_idWHEREEqs.execution_count> @MinExecutions         andEqs.last_execution_time> DATEDIFF(MONTH,-1,GETDATE())  ORDER  byAvglogicalioDESC, AvgcputimemisDESC

3. Current process and its statements:

--Current processes and their SQL statements SELECTPro.loginame asLoginName, Db.name asDatabaseName, PRO.[Status]  asProcessstatus, Pro.cmd asCommand, Pro.last_batch. asLastbatch, Pro.cpu asCpu, Pro.physical_io asPhysicalio, Ses.row_count as [RowCount], STM.[text]  asSQLStatement fromSys.sysprocesses asPROINNER JOINsys.databases asDB onPro.dbid=db.database_idINNER JOINSys.dm_exec_sessions asSES onPro.spid=ses.session_id CrossAPPLY sys.dm_exec_sql_text (Pro.sql_handle) asSTMWHEREPro.spid>=  -  --Exclude System Processes ORDER  byPro.physical_ioDESC, Pro.cpuDESC;

4. Execution of stored procedures:

--Stored Procedure Execution Statistics SELECT ISNULL(Dbs.name,"') asDatabaseName,object_name(EPS.object_id, eps.database_id) asObjectName, Eps.cached_time asCachedtime, Eps.last_elapsed_time asLastelapsedtime, Eps.execution_count asExecutioncount, Eps.total_worker_time/Eps.execution_count asAvgworkertime, Eps.total_elapsed_time/Eps.execution_count asAvgelapsedtime, (eps.total_logical_reads+eps.total_logical_writes)/Eps.execution_count asAvglogicalio fromSys.dm_exec_procedure_stats asEPS Left JOINsys.databases asDBS oneps.database_id=dbs.database_idORDER  byAvgworkertimeDESC;

5, the query of large cost:

/*queries with higher overhead*/ SELECTSS. 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(SELECTS.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_writes) sum_total_logical_writes fromsys.dm_exec_query_stats sGROUP  bys.plan_handle) asSS CrossAPPLY sys.dm_exec_sql_text (ss.plan_handle) TORDER  bySum_total_logical_readsDESC    

SQL several statements to view performance