32. Monitoring & Performance Analysis -- Finding Bottlenecks

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Dynamic Management Views (DMVs)
• Query execution statistics
• Identify slow queries
• CPU and IO usage
• Lock and blocking detection
• Index usage analysis
• Missing indexes
• Performance troubleshooting
• Best practices

Slow queries hurt users. Monitoring finds them. This article teaches identifying bottlenecks.

Why Monitoring Matters for Backend Developers

Performance issues need data:

Complaint: "App slow this morning"
    v
Without monitoring: Guess and hope
WITH monitoring: Query statistics show exact bottleneck
    v
Targeted fix: Add index, optimize specific query

Data drives decisions.

Dynamic Management Views (DMVs)

SQL Server provides DMVs for monitoring:

sys.dm_exec_query_stats       -- Query performance
sys.dm_exec_requests          -- Running queries
sys.dm_db_index_usage_stats   -- Index usage
sys.dm_db_missing_indexes     -- Missing indexes
sys.dm_os_waiting_tasks       -- Locks/blocks

Query these for insights.

Example 1: Identify Slow Queries

Find top 10 slowest queries:

SELECT TOP 10
    qs.total_elapsed_time / 1000000 AS TotalElapsedTimeMs,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgElapsedTimeMs,
    SUBSTRING(st.text, 1, 100) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_elapsed_time DESC;

Result (slowest queries):

TotalElapsedTimeMs | ExecutionCount | AvgElapsedTimeMs | QueryText
50000              | 100            | 500              | SELECT * FROM Student s ...
40000              | 200            | 200              | SELECT * FROM ExamResult ...
30000              | 50             | 600              | SELECT * FROM FeeAccount ...

AvgElapsedTimeMs > 100: Investigate. Add index or optimize.

Example 2: Query CPU and IO Usage

Identify resource hogs:

SELECT TOP 20
    SUBSTRING(st.text, 1, 80) AS QueryText,
    qs.total_worker_time / 1000000 AS TotalCpuMs,
    qs.total_physical_reads AS PhysicalReads,
    qs.total_logical_reads AS LogicalReads,
    qs.total_logical_writes AS LogicalWrites,
    qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Result (CPU hogs):

QueryText                          | TotalCpuMs | PhysicalReads | LogicalReads
SELECT * FROM ExamResult r JOIN ... | 80000      | 10000         | 50000

High CPU: Optimize query (add index, rewrite). High PhysicalReads: Add index (cache misses).

Example 3: Lock and Blocking Detection

Find blocked queries:

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.blocking_session_id,
    SUBSTRING(st.text, 1, 80) AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0;

Result (blocks):

SessionId | Status   | Command | BlockingSessionId | QueryText
52        | SLEEPING | UPDATE  | 0                 | (blocker)
55        | WAITING  | SELECT  | 52                | (blocked by 52)

SessionId 55 blocked by 52. Kill 52 or wait.

Example 4: Missing Index Analysis

Find indexes that would help:

SELECT 
    migs.user_seeks + migs.user_scans + migs.user_lookups AS TotalLookups,
    mid.equality_columns,
    mid.included_columns,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    'CREATE NONCLUSTERED INDEX IX_' + 
    REPLACE(REPLACE(mid.equality_columns, ', ', '_'), '[', '') +
    ' ON ' + mid.statement + ' (' + mid.equality_columns + ')' AS CreateStatement
FROM sys.dm_db_missing_indexes migs
INNER JOIN sys.dm_db_missing_indexes_details mid 
    ON migs.index_handle = mid.index_handle
WHERE database_id = DB_ID()
ORDER BY migs.user_seeks + migs.user_scans + migs.user_lookups DESC;

Result (missing):

TotalLookups | EqualityColumns | AvgTotalUserCost | AvgUserImpact
1000         | [StudentId]     | 0.5              | 0.9

Impact 0.9: High benefit. Create index.

Example 5: Index Usage Statistics

Check if indexes are used:

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalReads
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id 
    AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC;

Result (unused):

TableName | IndexName              | TotalReads
Student   | IX_Student_Name        | 0
Student   | IX_Student_ClassName   | 50000

IX_Student_Name never used. Drop it.

Example 6: Wait Statistics

Find what queries wait on:

SELECT TOP 10
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    avg_wait_time_ms = wait_time_ms / waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK', 'LOGMGR_QUEUE')
ORDER BY wait_time_ms DESC;

Result (waits):

WaitType          | WaitingTasksCount | WaitTimeMs | AvgWaitTimeMs
PAGEIOLATCH_SH    | 5000              | 50000      | 10
LOCK_M_X          | 1000              | 30000      | 30

PAGEIOLATCH: Disk I/O. Add index or cache. LOCK: Lock contention. Optimize queries.

Example 7: Running Queries (Real-time)

