[reprint] called the general SQL Server Performance diagnostic statements commonly used. --Save your study for reference

CopyFrom Https://www.cnblogs.com/zhaoguan_wang

/*
General server dynamic management objects include:
Dm_db_*: Database and Database objects
dm_exec_*: Executing user code and associated connections
dm_os_*: Memory, lock, and schedule
Dm_tran_*: Transactions and isolation
dm_io_*: Network and disk input/output
*/

---Run the following DMV query to view the CPU, scheduler memory, and buffer pool information.
Select
Cpu_count,
Hyperthread_ratio,
Scheduler_count,
physical_memory_in_bytes/1024/1024 as PHYSICAL_MEMORY_MB,
virtual_memory_in_bytes/1024/1024 as VIRTUAL_MEMORY_MB,
bpool_committed * 8/1024 as BPOOL_COMMITTED_MB,
Bpool_commit_target * 8/1024 as BPOOL_TARGET_MB,
Bpool_visible * 8/1024 as BPOOL_VISIBLE_MB
From Sys.dm_os_sys_info

Queries that---High I/o overhead identifying most costly Queries by I/O
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes)/Qs.execution_count
, [total IO] = (total_logical_reads + total_logical_writes)
, [execution Count] = Qs.execution_count
, [individual Query] = SUBSTRING (QT.TEXT,QS.STATEMENT_START_OFFSET/2,
(case when qs.statement_end_offset =-1
Then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2
ELSE qs.statement_end_offset End-qs.statement_start_offset)/2)
, [Parent Query] = Qt.text
, DatabaseName = db_name (qt.dbid)
From Sys.dm_exec_query_stats QS
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
ORDER by [Average IO] DESC;

Queries---High CPU overhead identifying most costly Queries by CPU
SELECT TOP 10
[Average CPU used] = Total_worker_time/qs.execution_count
, [total CPU used] = Total_worker_time
, [execution Count] = Qs.execution_count
, [individual Query] = SUBSTRING (QT.TEXT,QS.STATEMENT_START_OFFSET/2,
(case when qs.statement_end_offset =-1
Then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2
ELSE qs.statement_end_offset End-qs.statement_start_offset)/2)
, [Parent Query] = Qt.text
, DatabaseName = db_name (qt.dbid)
From Sys.dm_exec_query_stats QS
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
ORDER by [Average CPU used] DESC;

---High overhead missing index cost of Missing Indexes
SELECT TOP 10
[Total Cost] = ROUND (Avg_total_user_cost * avg_user_impact * (User_seeks + User_scans), 0)
, Avg_user_impact
, TableName = statement
, [equalityusage] = Equality_columns
, [inequalityusage] = Inequality_columns
, [Include cloumns] = Included_columns
From Sys.dm_db_missing_index_groups G
INNER JOIN sys.dm_db_missing_index_group_stats S
On s.group_handle = G.index_group_handle
INNER JOIN sys.dm_db_missing_index_details D
On d.index_handle = G.index_handle
ORDER by [Total cost] DESC;

---Most frequently executed queries identifying Queries that execute most Often
SELECT TOP 10
[Execution Count] = Execution_count
, [individual Query] = SUBSTRING (QT.TEXT,QS.STATEMENT_START_OFFSET/2,
(case when qs.statement_end_offset =-1
Then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2
ELSE qs.statement_end_offset End-qs.statement_start_offset)/2)
, [Parent Query] = Qt.text
, DatabaseName = db_name (qt.dbid)
From Sys.dm_exec_query_stats QS
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
ORDER by [execution Count] DESC;

---A query that is compiled repeatedly (plan_generation_num indicates the number of times that the query has been recompiled)
Select Top 25
Sql_text.text,
Sql_handle,
Plan_generation_num,
Execution_count,
dbid
Objectid
From Sys.dm_exec_query_stats a
Cross apply Sys.dm_exec_sql_text (sql_handle) as Sql_text
where Plan_generation_num > 1
ORDER BY plan_generation_num Desc

Why---Server waits SQL Query Records causes of the wait times
SELECT TOP 10
[Wait Type] = Wait_type,
[Wait time (s)] = wait_time_ms/1000,
[% waiting] = CONVERT (DECIMAL (12,2), Wait_time_ms * 100.0
/SUM (Wait_time_ms) over ())
From Sys.dm_os_wait_stats
WHERE wait_type not like '%sleep% '
ORDER by Wait_time_ms DESC;

---Read and write identifying the most Reads and writes
SELECT TOP 10
[Total Reads] = SUM (total_logical_reads)
, [execution Count] = SUM (qs.execution_count)
, DatabaseName = db_name (qt.dbid)
From Sys.dm_exec_query_stats QS
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
GROUP by Db_name (qt.dbid)
ORDER by [Total Reads] DESC;

SELECT TOP 10
[Total Writes] = SUM (total_logical_writes)
, [execution Count] = SUM (qs.execution_count)
, DatabaseName = db_name (qt.dbid)
From Sys.dm_exec_query_stats QS
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
GROUP by Db_name (qt.dbid)
ORDER by [Total writes] DESC;

---Run the following DMV query to find the I/O latch wait statistics.
Select Wait_type, Waiting_tasks_count, Wait_time_ms, Signal_wait_time_ms, Wait_time_ms/waiting_tasks_count
From Sys.dm_os_wait_stats
where wait_type like ' pageiolatch% ' and waiting_tasks_count > 0
ORDER BY Wait_type

