SQL Server Interview Questions & Answers

Topic 12: Production Performance Troubleshooting

Questions 101–110 | Advanced Scenario-Based Questions

This section begins Part II of the SQL Server Interview Questions series and focuses on real-world production performance scenarios.

The objective is not simply to identify a DMV or configuration setting. A senior DBA should be able to establish a baseline, identify what changed, isolate the bottleneck, validate the root cause and measure whether the corrective action actually improved performance.

101. A query that normally completes in 2 seconds suddenly takes 5 minutes. How would you troubleshoot it?

Answer:
I would first determine what changed between the normal 2-second execution and the current 5-minute execution rather than immediately modifying the query or creating an index.

I would investigate the following areas:

Check whether the query is actually running or waiting.
Review the current wait type, blocking session and wait resource.
Check for blocking.
A normally fast query can take several minutes simply because it is waiting behind another transaction.
Compare execution plans.
Determine whether the query is using a different plan from the one used when performance was normal.
Check parameter values.
Different parameters may return dramatically different numbers of rows and expose a parameter-sensitive plan.
Check statistics.
Significant data changes or inaccurate statistics can lead to poor cardinality estimates and an inefficient plan.
Check logical reads, CPU and elapsed time.
Determine whether the query is consuming more resources or mainly spending time waiting.
Check Query Store.
If enabled, compare historical execution plans and performance.
Check environmental changes.
Review recent deployments, index changes, statistics changes, configuration changes, data growth and infrastructure events.

During controlled testing I may use:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Execute query here

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Senior DBA Tip:
If a query suddenly changes from 2 seconds to 5 minutes, the first question should be: "What changed?"

Check blocking, execution-plan changes, parameters, statistics and resource waits before changing the query.

102. CPU utilization on the SQL Server suddenly reaches 100%. What would you check first?

Answer:
I would first confirm whether SQL Server is actually responsible for the CPU consumption.

If SQL Server is responsible, I would determine which sessions and queries are consuming the CPU.

I would investigate:

Currently executing CPU-intensive requests
Recent expensive queries
Execution-plan changes
Large table or index scans
Missing or inefficient indexes
Statistics and cardinality-estimation problems
Excessive recompilation
High concurrency
Parallel queries
Recently deployed application code

A useful starting query is:

SELECT TOP (20)
    r.session_id,
    r.status,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    st.text AS SQLText
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

For recently cached workloads, I could also review accumulated CPU:

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000.0 AS TotalCPU_ms,
    qs.total_worker_time /
        NULLIF(qs.execution_count, 0) / 1000.0 AS AvgCPU_ms,
    qs.total_logical_reads,
    st.text AS SQLText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

Interview Tip:
Do not immediately change MAXDOP or add CPUs. First identify which workload is consuming the CPU and why.

103. SQL Server is experiencing high memory pressure. How would you identify the cause?

Answer:
I would first determine whether the pressure is occurring inside SQL Server, at the operating-system level, or both.

I would review:

SQL Server max server memory configuration
Available operating-system memory
Memory grants
Queries waiting for execution memory
RESOURCE_SEMAPHORE waits
Large query memory grants
Plan cache usage
Buffer Pool usage
Other processes consuming server memory

A useful SQL Server memory overview is:

SELECT
    physical_memory_in_use_kb / 1024 AS SQLMemoryUsedMB,
    large_page_allocations_kb / 1024 AS LargePagesMB,
    locked_page_allocations_kb / 1024 AS LockedPagesMB,
    memory_utilization_percentage,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;

To look for queries waiting on memory grants:

SELECT
    session_id,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

DBA Tip:
Do not diagnose memory pressure solely because SQL Server is using most of the configured memory. SQL Server is designed to cache data in memory. Look for actual indicators of memory pressure.

104. Users report that the application is slow, but CPU and memory utilization appear normal. How would you investigate?

Answer:
Normal CPU and memory do not mean SQL Server is healthy. The workload may be spending most of its time waiting instead of consuming CPU.

I would check:

Blocking
Current wait types
Storage latency
Transaction log latency
Network/client consumption
Long-running transactions
Query-plan regressions
Availability Group synchronization issues
Application connection-pool issues

Current waits can be reviewed using:

SELECT
    session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id > 50
ORDER BY wait_duration_ms DESC;

This helps answer a critical performance question:

Is SQL Server working, or is SQL Server waiting?

Interview Tip:
When CPU appears normal but users report slowness, waits and blocking should be among the first areas investigated.

