SQL Server Interview Questions & Answers

Topic 7: Query Processing & Performance Tuning

Questions 63–74 | Intermediate to Advanced

This section covers practical SQL Server performance tuning concepts including execution plans, parameter sniffing, wait statistics, I/O waits, transaction log waits, memory, parallelism and Query Store.

Effective performance tuning is not about changing random configuration settings. The correct approach is to identify the bottleneck, collect evidence, understand the execution plan or wait condition and then address the root cause.

63. How do you troubleshoot a slow SQL query?

Answer:
I would troubleshoot a slow query systematically instead of immediately adding an index or changing SQL Server configuration.

A practical approach is:

Confirm whether the problem affects one query, one application, one database or the entire SQL Server instance.
Capture the actual execution plan.
Check logical reads, CPU time and elapsed time.
Compare estimated rows with actual rows.
Review Index Seeks, Scans, Key Lookups, Sorts and Spills.
Check whether statistics accurately represent the current data.
Review existing indexes before creating new ones.
Check for blocking and concurrency issues.
Review relevant wait statistics.
Investigate parameter sensitivity if performance varies with different parameter values.
Compare current and historical plans using Query Store when available.

A useful starting point during controlled testing is:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT
    ...
FROM dbo.YourTable
WHERE ...;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Interview Tip:
A strong answer should demonstrate a troubleshooting methodology. Start with evidence, identify where time and resources are being consumed, and then fix the root cause.

64. What is an execution plan?

Answer:
An execution plan describes the physical operations SQL Server chooses to execute a query.

The Query Optimizer evaluates possible strategies and chooses an execution plan based on estimated cost.

An execution plan may contain operators such as:

Index Seek
Index Scan
Table Scan
Key Lookup
Nested Loops
Hash Match
Merge Join
Sort
Filter
Aggregate
Parallelism operators

Execution plans help DBAs understand how SQL Server accesses data, joins tables, performs calculations and returns the requested result.

DBA Tip:
Do not focus only on the operator showing the highest percentage cost. Review row estimates, actual rows, predicates, warnings, reads, memory grants and data flow across the entire execution plan.

65. Estimated vs Actual Execution Plan?

Answer:
An estimated execution plan shows the plan SQL Server expects to use without actually executing the query.

An actual execution plan is collected after executing the query and includes runtime information from that execution.

Estimated Execution Plan

The query is not executed to generate the plan.
Shows optimizer estimates.
Useful when executing the query would be expensive or unsafe.

Actual Execution Plan

The query actually executes.
Includes runtime information.
Allows comparison of estimated and actual row counts.
Can expose runtime warnings such as spills.

A significant difference between estimated and actual row counts can indicate issues such as inaccurate cardinality estimates, stale or insufficient statistics, data skew or parameter sensitivity.

Interview Tip:
The actual plan does not mean the optimizer selected a completely different type of plan called an "actual plan." It adds runtime execution information to the plan used for that execution.

66. What is parameter sniffing?

Answer:
Parameter sniffing refers to SQL Server using parameter values available during compilation when estimating selectivity and generating an execution plan for a parameterized statement or stored procedure.

The resulting plan may then be cached and reused for subsequent executions.

This is normally beneficial because it allows SQL Server to optimize the query for actual parameter values.

Problems can occur when data is highly skewed and one cached plan is efficient for one parameter value but inefficient for another.

Example:

CREATE PROCEDURE dbo.GetOrders
    @CustomerID int
AS
BEGIN

    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID;

END;

Imagine:

Customer A = 5 rows

Customer B = 2,000,000 rows

A plan optimized around one distribution may perform poorly for the other.

Interview Tip:
Parameter sniffing itself is not automatically a problem. The problem occurs when parameter sensitivity causes inappropriate plan reuse for materially different parameter values.

67. How would you troubleshoot a parameter-sensitive plan?

Answer:
I would first prove that different parameter values produce significantly different data volumes or performance characteristics.

I would investigate:

Which parameter values are fast and slow
Actual execution plans
Estimated versus actual rows
Data distribution and skew
Statistics quality
Existing indexes
Cached execution plans
Query Store history