--View SQL blocking information
With TMP as (
SELECT * FROM Master. sysprocesses t where t.blocked! = 0
UNION ALL
Select b.* from Master. sysprocesses b
Join tmp t on b.spid = t.blocked
)
Select T.spid, t.blocked, T.status, T.lastwaittype, T.waitresource, T.waittime
, Db_name (t.dbid) DbName, T.login_time, T.loginame, T.program_name, Dc.text
From (the select SPID from the TMP GROUP by SPID) s
Join Master: sysprocesses T on s.spid = T.spid
Cross apply Master.sys.dm_exec_sql_text (T.sql_handle) DC

--kill 53;

--View the status of all sessions, wait types, and currently executing SQL scripts
Select T.spid, T.kpid, t.blocked, T.status, T.lastwaittype, T.waitresource, T.waittime
, Db_name (t.dbid) DbName, T.last_batch, T.loginame, T.program_name, T.hostname, t.hostprocess
, T.cmd, T.stmt_start, T.stmt_end, t.request_id, Dc.text
From Master.sys.sysprocesses t
Outer Apply Master.sys.dm_exec_sql_text (t.sql_handle) DC
where T.spid >= 50

Select S.spid, S.kpid, s.blocked, S.hostname, s.hostprocess, S.program_name, S.loginame
, S.status, S.lastwaittype, S.waitresource, S.waittime
, t.transaction_id, T.name, T.transaction_begin_time, Dc.text
From Sys.sysprocesses S
Join Sys.dm_tran_session_transactions st on s.spid = st.session_id
Join Sys.dm_tran_active_transactions t on st.transaction_id = t.transaction_id
Outer Apply Master.sys.dm_exec_sql_text (s.sql_handle) DC

---supplement, view the lock resources currently held and requested by all sessions (choose to perform in a specific business library, test simulations, recommend changing the isolation level to repeatable Read)

Set TRANSACTION ISOLATION LEVEL repeatable READ

Select l.request_session_id,
L.resource_type,
L.resource_subtype,
L.request_status,
L.request_mode,
L.resource_description,
Db_name (l.resource_database_id) as DbName,
Case L.resource_type
When the ' database ' then db_name (l.resource_database_id)
When the ' object ' then object_name (l.resource_associated_entity_id)
else object_name (p.object_id)
End as Obj_name,
P.INDEX_ID,
L.request_lifetime
From sys.dm_tran_locks l
Left JOIN sys.partitions p on l.resource_associated_entity_id = p.hobt_id
Order by l.request_session_id, L.resource_type

---View the execution statements for the found active transaction for all sessions
Select dc.session_id,
Ds.login_name,
Ds.login_time,
Dc.connect_time,
Dc.net_transport,
Dc.client_net_address,
Ds.host_name,
Ds.program_name,
Case Ds.status if ' sleeping ' then ' sleep-no request is currently running '
When ' running ' is running-one or more requests are currently running
When ' dormancy ' then ' Hibernate-session is reset due to connection pooling and is now in pre-logon state '
When ' pre-connected ' then ' pre-connect-session in Resource Governor classifier '
End as status,
Ds.cpu_time as Cpu_time_ms,
Ds.memory_usage*8 as MEMORY_KB,
Ds.total_elapsed_time as Total_elapsed_time_ms,
Case Ds.transaction_isolation_level If 0 then ' unspecified '
When 1 Then ' uncommitted read '
When 2 Then ' committed read '
When 3 Then ' repeatable '
When 4 Then ' serializable '
When 5 Then ' snapshot '
End ' session's transaction isolation level ',
Dt.text
From Sys.dm_exec_connections DC--perform connection, most recently executed query information
Cross apply sys.dm_exec_sql_text (dc.most_recent_sql_handle) DT
Join Sys.dm_exec_sessions DS on dc.session_id=ds.session_id
where Ds.login_name= ' LCGS609999 '
--where ds.program_name = '. Net SqlClient Data Provider '
ORDER by Dt.text
Copy Code

Copy Code
--Clears all cached data in the buffer pool
DBCC dropcleanbuffers
GO
--Clears all cached execution plans in the buffer pool
DBCC Freeproccache
GO

--statistical information, implementation plan

SET STATISTICS IO on
SET STATISTICS time on
SET STATISTICS profile on

--Check the deadlock information for analysis

1. Use SQL Profiler to capture dead chains and graphs
2, Trace analysis SQL Errorlog log

DBCC TRACEON (1204, 3605,-1)
Go
DBCC TRACESTATUS (-1)
Go

Resources:
http://support.microsoft.com/zh-cn/kb/832524

--Check for blocking information such as analysis latches

While 1=1
BEGIN
EXEC MASTER.DBO.SP_BLOCKER_PSS80--or sp_blocker_pss08
--Or for fast mode
--EXEC MASTER.DBO.SP_BLOCKER_PSS80 @fast =1
--Or for latch mode
--EXEC MASTER.DBO.SP_BLOCKER_PSS80 @latch =1
WAITFOR DELAY ' 00:00:15 '
END
GO

CMD execution command:
OSQL-E-sserver-icheckblk.sql-ocheckblk.out-w2000

Resources:
http://support.microsoft.com/zh-cn/kb/271509

Uncover hidden data and optimize application performance
Https://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx

[reprint] called the general SQL Server Performance diagnostic statements commonly used. --Save your study for reference