Monitor currently executing queries:

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time / 1000 AS CpuMs,
    r.total_elapsed_time / 1000 AS ElapsedMs,
    SUBSTRING(st.text, 1, 100) AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

Result (running now):

SessionId | Status    | CpuMs | ElapsedMs | QueryText
52        | RUNNING   | 5000  | 10000     | SELECT * FROM ExamResult ...
55        | RUNNING   | 2000  | 3000      | SELECT * FROM Student ...

Monitor live query performance.

Example 8: Database Size and Growth

Check database statistics:

SELECT 
    DB_NAME() AS DatabaseName,
    SUM(size) * 8.0 / 1024 AS SizeMB,
    SUM(CASE WHEN status & 64 = 64 THEN size ELSE 0 END) * 8.0 / 1024 AS UsedMB
FROM sys.database_files;

-- Result:
-- DatabaseName: SchoolDB
-- SizeMB: 5120 (5 GB)
-- UsedMB: 4096 (4 GB used)

Monitor growth. Plan capacity.

---

Common Monitoring Mistakes

Mistake 1: Only monitoring CPU (ignoring IO)

Wrong:

-- CPU at 20%, seems fine
-- But physical reads = 100K/sec (disk thrashing)

Result: Slow queries despite low CPU.

Fix: Monitor holistically:

-- CPU, logical reads, physical reads, locks
-- All contribute to performance

Mistake 2: Ignoring wait statistics

Wrong:

-- Query slow, CPU shows 50%
-- But wait: PAGEIOLATCH_SH (disk IO wait)
-- Problem not CPU, problem is missing index

Fix: Check waits first:

SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
-- Guides tuning (index? cache? lock?)

Mistake 3: Reacting after production crash

Wrong:

-- No monitoring setup
-- Customers report slow at 3 PM
-- Investigate after damage

Fix: Proactive monitoring:

-- Query statistics collected continuously
-- Alerts on performance degradation
-- Fix before users notice

Mistake 4: Misinterpreting execution plan

Wrong:

-- Plan shows Table Scan (100%), seems bad
-- But it's a 100-row table (fast anyway)

Result: Add unnecessary index.

Fix: Look at cost and rows:

-- Actual rows: 100 (small table, scan fine)
-- Actual rows: 1M (large table, scan bad)

Mistake 5: DMVs not cleared (old data accumulated)

Wrong:

-- Monitoring data from 90 days ago still counted
-- Statistics skewed by old workload

Fix: Clear periodically:

DBCC DROPCLEANBUFFERS;  -- Clear cache
DBCC SQLPERF('sys.dm_exec_query_stats', CLEAR);  -- Clear stats

---

Best Practices for Monitoring

1. Monitor continuously -- Not just on emergency
2. Track CPU, IO, locks -- All matter
3. Baseline normal behavior -- Detect anomalies
4. Alert on thresholds -- CPU > 80%, waits > 5s
5. Collect execution plans -- Save for analysis
6. Review missing indexes -- Implement high-impact
7. Drop unused indexes -- Free space, improve writes
8. Document bottlenecks -- Track patterns

---

🎯 Q1: What are Dynamic Management Views (DMVs)?

System views for monitoring:

sys.dm_exec_query_stats    -- Query performance
sys.dm_db_missing_indexes  -- Missing indexes
sys.dm_os_wait_stats       -- Wait events
sys.dm_exec_requests       -- Running queries

Query for insights into performance.

🎯 Q2: How do I find slow queries?

SELECT TOP 10 * FROM sys.dm_exec_query_stats
ORDER BY total_elapsed_time DESC;

AvgElapsedTime > 100ms: Investigate. Add index or rewrite query.

🎯 Q3: What's the difference between CPU and IO waits?

CPU: Query using CPU time (computation). IO: Query waiting for disk (PAGEIOLATCH, DISK_IO_THROTTLE).

High CPU: Optimize logic. High IO: Add index (cache hits).

🎯 Q4: How do I know if index helps?

Missing indexes DMV suggests:

AvgUserImpact: 0-1 (0 = no help, 1 = huge help)

Impact > 0.8: Create index. Impact < 0.2: Skip.

🎯 Q5: Should I drop all unused indexes?

Mostly yes, but check:

• Used by other queries?
• Part of foreign key?
• Recently unused (development phase)?

Test before dropping (verify no dependency).

🎯 Q6: How do I monitor locks?

SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

Shows blocked queries. Blocking session ID = blocker.

Kill blocker or wait for completion.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Monitoring and performance analysis in SQL Server. Try these prompts:

• "Show DMV query to find top 10 slowest queries"
• "How do I identify missing indexes that would help performance?"
• "How do I detect lock contention and blocking?"
• "What's the difference between physical and logical reads?"
• "Quiz me: ask 5 questions about DMVs, performance analysis, bottlenecks"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

33. Security & User Management -- Controlling Access ->