SQL Server performance Optimization manual performance collection dynamic management view (iii)

dynamic management Views:

Specific instance statements:

--on the basic situation of statement execution
SELECT * FROM Sys.dm_exec_query_stats

--dynamic management functions need to provide parameters
Select Top Ten Execution_count,total_worker_time,t.text,total_worker_time/execution_count
As Avgcpu from Sys.dm_exec_query_stats as S
Cross apply Sys.dm_exec_sql_text (S.sql_handle) as T
ORDER BY avgcpu Desc

--Number of tasks currently waiting for CPU to execute
Select Runnable_tasks_count from Sys.dm_os_schedulers where runnable_tasks_count>0

--memory-related dynamic management views
SELECT * FROM Sys.dm_os_memory_cache_clock_hands where rounds_count>0
and removed_all_rounds_count>0

If cross APPLY is specified, the rowset expression on the right returns an empty rowset for that row without generating any rows to the left of the row set.

--Evaluate which statements wasted SQL performance SELECT * from sys.dm_exec_query_stats--time required divided by number of executions select top Total_elapsed_time/execution_count as Avgtotal,t.text  from sys.dm_exec_query_stats as s--information about statements cross apply Sys.dm_exec_sql_text (S.sql_handle) as Torder by avgtotal Desc

--Evaluation of database IO SELECT * FROM sys.dm_io_pending_io_requests  --Request view of suspend IO  select * from Sys.dm_io_virtual_file_ Stats ((select db_id (' AdventureWorks2014 '), null)  --function   returns I/O statistics for data and log files--The description file is a pending select S.file_id,r.io_ Pending from Sys.dm_io_pending_io_requests as R inner joinsys.dm_io_virtual_file_stats ((Select DB_ID (' AdventureWorks2014 ')), null) as S on R.io_handle=s.file_handle

Create a table:

Create DATABASE  Hrdb go use  hrdb go  CREATE table T1 (C1 int,c2 int) go insert T1 VALUES (

Starting things without shutting down causes thread blocking

BEGIN TRAN Update T1 set c1=2 where C1=1--does not end

Another user to query the table

--Statement is blocked select * from t1

To view system blocking conditions

--View System blocking conditions select * from Master. sysprocesses SELECT distinct ' PID ' =str (s.spid,4), ' Blockid ' =str (s.blocked,4), ' Status ' = case s.status when ' sleeping ' th En ' Running ' when ' runnalbe ' and ' complete  ' when ' suspended ' and ' Blocked ' End,t.text from master. sysprocesses as S cross apply Sys.dm_exec_sql_text (S.sql_handle) as T where s.dbid= (select db_id (' Hrdb '))

To commit a thing to:

Commit    SELECT * from t1

Running the above statement again results in the following:

SQL Server performance Optimization manual performance collection dynamic management view (iii)