105. A stored procedure is fast for some parameter values but very slow for others. What could be causing this?

Answer:
One common cause is a parameter-sensitive execution plan.

SQL Server may compile and cache a plan using one parameter value and later reuse that plan for very different parameter values.

For example:

Customer A = 10 orders

Customer B = 3,000,000 orders

A plan that works well for Customer A may perform badly for Customer B.

I would check:

Data skew
Parameter values used during slow and fast executions
Execution plans
Estimated vs actual rows
Statistics
Indexes
Query Store plan history

Possible solutions depend on the root cause and can include:

Improved statistics
Better indexing
Query redesign
OPTION (RECOMPILE) for appropriate cases
OPTIMIZE FOR where justified
Query Store hints or plan forcing where appropriate
Parameter Sensitive Plan optimization on supported configurations

Interview Tip:
Parameter sniffing itself is normal SQL Server behavior. The problem occurs when materially different parameter values require different strategies but an unsuitable cached plan is reused.

106. A query uses an Index Scan even though an appropriate index appears to exist. Why might this happen?

Answer:
An Index Scan is not automatically a problem. The Query Optimizer may estimate that scanning the index is cheaper than performing many seeks and lookups.

Possible reasons include:

The query returns a large percentage of the table
The predicate has low selectivity
The index does not cover the required columns
A Seek would require many expensive Key Lookups
Statistics do not accurately represent data distribution
Implicit conversions prevent efficient index use
A function is applied to the indexed column
Predicates are not SARGable
The index key order does not support the predicate effectively

For example:

-- Less SARGable pattern

WHERE YEAR(OrderDate) = 2026


-- Better search pattern

WHERE OrderDate >= '20260101'
AND OrderDate < '20270101'

Common Interview Trap:
Seek = good and Scan = bad is an oversimplification. A Scan may be the most efficient plan when a large portion of the data is required.

107. An execution plan shows a large difference between estimated and actual row counts. What does this indicate?

Answer:
A large difference suggests that the Query Optimizer's cardinality estimate did not match what happened during execution.

This can affect decisions involving:

Join strategy
Join order
Index access method
Memory grants
Parallelism
Sorts and hash operations

Possible causes include:

Stale or insufficient statistics
Skewed data distribution
Parameter sensitivity
Correlated predicates
Complex expressions
Table variables or temporary objects in certain scenarios
Optimizer assumptions that do not match the actual distribution

I would identify the earliest significant point in the execution plan where estimated and actual row counts diverge and investigate from there.

DBA Tip:
Do not focus only on the final operator. The first major cardinality estimation error can influence many downstream operators.

108. A production server shows high `PAGEIOLATCH_SH` waits. How would you troubleshoot it?

Answer:
PAGEIOLATCH_SH indicates workers are waiting for database pages involved in physical reads to become available in memory with the required latch mode.

I would not immediately conclude that the storage subsystem is slow.

I would investigate two major areas:

1. Is storage latency high?

Review data-file read latency:

