The secret of fixing SQL performance issues
When Saving Minutes Costs You Hours of Debugging Later.
SQL debugging differs from procedural application debugging.
SQL is declarative, so the optimizer decides the execution plan while you state desired results.
Error types:
There are five types of SQL errors:
1) Syntax Errors:
Errors occur when a query violates the grammar rules of the SQL dialect, for example, missing a comma or having unmatched parentheses.
If you omit required clauses or use invalid characters, SQL will trigger syntax errors.
2) Runtime errors:
Runtime errors occur during query execution: division by zero, arithmetic overflow, deadlock, or constraint violations.
3) Logical errors:
Logical errors produce incorrect results and do not raise any error message, so they are the hardest to detect.
Flawed query design causes these errors: incorrect use of predicates, improper handling of NULL values, or mismatched data types.
If a subquery used with NOT IN statement can return NULL, the query will return no rows.
Use NOT EXISTS or filter NULLs to avoid that behavior. Test edge cases and compare results across approaches.
4) System Errors:
System errors relate to database infrastructure issues, including connection failures, authentication problems, database corruption, insufficient memory, or network issues.
These errors appear as query execution failures or performance degradation and require debugging techniques that go beyond query design, involving database administration tools and monitoring.
5) Data Corruption and Integrity Errors
Hardware failures, software bugs, human errors, or malware attacks cause data corruption, compromising database integrity.
Forensic analysis of database files and logs identifies these issues, and you restore from backups or apply database repair tools.
Constraint Violations Foreign key violations, unique constraint failures, and check constraint errors indicate application bugs or race conditions.
Debugging tools:
SSMS is the baseline for SQL Server, with built-in debugging and profiling: query execution plans, breakpoints, step-through execution, and variable monitoring.
3rd party tools and AI:
Warehouse-aware AI tools can connect to your database schema. They read table metadata, column-level lineage, and foreign key relationships.
VS Code MSSQL Extension.
SolarWinds Database Performance Analyzer (Cross-platform).
JetBrains DataGrip (Cross-platform).
DBeaver Pro (cross-platform).
Now let’s move to debugging methods.
Execution Plan Analysis:
The execution plan is the most important debugging tool for SQL queries.
It shows you how the database engine processes a query, which indexes it uses, how tables join, and if you have bottlenecks.
Run the following commands in SQL Server Management Studio (SSMS) or Azure Data Studio.
For example, let’s analyze a query that joins an Orders table with a Customers table.
-- Turn on actual execution plan output in XML
SET STATISTICS XML ON;
GO
-- The query you want to analyze
SELECT
c.CustomerName,
o.OrderDate,
o.TotalAmount
FROM
Orders o
INNER JOIN
Customers c ON o.CustomerID = c.CustomerID
WHERE
o.OrderDate >= ‘2026-01-01’;
GO
-- Turn off actual execution plan output in XML
SET STATISTICS XML OFF;
GOWhen you analyze the execution plan, look for these operators:
Index Seek (efficient selective access).
Index Scan or Table Scan (can indicate missing indexes).
Join operators: Nested Loops, Hash Match, or Merge Join.
Nested loops work well when one input is small. Hash matches process large unsorted datasets but use more memory.
Merge joins require sorted inputs.
Check for large gaps between estimated and actual row counts, warning icons, spills to tempdb, and high-cost operators.
Cost Estimates:
You can visualize execution plans to spot the bottlenecks.
Scan for high-cost operations with high estimated costs: table scans, nested loops with large inputs, and sort operations possess plenty of room for optimization.
Look for warnings about missing indexes, statistics, or data type conversions.
What you should examine:
Scan vs. seek operators join types (nested loops, hash, and merge).
Estimated vs. actual row counts.
Parallelism usage.
Warnings and missing index suggestions.
Concurrency debugging:
Concurrency bugs are elusive, non-deterministic defects that occur when multiple threads or processes execute simultaneously and interfere with each other.
Deadlock Resolution:
A deadlock occurs when two sessions lock each other. The database engine detects the deadlock, queues one of the sessions, and rolls back its transaction.
How to fix it:
1) Always access tables in the same order across all transactions.
2) If Transaction A updates table one and then table two, then Transaction B should also update table one and then table two.
3) Keep transactions short.
Debug Stored Procedures:
Use the SSMS stored procedure debugger in supported environments, set breakpoints, watch variables, and step through code.
If the debugger is unavailable, add diagnostic PRINT statements, use temporary tables to capture state, or run T-SQL unit tests with tSQLt.
The debug windows show parameter values, return values, and local variables in real time. The Watch window can add specific variables and expressions to monitor.
The Call Stack window shows the hierarchy of procedure calls that led to the current point. Use these tools together to understand what happens during execution.
The server must apply debugger connections, and your account needs permissions to execute the procedure and view server state. Some hosting environments deactivate the debugger for security reasons.
Logging and Tracing:
If you need an immediate visual report on execution statistics, slow queries, and locks without configuring external tools, you can use the native reports inside SQL Server Management Studio (SSMS).
These leverage underlying Dynamic Management Views (DMVs) and can be exported directly to HTML or PDF.
How to access: Right-click your database or server instance in SSMS >> Reports >> Standard Reports.
Top Queries by Average CPU Time / Total CPU Time: Identifies the resource-intensive, slow queries.
All Blocking Transactions: Provides a clear view of lock waits, block chains, and resource contention.
Object Execution Statistics: Displays historical statistics for cached query execution plans.
Automated testing:
Unit Testing:
tSQLt is an open-source unit testing framework built entirely inside T-SQL.
It can run and automate unit tests in SQL Server Management Studio (SSMS) or CI/CD pipelines without leaving the database ecosystem.Because database code inherently relies on state (tables, constraints, and rows), traditional application unit testing does not work.
tSQLt has 3 methods to solve that problem: transaction isolation, table faking, and stored procedure spying.
Regression verification:
Regression verification in SQL Server runs in 2 modes:
Performance Regression Verification checks whether a query, procedure, or workload got slower after some change.
Functional Regression Verification checks that after a change, your SQL code still returns the correct results and works the same way.
Methods for Performance Regression Verification:
Performance regression happens when a query that previously ran well suddenly switches to an inefficient execution plan and runs significantly slower.
SQL Server provides native, automated methods to verify and correct this:
1. Query Store:
Query Store continuously aggregates the history of query execution plans, CPU consumption, execution duration, and logical reads. After analyzing this historical baseline, SQL Server identifies the moment query performance deviates from its standard.
2. Automatic Plan Correction:
Automatic Plan Correction (APC) checks if a query got slower after a plan change and automatically reverts to the last known good plan:
The Three-Sigma Rule (Legacy): tracks if a query’s mean CPU time shifts by more than three standard deviations after a compilation event. It requires at least 15 executions to establish the variance.
Welch’s t-Test Model: The engine uses a Welch’s t-test to evaluate two execution plan populations with unequal variances and verify a performance regression in only 2 or 3 executions.
Methods for Functional Regression Verification:
1. Set-Based Data Diff Verification (EXCEPT):
When you refactor legacy T-SQL objects (rewrite a massive reporting procedure), use a direct set-operator validation approach to verify code changes:
Run the old, untouched version of the query and pipe the output rows into an isolation table (
#Expected).Run the newly modified, optimized query into a matching table (
#Actual).Run an
EXCEPTquery to evaluate discrepancies:
SELECT
*
FROM
#Expected
EXCEPT
SELECT * FROM #Actual;If the query returns zero rows, functional regression verification succeeds. But if any rows appear, the new code has introduced a functional deviation.
Debugging workflow:
Identify an error: Capture errors via logs, monitoring alerts, and user reports.
Root cause analysis: Run query profiling, evaluate execution plans, and log analysis to pinpoint the source.
Hypothesis testing: Isolate and test problematic query segments or stored procedure steps.
Fix: Apply targeted changes, such as query rewrites, index adjustments, or code corrections.
Validate: Rerun tests, monitor performance, and look for side effects.
Document and automate: Record the fix, update tests, and automate regression checks for future safety.
Best Practices:
Do not use SELECT *. List the columns you need in your query.
Use JOIN or EXISTS functions instead of subqueries in the WHERE clause. The optimizer performs joins better.
Do not use leading wildcards. The database engine will run a full text search even if you have an index.
Use GROUP BY on key columns instead of using the DISTINCT function.
Write queries with consistent formatting: indent subqueries and CTEs, and place each clause on its own line.
Add comments to explain complex calculations or business rules.
Use transactions when debugging UPDATE, DELETE, or INSERT statements.
Follow me for more weekly SQL and Data Analytics tips.
What’s your hardest SQL bug to solve?
Let me know in the comments👇
P.S. I’m launching an advanced Power BI performance guide.
» Join the waitlist