SQL Server monitoring and optimization

I have recently received a training session to explain SQL Server optimization and make some reserves.

 

First, let's explain several terms:
Execution Plan: the code segment after SQL compilation. It is not binary code, but the Execution Code for SQL. It is called plan. The SQL statement may be compiled into multiple plans due to different parameter and branch execution.
Perfmon: extends cemonitor and performance analysis tools provided by the server system
Dmvs: Some system views provided by SQL Server for analyzing SQL Performance
DBCC: a console command of SQL Server that processes the database physically or logically.

SQL Server performance bottlenecks can be divided into three types:
1. CPU bottleneck
2. Memory bottleneck
3. Io read/write bottleneck

Next we will explain it separately.

CPU bottleneck:

I. recompile
Exec ('SQL ');
Withrecompiles;
Temporary table changes;
Processing: perfmon counters
Batch requests/sec batch processing per second
The number of SQL statements compiled per second by sqlcompilations/sec is different. Different plans are generated and used properly by the system.

Shouldn't be too many after a period of time
Sqlrecompilations/sec
Rationo recompile to batch requests shocould be very low

Query recompilation statements
Select top 25
Sqltext. Text, SQL _handle, plan_generation_num,
Execution_count, dbid, objectid
From SYS. dm_exec_query_stats
Crosapply SYS. dm_exec_ SQL _text (SQL _handle) as sqltext
Where plan_generation_num> 1
Order by plan_generation_num DESC

50 of the slowest statements in the query system
Select top 50
Sum (Qs. total_worker_time) as totalcputime,
Sum(qs.exe cution_count) as totalexecutioncount,
Count (*) as numberofstatements,
Qs. plan_handle
From SYS. dm_exec_query_stats Qs
Group by QS. plan_handle
Order by sum (Qs. total_worker_time) DESC

View Compilation Time
Select * From SYS. dm_exec_query_optimizer_info

Solution:
1. Avoid using the set statement in the stored procedure to set the database status (causing re-compilation)
2. When using a temporary table, use the table variable or use the keep plan
3. Avoid using select * From tabel whenever possible. If the schema changes, it will cause re-compilation.
4. Do not write DML and DDL together for interaction. This will cause schema changes and re-compilation.
5. Avoid using if to generate an execution plan for every condition.

Ii. Query
1. Connection query (debugging depends on the data volume of the two tables)

View the most time-consuming queries
Find queries use most cumulative CPU through
Dm_exec_query_stats
Look for CPU intensive operators through
Dm_exec_cached_plans

Iii. Parallel Processing

Perfmon: Process-% processor time -sqlservr.exe

Dmvs:
Find query plans that may run in parallel
Select P. *, Q. *, CP. plan_handle
From SYS. dm_exec_cached_plans CP
Crossapply SYS. dm_exec_query_plan (CP. plan_handle) P
Crossapply SYS. dm_exec_ SQL _text (CP. plan_handle) as Q
Where
CP. cacheobjtype = 'compledplan' and
P. query_plan.value ('clarenamespace

P = "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max (// P: relop/@ parallel) ', 'float')> 0

Only parallel query cocould use more CPU time thanthe elapsed time
Select Qs. SQL _handle, Qs. statement_start_offset,
Qs. statement_end_offset, Q. dbid, Q. objectid, Q. Number,
Q. encrypted, Q. Text
From SYS. dm_exec_query_stats Qs
Crossapply SYS. dm_exec_ SQL _text (Qs. plan_handle) as Q
Where Qs. total_worker_time> Qs. total_elapsed_time

SQL traces:
Showplans that have parallelism Operators
Selecteventclass, stmttext
From: fn_trace_gettable ('C: \ temp \ high_cpu_trace.trc', default)
Wherestmttext like '% parallelism %'
Parallel Query use more CPU time than theelapsed time
Selecteventclass, stmttext
From: fn_trace_gettable ('C: \ temp \ high_cpu_trace.trc', default)
Whereeventclass in (10, 12)
-- RPC: Completed, SQL: batchcompleted
Andcpu> duration/1000
-- CPU is in milliseconds, duration in microseconds

Processing:
1. Increase the number of CPUs
2. Optimize Query statements
3. Comparison between table-valued functions and CLR functions consumes proper CPU Optimization

Memory Optimization

Check whether the memory pressure comes from the internal or external method:
Use the task management tool under the performance tab, "availablephysical memory" <50 m available memory is less than 50 m
Check page file size
-Page files exceed 2 times of physical memory
-Use the task management tool. The commit change on the performance tab should not be greater than the physical memory.
Check whether other applications occupy too much memory.
-Do applications other than SQL Server occupy a large amount of memory, such as IE, to seize the SQL Memory?

Confirm internal memory pressure:

DBCC memorystatus

View memory allocation
Dmvs
Select Type, sum (multi_pages_kb)
From SYS. dm_ OS _memory_clerks
Where multi_pages_kb! = 0
Group by type

Troubleshooting process:
1. Whether your server is facing external memory pressure or internal memory pressure
External pressure: Adjust sqlserver's memory usage, set SQL attributes, expand memory usage, and kill external processes
Internal Pressure: DBCC or dmvs.
Error 701: insufficient memory, external pressure, memory expansion or application reduction
Error 802: bufferpool has no memory. If the buffer pool is released, all plans will be lost.
Error 8645: Waiting For memory allocation timeout during execution. Use a lot of temporary tables and a lot of concurrency. Locate the problem

2. collect performance data SQL server: buffermanager, SQL SERVER: Memory Manager
3. Verify the configuration parameters (sp_configure)
'Minmemory per query', 'min/MAX Server Memory ',
'Awenabled '-- large memory can be used more than 4 GB, 'lock pages in memory'
4. DBCC memorystatus

Io bottleneck

Check
1 perfmon
Disk queue avg. Disk queue length> 2 indicates waiting
Average read/write avg. Disk SEC/read? 0.12, avg. disksec/Write> 0.12
Disk time % disk Time> 50%
AVG. diskreads/sec> 85%, avg. Disk writes/sec> 85%
2 use RAID
RAID 0 -- I/OS per disk = (reads + writes)/number ofdisks
Maximum Performance Improvement
Raid 1 -- I/OS per disk = [reads + (2 * writes)]/2
RAID 5 -- I/OS per disk = [reads + (4 * writes)]/number of disks
Read performance tips: Write Performance is inferior to raid0
Raid 10--i/OS per disk = [reads + (2 * writes)]/number of disks

3 dmvs
-- Physical Io wait when reading and writingbuffer pages
Selectwait_type, waiting_tasks_count, wait_time_ms
From SYS. dm_ OS _wait_stats
Where wait_type like 'pageiolatch %'
Order by wait_type
-- Different lock wait times

-- Pending Io request
Selectdatabase_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address
From SYS. dm_io_virtual_file_stats (null, null) T1,
SYS. dm_io_pending_io_requestsas T2
Where t1.file _ HANDLE = t2.io _ HANDLE
-- If there is data, it indicates that there is a problem with Io.

Solution:
Optimize indexes through the disk index Wizard to reduce disk scans

Find the most wasteful query of Io Performance
Select top 5
(Total_logical_reads/execution_count) asavg_logical_reads,
(Total_logical_writes/execution_count) asavg_logical_writes,
(Total_physical_reads/execution_count) asavg_physical_reads,
Execution_count,
Statement_start_offset,
SQL _handle,
Plan_handle,
Sqltext. Text
From SYS. dm_exec_query_stats
Cross apply SYS. dm_exec_ SQL _text (SQL _handle) assqltext
Order by (total_logical_reads + total_logical_writes)/execution_count DESC