View System LogsSELECT * FROM Sys.traces
View SQL Profile dump file
- First, use SQL profile to monitor the database and export it to TRC.
- Copy to the database server, EG.D:\SQLDATA\TRACE\201611290881.TRC
- The following statement in the SQL query looks
--select cpu,textdata,reads,writes
SELECT *
From:: fn_trace_gettable (' d:\sqldata\trace\201611290881.trc ', default)
WHERE SPID > 50
and StartTime > ' 2016-11-29 11:30:00.000 '
and CPU > 100ORDER by CPU desc--View trace file contents Select *from fn_trace_gettable (' C:\a.trc ', 0) where Hostname= ' Sit1_win_api ' ORDER by reads DESC
Perform statistical functionsOpen statistics function SET STATISTICS IO on execute specific SQL statement messages can be seen in the specific execution situation
View execution PlansOpen execution plan execution statement in Execution Plan tab view specific execution plan
--View the SQL and execution plan being executedselect S. [session_id], R.[start_time], DATEDIFF (S, R. Start_time, GETDATE ( ) as Elapsed_ms, R.[status] as RequestStatus, db_name (R. Database) _id) as DatabaseName, R.[wait_type], R.[wait_resource], &NB Sp R.[wait_time], R.[reads], R.[writes], R.[sql_handle], R.[logical_reads], S.[status] as Ses Sionstatus, S.[host_name], S.[original_login_name], S.[nt_user_name], S.[program_name], S.[client_interf Ace_name], c.[client_net_address], SUBSTRING (qt. Text, R. statemen T_start_offset/2, (case when r.statement_end_offset = -1  ; then LEN (NVARCHAR (MAX), Qt. Text) * 2 else R. Statement_end_ offset end-r. Statement_start_offset)/2) as Exe Cutingsql, qp.query_planfrom sys. dm_exec_requests r I Nner JOIN sys. Dm_exec_sessions s on r.session_id = s.session_id left JOIN sys. Dm_exec_connections C on c.session_id = s.session_id cross APPLY sys. Dm_exec_sql_text (R sql_handle) as qt cross APPLY sys. Dm_exec_query_plan (r. plan_handle) as Qporder by Elapsed_ms DESC
Perfmon monitors SQL Server locks
--View blocking
SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT
waits.wait_duration_ms/1000 as Waitinseconds
, Blocking. session_id as Blockingsessionid
, db_name (Blocked database_id) as DatabaseName
, Sess. Login_name as Blockinguser
, Sess. Host_name as Blockinglocation
, Blockingsql. Text as Blockingsql
, Blocked. session_id as Blockedsessionid
, blockedsess. Login_name as Blockeduser
, blockedsess. Host_name as Blockedlocation
, Blockedsql. Text as Blockedsql
, SUBSTRING (blockedsql. Text, (blockedreq. Statement_start_offset/2) + 1,
(case when blockedreq. statement_end_offset =-1
Then LEN (CONVERT (NVARCHAR (MAX), blockedsql. Text)) * 2
ELSE Blockedreq. statement_end_offset
End-blockedreq. Statement_start_offset)/2) + 1)
As [Blocked individual Query]
, Waits. Wait_type
From Sys. dm_exec_connections as Blocking
INNER JOIN sys. Dm_exec_requests as Blocked
On Blocking. session_id = Blocked. blocking_session_id
INNER JOIN sys. Dm_exec_sessions Sess
On Blocking. session_id = Sess. session_id
INNER JOIN sys. Dm_tran_session_transactions St
On Blocking. session_id = St. session_id
Left OUTER JOIN sys.dm_exec_requests er
On St. session_id = er. session_id
And ER. session_id is NULL
INNER JOIN sys. Dm_os_waiting_tasks as Waits
On Blocked. session_id = Waits. session_id
Cross APPLY sys. Dm_exec_sql_text (Blocking. Most_recent_sql_handle) as Blockingsql
INNER JOIN sys. Dm_exec_requests as Blockedreq
On Waits. session_id = blockedreq. session_id
INNER JOIN sys. Dm_exec_sessions as Blockedsess
On Waits. session_id = blockedsess. session_id
Cross APPLY sys. Dm_exec_sql_text (Blocked. sql_handle) as Blockedsql
ORDER by Waitinseconds
Performance analysis of common SQL Server