Depending on the root cause and SQL Server version, possible solutions may include:

Updating or improving statistics
Improving indexes
Rewriting the query
Using OPTION (RECOMPILE) selectively
Using OPTIMIZE FOR where appropriate
Applying an appropriate Query Store hint or plan-control mechanism
Using Parameter Sensitive Plan optimization on supported SQL Server versions and configurations

Example:

SELECT
    OrderID,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

DBA Tip:
Do not immediately add OPTION (RECOMPILE) everywhere. Recompilation has CPU overhead and removes some benefits of plan reuse. Choose the solution based on the workload and root cause.

68. What are SQL Server wait statistics?

Answer:
Wait statistics record categories of time SQL Server workers spend waiting for resources or events before they can continue processing.

They are a powerful way to understand where SQL Server is spending time at the instance level.

Examples include:

PAGEIOLATCH_* - waiting for data pages to arrive from storage
WRITELOG - waiting for transaction log writes
LCK_M_* - waiting for locks
SOS_SCHEDULER_YIELD - CPU scheduling-related waits
RESOURCE_SEMAPHORE - waiting for query execution memory grants
ASYNC_NETWORK_IO - waiting for the client to consume query results

A basic query is:

SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Important:
Not every wait indicates a performance problem. SQL Server always has waits. The objective is to identify waits that are significant for the workload and correlate them with observable performance issues.

69. What does `PAGEIOLATCH_*` indicate?

Answer:
PAGEIOLATCH_* waits occur when SQL Server is waiting for a database page involved in an I/O operation to become available in memory.

When these waits are significant, I would investigate:

Storage read latency
Queries performing excessive logical or physical reads
Missing or inefficient indexes
Large table or index scans
Memory pressure causing useful pages to leave the Buffer Pool
Data-file placement and storage performance

I would not immediately conclude that the storage is slow.

A poorly written query reading millions of pages can produce I/O waits even on fast storage.

Interview Tip:
High PAGEIOLATCH_* waits should trigger an investigation into both storage latency and why SQL Server is requesting so much data.

70. What does `WRITELOG` indicate?

Answer:
WRITELOG represents waits related to SQL Server flushing transaction log records to durable storage.

Significant WRITELOG waits may be associated with:

High transaction-log write latency
Very frequent small transactions
Excessive transaction volume
Large data modification operations
Slow transaction log storage
Workloads generating excessive log records

I would review:

Log-file write latency
Transaction patterns
Log file sizing and growth behavior
Storage used by the transaction log
Application commit frequency

Interview Tip:
WRITELOG does not simply mean "the log file is full." It specifically relates to waiting for log records to be flushed.

71. Is 99% Buffer Cache Hit Ratio always good?

Answer:
No. Buffer Cache Hit Ratio should not be interpreted in isolation.

It represents how frequently requested pages are satisfied from the Buffer Pool rather than requiring physical reads, but a high value does not automatically prove that overall SQL Server performance is healthy.

A server could have a high Buffer Cache Hit Ratio and still suffer from:

CPU bottlenecks
Blocking
Transaction log latency
Bad execution plans
Excessive logical reads
Memory-grant problems

DBA Principle:
Avoid diagnosing SQL Server from one performance counter. Correlate multiple metrics with the actual workload and observed problem.

72. Does increasing SQL Server memory always improve performance?

Answer:
No. Additional memory helps only when memory is genuinely a limiting resource for the workload.

SQL Server uses memory for many purposes, including:

Buffer Pool
Query execution memory
Plan cache
Internal SQL Server structures

Increasing memory may help when the instance is experiencing genuine memory pressure and the working set can benefit from additional memory.

It will not fix problems caused by:

Poor indexing
Bad query design
Blocking
Slow transaction-log storage
CPU saturation
Inaccurate statistics

SQL Server should also be configured so that sufficient memory remains available for the operating system and other required processes.

Interview Tip:
More memory is not a universal SQL Server performance solution. First prove that memory pressure is the actual bottleneck.

73. Should MAXDOP always be 1?

Answer:
No. MAXDOP should not automatically be configured as 1 on every SQL Server.

MAXDOP controls the maximum number of processors that can participate in a parallel section of a query execution plan.