SELECT
    DB_NAME(vfs.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.physical_name,
    vfs.num_of_reads,
    CASE
        WHEN vfs.num_of_reads = 0 THEN 0
        ELSE CAST
        (
            vfs.io_stall_read_ms * 1.0 /
            vfs.num_of_reads AS decimal(18,2)
        )
    END AS AvgReadLatencyMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY AvgReadLatencyMs DESC;

2. Why is SQL Server reading so many pages?

Investigate:

Large scans
Missing indexes
Poor query design
Excessive logical reads
Memory pressure
Execution-plan regressions

Interview Tip:
High PAGEIOLATCH_SH should lead to two questions:

1. Are physical reads slow?
2. Why does SQL Server need so many physical reads?

109. A production server shows high `WRITELOG` waits. What would you investigate?

Answer:
WRITELOG indicates sessions are waiting for transaction log records to be flushed to durable storage.

I would investigate:

Transaction log write latency
Storage performance for the log file
Transaction frequency
Very frequent small commits
Large modification operations
Bulk data loads
Index maintenance
Unexpected transaction-log generation
Log autogrowth events
Application transaction design

I would inspect file-level write latency:

SELECT
    DB_NAME(vfs.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.type_desc,
    mf.physical_name,
    vfs.num_of_writes,
    CASE
        WHEN vfs.num_of_writes = 0 THEN 0
        ELSE CAST
        (
            vfs.io_stall_write_ms * 1.0 /
            vfs.num_of_writes AS decimal(18,2)
        )
    END AS AvgWriteLatencyMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'LOG'
ORDER BY AvgWriteLatencyMs DESC;

Interview Tip:
WRITELOG does not mean the transaction log is full. It specifically relates to waiting for transaction log flush activity.

110. Users report intermittent application slowness that cannot be reproduced easily. How would you capture the problem?

Answer:
Intermittent problems require historical monitoring because the issue may disappear before the DBA connects to the server.

I would establish lightweight continuous monitoring using tools such as:

Query Store
Extended Events
SQL Server DMVs captured periodically
SQL Server Agent monitoring jobs
Windows Performance Monitor where appropriate
Application monitoring and telemetry

I would capture:

Timestamp
Query or procedure
Duration
CPU
Logical reads
Wait type
Blocking session
Execution plan
Database name
Application name
Host name
Login

For query regressions, Query Store is especially valuable because it allows comparison of historical runtime statistics and execution plans.

For blocking and deadlocks, I would capture the required events with Extended Events rather than trying to reproduce the issue manually.

Senior DBA Tip:
Intermittent problems are difficult to solve with a snapshot taken after the incident. Build enough historical telemetry so that when users report "the application was slow at 10:35 AM," you can reconstruct what SQL Server was doing at that time.

Useful DBA Script: Check Currently Running Requests

SELECT
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    s.login_name,
    s.host_name,
    s.program_name,
    st.text AS SQLText
FROM sys.dm_exec_requests AS r

INNER JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE
    r.session_id <> @@SPID

ORDER BY
    r.total_elapsed_time DESC;

Useful DBA Script: Top Wait Statistics

SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS ResourceWaitTimeMs
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
(
    'SLEEP_TASK',
    'SLEEP_SYSTEMTASK',
    'SQLTRACE_BUFFER_FLUSH',
    'WAITFOR',
    'LAZYWRITER_SLEEP'
)
ORDER BY wait_time_ms DESC;

Important:
Instance wait statistics are cumulative. A high historical wait does not prove that the same wait is responsible for the current incident. Baselines and deltas over meaningful time intervals are more useful.

Useful DBA Script: Check for Blocking

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.status,
    DB_NAME(r.database_id) AS DatabaseName,
    s.login_name,
    s.host_name,
    st.text AS SQLText
FROM sys.dm_exec_requests AS r

INNER JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE
    r.blocking_session_id <> 0

ORDER BY
    r.blocking_session_id,
    r.session_id;

Production Performance Troubleshooting Model


      Performance Incident

         ↓

      Establish Time + Scope

         ↓

      What Changed?

         ↓

      Check Blocking

         ↓

      Check Active Waits

         ↓

      CPU / Memory / I/O / Log / Network

         ↓

      Identify Expensive Query

         ↓

      Review Actual Execution Plan

         ↓

      Estimated vs Actual Rows

         ↓

      Check Statistics + Indexes

         ↓

      Check Query Store History

         ↓

      Identify Root Cause

         ↓

      Apply Targeted Fix

         ↓

      Compare Before vs After

         ↓

      Monitor for Recurrence

Senior DBA Troubleshooting Principle

Do not start with the fix.

Start with the evidence.

1. What is slow?
2. When did it become slow?
3. What changed?
4. Is SQL Server consuming resources or waiting?
5. Which query or session is responsible?
6. What does the execution plan show?
7. What is the root cause?
8. Can the improvement be measured after the fix?

Interview Quick Recap

When performance suddenly changes, first determine what changed.
High elapsed time does not necessarily mean high CPU usage.
Check whether a query is working or waiting.
High CPU should lead to identification of CPU-consuming queries before configuration changes are made.
High SQL Server memory utilization by itself does not prove memory pressure.
Normal CPU and memory do not rule out blocking, I/O or transaction log bottlenecks.
Parameter-sensitive plans can cause the same procedure to perform differently for different parameter values.
An Index Scan is not automatically bad.
Large estimated-vs-actual row differences point toward a cardinality estimation problem that should be investigated.
High PAGEIOLATCH_SH requires investigation into both physical I/O latency and excessive data reads.
High WRITELOG relates to transaction log flush waits, not simply a full transaction log.
Intermittent performance issues require historical telemetry such as Query Store and Extended Events.

← Previous Topic
# Topic 11: HA/DR & Administration

Next Topic →

# Topic 13: Indexing and Query Optimization Scenarios

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone

SQL Server Q&A Topic 12: Production Performance Troubleshooting