Performance bottlenecks - ways to detect

Source: Praveen (AsthraSoft)

CPU

Symptoms: unexpectedly high CPU usage and low throughput

How to detect:

·         If % Processor Time counter > 80%

·         sys.dm_os_schedulers DMV - runnable_tasks_count is high

·         sys.dm_exec_query_stats DMV, statistics of currently cached batches/stored procedures, and total_worker_time, execution_count

Potential causes

·         Excessive compilation/recompilation

o    Goal: identify excessive recompilation and reduce it

·         Inefficient query plan

o    Goal: take steps to write queries with efficient plans

·         Intra-query parallelism

o    Goal: identify parallel queries and make sure they are efficient

Memory

Symptoms

·         Explicit memory-related errors (e.g. out of memory, timeout while waiting for memory resource).

·         I/O utilization is higher than usual

·         Overall system slow behavior

How to detect:

·         PerfMon

o    Process object: Working set, Private bytes

o    Memory object: Available KBytes, System Cache Resident Bytes, Committed bytes, Commit Limit

o    SQL Server: Buffer Manager object - Buffer cache hit ratio, Page life expectancy, Checkpoint pages/sec, Lazy writes/sec

IO

Symptoms

·         Slow response time, timeout error messages, I/O subsystem operates at its max capacity

Causes

·         Moving database pages between memory and disk

·         Log file operations

·         TempDB operations

How to detect

·         PerfMon: Physical Disk object

o    % Disk Time > 50%

o    Avg. Disk Queue Length > 2

o    Avg. Disc sec/Read or Avg. Disc sec/Write > 10-20 ms

o    Avg. Disk Reads/sec or Avg. Disk Writes/sec > 85% of disk capacity

• DMVs
• sys.dm_os_wait_stats for wait_type like ‘PAGEIOLATCH%’
• sys.dm_io_pendion_io_requests with sys.dm_io_virtual_file_stats
• sys.dm_exec_query_stats: *_reads, *_writes columns

TempDB

Symptoms

·         Running out of TempDB space

·         Bottleneck in system tables due to excessive DDL operations

·         Allocation contention

How to detect

·         DMVs:

o    sys.dm_db_file_space_usage (user, internal objects and version store sizes)

o    sys.dm_tran_active_snapshot_database_transactions (longest running transaction  most row version space)

o    sys.dm_db_session_space_usage (accounted at the end of a task)

o    sys.dm_db_task_space_usage

·         PerfMon:

o    SQL Server: Transactions object

o    Version Generation/Cleanup rates