Setting:

MAXDOP = 1

prevents parallel query execution at that scope.

The correct configuration depends on factors such as:

CPU and NUMA architecture
SQL Server version
OLTP vs analytical workload
Query characteristics
Concurrency
Existing parallelism-related waits

MAXDOP should also be considered alongside Cost Threshold for Parallelism rather than tuning either setting blindly.

Common Interview Trap:
MAXDOP = 1 is not a universal performance best practice. The correct value depends on the server architecture and workload.

74. What is Query Store?

Answer:
Query Store is a SQL Server feature that retains query text, execution plans and runtime performance information over time.

It is particularly valuable for identifying query-performance changes and plan regressions.

Query Store can help answer questions such as:

Which queries consume the most resources?
Which queries recently became slower?
Did the execution plan change?
Which plans have been used for this query?
Can a known good plan be forced?

Check Query Store configuration using:

SELECT
    actual_state_desc,
    desired_state_desc,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Query Store data can be explored through views such as:

sys.query_store_query
sys.query_store_query_text
sys.query_store_plan
sys.query_store_runtime_stats

DBA Tip:
Query Store is extremely useful when a query was fast yesterday but slow today because it provides historical evidence rather than relying only on what is currently in the plan cache.

Useful DBA Script: Find Expensive Cached Queries

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000.0 AS TotalCPU_ms,
    qs.total_elapsed_time / 1000.0 AS TotalElapsed_ms,
    qs.total_logical_reads AS TotalLogicalReads,
    qs.total_logical_writes AS TotalLogicalWrites,
    qs.total_physical_reads AS TotalPhysicalReads,
    qs.total_worker_time /
        NULLIF(qs.execution_count, 0) / 1000.0 AS AvgCPU_ms,
    qs.total_elapsed_time /
        NULLIF(qs.execution_count, 0) / 1000.0 AS AvgElapsed_ms,
    SUBSTRING
    (
        st.text,
        (qs.statement_start_offset / 2) + 1,
        (
            (
                CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE qs.statement_end_offset
                END
                - qs.statement_start_offset
            ) / 2
        ) + 1
    ) AS StatementText
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;

Important:
Cache-based DMVs represent the plans currently available in cache. They should not be treated as permanent historical performance data.

Useful DBA Script: Current Waiting Requests

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;

Useful DBA Script: Check File I/O Latency

SELECT
    DB_NAME(vfs.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.type_desc AS FileType,

    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,

    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,

    vfs.num_of_reads,
    vfs.num_of_writes

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;

SQL Server Performance Troubleshooting Flow


      Performance Problem Reported

         ↓

      Identify Scope

      Query / Database / Instance / Application

         ↓

      Check Blocking and Active Requests

         ↓

      Review Wait Statistics

         ↓

      Identify CPU / Memory / I/O / Log / Lock Bottleneck

         ↓

      Review Actual Execution Plan

         ↓

      Check Row Estimates and Statistics

         ↓

      Review Indexes and Query Design

         ↓

      Check Query Store for Historical Regression

         ↓

      Apply Targeted Fix

         ↓

      Measure Before vs After

Interview Quick Recap

Troubleshoot performance systematically rather than changing configurations blindly.
Execution plans show how SQL Server processes a query.
Actual execution plans provide runtime information that estimated plans do not.
Parameter sniffing is normal behavior, but parameter-sensitive workloads can experience inefficient plan reuse.
Compare parameter values, plans, cardinality estimates and data distribution when investigating parameter-sensitive queries.
Wait statistics help identify where SQL Server workers spend time waiting.
PAGEIOLATCH_* should trigger investigation into storage latency and excessive data access.
WRITELOG indicates waits associated with transaction log flushes.
A 99% Buffer Cache Hit Ratio does not prove that the server has no performance problem.
Adding memory only helps when memory pressure is actually contributing to the bottleneck.
MAXDOP = 1 is not a universal best practice.
Query Store provides valuable historical query-performance and plan information.

← Previous Topic
# Topic 6: Indexes & Statistics

Next Topic →

# Topic 8: Locks, Blocking & Deadloacks

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 7: Query Processing & Performance Tuning