SQL Server Common Performance diagnostic statements

/* General server dynamic management objects include: dm_db_*: Database and Database object dm_exec_*: Execute user code and associated connection dm_os_*: Memory, lock, and schedule dm_tran_*: Transactions and isolation dm_io_*: input/output */-for networks and disks --run the following DMV query to view CPU, scheduler memory, and buffer pool information. Select Cpu_count, Hyperthread_ratio, Scheduler_count, physical_memory_in_bytes/1024/1024 as Physical_memo     RY_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_mbfrom sys.dm_os_sys_i NFO---High I/o overhead queries identifying most costly Queries by I/O SELECT TOP [Average IO] = (total_logical_reads + total_l ogical_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) FRO  M sys.dm_exec_query_stats qscross APPLY sys.dm_exec_sql_text (qs.sql_handle) as Qtorder by [Average IO] DESC;---high CPU overhead queries    Identifying most costly Queries by CPU SELECT TOP [Average CPU used] = Total_worker_time/qs.execution_count , [total CPU used] = Total_worker_time, [execution count] = Qs.execution_count, [individual Query] = SUBSTRING (q T.TEXT,QS.STATEMENT_START_OFFSET/2, (case is 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 Q Uery] = qt.text, DatabaseName = db_name (qt.dbid) from sys.dm_exec_query_stats qscross APPLY sys.dm_exec_sql_text (qs.sql  _handle) as Qtorder by [Average CPU used] DESC;---expensive missing index cost of Missing Indexes SELECT TOP Ten [total cost] = ROUND (Avg_total_user_cosT * avg_user_impact * (User_seeks + User_scans), 0), avg_user_impact, TableName = statement, [Equa Lityusage] = Equality_columns, [inequalityusage] = Inequality_columns, [Include cloumns] = Included_colum Nsfrom sys.dm_db_missing_index_groups G INNER JOIN sys.dm_db_missing_index_group_stats s on S.group_handl  E = G.index_group_handle INNER JOIN sys.dm_db_missing_index_details d on d.index_handle = G.index_handleorder by [Total Cost] DESC;---Most frequently executed query identifying Queries that execute most Often SELECT TOP [execution count] = Execution_count,[individua L Query] = SUBSTRING (QT.TEXT,QS.STATEMENT_START_OFFSET/2, (case when qs.statement_end_offset =-1 TH EN LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2 ELSE Qs.statement_end_offset End-qs.statement_start_offset)/2), [Pa Rent Query] = Qt.text,databasename = Db_name (qt.dbid) from sys.dm_exec_query_stats qscross APPLY sys.dm_exec_sql_text ( Qs.sql_handle)As Qtorder by [execution Count] DESC;---a repeatedly compiled query (plan_generation_num indicates the number of times that the query has been recompiled) select top Sql_text.text, Sql_handle, Plan_generation_num, Execution_count, dbid, Objectid from Sys.dm_exec_query_stats a C Ross Apply Sys.dm_exec_sql_text (sql_handle) as Sql_textwhere plan_generation_num > 1order by plan_generation_num DESC---Server waits for SQL Query Records causes of the wait times SELECT TOP [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.D M_os_wait_statswhere wait_type not like '%sleep% ' ORDER by Wait_time_ms DESC;---Read and write identifying the most Reads and writes        SELECT TOP [total Reads] = SUM (total_logical_reads), [execution count] = SUM (qs.execution_count) , DatabaseName = db_name (qt.dbid) from sys.dm_exec_query_stats qscross APPLY sys.dm_exec_sql_text (Qs.sql_handle) as Qtgroup by Db_name (qt.dbid) ORDER by [TOtal Reads] DESC;        SELECT TOP [total writes] = SUM (total_logical_writes), [execution count] = SUM (qs.execution_count) , DatabaseName = db_name (qt.dbid) from sys.dm_exec_query_stats qscross APPLY sys.dm_exec_sql_text (Qs.sql_handle) as Qtgroup by Db_name (qt.dbid) Order by [Total writes] DESC;---Run the following DMV query to find I/O latch wait statistics. Select Wait_type, Waiting_tasks_count, Wait_time_ms, Signal_wait_time_ms, Wait_time_ms/waiting_tasks_countfrom Sys.dm_os_wait_stats where wait_type like ' pageiolatch% ' and Waiting_tasks_count > 0order by wait_type---View blocking information for the database s Elect * from sysprocesses a where a.program_name = '. Net SqlClient Data Provider ' and blocked! = 0---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_tr Ansport, Dc.client_net_address, Ds.host_name, ds.program_name, Case ds.status when ' sleeping ' Then ' sleep-not currently running any requests ' when 'Running ' Then ' 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 when 0 Then ' not specified ' 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--Performs the connection, most Query information near execution cross apply sys.dm_exec_sql_text (dc.most_recent_sql_handle) dtjoin sys.dm_exec_sessions ds on dc.session_id= Ds.session_idwhere Ds.login_name= ' LCGS609999 '--where ds.program_name = '. Net SqlClient Data Provider ' ORDER by Dt.text--kill 53;--Check parsing deadlock information 1, using SQL Profiler captures dead chain and Figure 2, trace Analysis SQL log DBCC TRACEON (1204, 3605, 1) godbcc tracestatus ( -1) go reference: http://support.microsoft.com/  zh-cn/kb/832524--Check analyze blocking information while 1=1begin 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 ' endgocmd execute command: OSQL-E-sserver-icheckblk.sql-ocheckblk.out-w2000 reference:/http support.microsoft.com/zh-cn/kb/271509 uncover hidden data and optimize application performance https://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx

　　

SQL Server Common Performance diagnostic statements