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

Source: Internet
Author: User
Tags connection pooling

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.