Published / updated29 Aug 2026

Debugging Databases

Before you start

You need: API debugging (Article 04) and SQL (Track 06).

Time: about 55 minutes, at the keyboard.

Learning objective

Capture the SQL your application actually runs, verify it against the data, and diagnose slowness, blocking and silently wrong results.

Topics

• Seeing the SQL your code runs
• Verifying against the data
• Wrong results with no error
• Nulls and aggregates
• Joins that change row counts
• Slow queries
• Blocking and deadlocks
• Connection problems

Seeing the SQL

You cannot debug a query you have not read.

EF Core:

{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

Executed DbCommand (14ms) [Parameters=[@__schoolId_0='1'], CommandType='Text']
SELECT [s].[Id], [s].[Name], [s].[RollNumber]
FROM [Student] AS [s]
WHERE [s].[SchoolId] = @__schoolId_0

Dapper — log the SQL and parameters yourself, or use SQL Server's own capture.

Extended Events in SSMS (Management → Extended Events → sessions) captures every statement the server actually receives, which is the ground truth when logging and behaviour disagree.

SELECT TOP 20
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    qs.total_logical_reads / qs.execution_count      AS avg_reads,
    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 statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_ms DESC;

Run the captured SQL in SSMS with the same parameters. If it returns the wrong answer there too, the bug is the query. If it returns the right answer, the bug is in the mapping, the caching, or which parameters the application actually passed.

That split is the whole method for this article.

Verifying against the data

SELECT COUNT(*) FROM Student WHERE SchoolId = 1;
SELECT * FROM Student WHERE RollNumber = 'NCA-2024-0012';
SELECT * FROM FeeAccount WHERE StudentId = 12;
SELECT * FROM ExamResult WHERE StudentId = 12 AND ExamId = 5;

"The API returns nothing" has two causes: the query is wrong, or the data is not there. One SELECT distinguishes them, and it should come before reading any C#.

Checks worth running when a result looks wrong:

-- Is the row soft-deleted?
SELECT Id, Name, IsDeleted FROM Student WHERE Id = 12;

-- Does the row belong to another school?
SELECT Id, Name, SchoolId FROM Student WHERE RollNumber = 'NCA-2024-0012';

-- Trailing whitespace or case
SELECT Id, '[' + RollNumber + ']' AS Padded, LEN(RollNumber) FROM Student WHERE Id = 12;

-- Nulls where the code assumes a value
SELECT COUNT(*) FROM ExamResult WHERE MarksObtained IS NULL;

Trailing whitespace is invisible and breaks comparisons. Wrapping in brackets shows it immediately.

Wrong results with no error

These are the dangerous database bugs: the query succeeds, returns rows, and the rows are wrong.

Missing tenant filter

-- Wrong: every school's students
SELECT * FROM Student WHERE ClassName = '10th';

-- Correct
SELECT * FROM Student WHERE SchoolId = @SchoolId AND ClassName = '10th';

No error. A 200 response. Data from other schools.

Every query against every table needs SchoolId in its WHERE clause, and @SchoolId must come from the auth claim.

Missing soft-delete filter

-- Returns deleted students
SELECT * FROM Student WHERE SchoolId = @SchoolId;

-- Correct
SELECT * FROM Student WHERE SchoolId = @SchoolId AND IsDeleted = 0;

Soft delete creates an obligation on every read. Add the column and you have added a filter to every existing query — miss one and deleted records reappear in a report.

Uniqueness on the wrong column

-- Wrong for a multi-tenant system: two schools cannot both have NCA-2024-0001
ALTER TABLE Student ADD CONSTRAINT UQ_Student_RollNumber UNIQUE (RollNumber);

-- Correct
ALTER TABLE Student ADD CONSTRAINT UQ_Student_School_RollNumber UNIQUE (SchoolId, RollNumber);

The symptom is an insert failing for a school that has never used that roll number.

Money in the wrong type

Amount FLOAT          -- ₹12,000.00 becomes 11999.999999999998
Amount DECIMAL(18,2)  -- correct

Float arithmetic does not sum exactly. A fee report that is off by paise is this, every time.

Nulls and aggregates

SELECT SUM(Amount) FROM FeePayment WHERE FeeAccountId = 5;   -- NULL if no rows
SELECT ISNULL(SUM(Amount), 0) FROM FeePayment WHERE FeeAccountId = 5;

SUM over no rows returns NULL, not 0. In C#, assigning that to a non-nullable decimal throws; assigning it to decimal? and using it silently produces nothing.

-- COUNT(*) counts rows; COUNT(column) skips NULLs
SELECT COUNT(*), COUNT(MarksObtained) FROM ExamResult WHERE ExamId = 5;

-- AVG ignores NULLs entirely — which is right for absent students
SELECT AVG(MarksObtained) FROM ExamResult WHERE ExamId = 5;

-- and wrong if absences were stored as 0

This is the absent-student problem in its database form. If an absent student's MarksObtained is stored as 0, the class average is wrong and nothing indicates it. If it is stored as NULL, AVG correctly excludes them.

Store absent as NULL with IsAbsent = 1. Never as 0.

-- Comparisons with NULL are never true
WHERE MarksObtained <> 0        -- excludes NULL rows too
WHERE MarksObtained IS NULL     -- the only way to test for NULL

SELECT s.Name,
       CASE WHEN r.IsAbsent = 1 THEN 'Absent'
            WHEN r.MarksObtained >= sub.PassingMarks THEN 'Pass'
            ELSE 'Fail' END AS Result
FROM ExamResult r
JOIN Student s ON s.Id = r.StudentId
JOIN Subject sub ON sub.Id = r.SubjectId
WHERE r.ExamId = @ExamId AND s.SchoolId = @SchoolId;

The absent check must come first. Reverse the first two WHEN clauses and every absent student is marked Fail.

Joins that change row counts

-- Looks right, returns 800 students
SELECT s.Name, f.TotalFees
FROM Student s
JOIN FeeAccount f ON f.StudentId = s.Id
WHERE s.SchoolId = 1;

Two problems hide here.

JOIN is an inner join, so students with no fee account disappear. The count drops from 800 to 780 and nobody notices until someone asks why a student is missing from a report.

LEFT JOIN FeeAccount f ON f.StudentId = s.Id

And if a student has three fee accounts (one per academic year), that student appears three times — and any SUM over the result triples.

-- Check before trusting a joined result
SELECT COUNT(*) FROM Student WHERE SchoolId = 1;                 -- 800
SELECT COUNT(*) FROM Student s JOIN FeeAccount f ON f.StudentId = s.Id
WHERE s.SchoolId = 1;                                            -- 780? 2400?

Compare row counts before and after every join. A number that goes down means an inner join dropped rows; a number that goes up means the join multiplied them. Both produce wrong totals with no error.

Slow queries

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Table 'ExamResult'. Scan count 1, logical reads 48291, physical reads 0

Logical reads are the metric to tune against, not elapsed time — elapsed time varies with cache and load; logical reads are stable and comparable.

Ctrl+M in SSMS includes the actual execution plan. What to look for:

In the plan	Meaning
Table Scan / Clustered Index Scan	Reading everything — a missing index, on a large table
Key Lookup	The index found rows but had to fetch more columns — consider INCLUDE
Thick arrows	Many rows flowing between operators
Estimated vs actual rows differ wildly	Stale statistics — UPDATE STATISTICS
Sort with a high cost	An ORDER BY with no supporting index

CREATE NONCLUSTERED INDEX IX_ExamResult_School_Exam
    ON ExamResult (SchoolId, ExamId)
    INCLUDE (StudentId, MarksObtained, IsAbsent);

A scan on 50 rows is fine. A scan on 5 million is the problem. Do not index reflexively — every index slows writes.

The common causes of a slow query in application code:

Cause	Fix
N+1 — a query per row in a loop	One query with a join, or Include
A function on an indexed column: WHERE YEAR(PaidOn) = 2024	Range: PaidOn >= '2024-01-01' AND PaidOn < '2025-01-01'
SELECT * fetching unused columns	Name the columns
No pagination	OFFSET … FETCH NEXT
Parameter sniffing	OPTION (RECOMPILE), or a local variable

N+1 is the most common performance bug in ORM code, and the SQL log is how you spot it: 801 queries where you expected one.

A function wrapping an indexed column makes the index unusable, which is why the date range form matters.

Blocking and deadlocks

SELECT
    r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
    t.text AS running_sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

A query that is suddenly slow and was not before is usually blocked, not slow. blocking_session_id names the session holding the lock — very often a developer who ran a BEGIN TRANSACTION in SSMS and went to lunch.

SELECT @@TRANCOUNT;      -- must be 0 before you close a window

Never leave a transaction open in SSMS. It blocks the whole application, and the symptom looks like a broken API.

Deadlock:

Transaction (Process ID 62) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

Two transactions each hold what the other needs. The fix is to access tables in the same order everywhere — if one code path updates FeeAccount then FeePayment, every path must.

Keep transactions short, and never wait for user input inside one.

Connection problems

Error	Cause
2 / 53 — server not found	Wrong instance name, service stopped, firewall
18456 — login failed	Wrong credentials, or the login does not exist
4060 — cannot open database	Wrong database name, or no permission
-2 — timeout expired	Query too slow, or blocked
Max pool size reached	Connections not disposed

// Leaks a connection on every exception
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

// Correct
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
}

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool" is not a slow query — it is a leak. Every connection is checked out and never returned, and the fix is a using block, not a bigger pool.

Diagnosing

Symptom	Check
No rows returned	Run the SQL in SSMS — data, or query?
Wrong school's data	Missing SchoolId filter
Deleted records reappear	Missing IsDeleted = 0
Totals off by paise	FLOAT instead of DECIMAL
Sum is null	SUM over no rows — ISNULL(...,0)
Class average wrong	Absences stored as 0 instead of NULL
Rows missing from a report	Inner join where a left join was needed
Totals inflated	A join multiplying rows
Suddenly slow	sys.dm_exec_requests — blocked?
801 queries for one page	N+1
Pool exhausted	Connections not disposed

Errors you will hit

What you see	Likely cause
No rows returned	Query wrong, or the data is not there — one SELECT tells you which
Another school's data	Missing SchoolId filter
Deleted records reappear	Missing soft-delete filter
Totals off by paise	FLOAT instead of DECIMAL
Class average wrong	Absences stored as 0
Rows missing from a report	Inner join where a left join was needed
Suddenly slow	Blocked, not slow — check sys.dm_exec_requests
801 queries for one page	N+1

Run the captured SQL in SSMS. If it is wrong there, the query is the bug; if it is right, the bug is in the mapping or the parameters.

Common mistakes

• Debugging the C# without reading the generated SQL
• Not checking whether the data actually exists
• Omitting the SchoolId filter
• Forgetting the soft-delete filter
• UNIQUE on RollNumber alone
• FLOAT for money
• Storing absent marks as 0
• Putting the absent check after the pass check
• Inner join where left join is needed
• Not comparing row counts before and after a join
• Tuning on elapsed time instead of logical reads
• Wrapping an indexed column in a function
• Leaving a transaction open in SSMS
• Connections without using

Practice

The course exercise is debug a data problem.

1. Enable EF Core command logging and read the SQL for one page load.
2. Take that SQL into SSMS, run it with the same parameters, and compare results.
3. Report "no data" for a student, then determine with one SELECT whether the query or the data is at fault.
4. Add trailing whitespace to a RollNumber and find it using the bracket trick.
5. Remove the SchoolId filter from one query and confirm you can read another school's students with no error.
6. Add IsDeleted to Student, soft-delete a row, and find every query that now returns it.
7. Create UNIQUE (RollNumber), then try to insert the same roll number for a second school.
8. Store an amount as FLOAT, sum a thousand payments, and compare with DECIMAL(18,2).
9. Run SUM over an empty result set and assign it to a non-nullable decimal in C#.
10. Store an absent student's marks as 0, compute the class average, then change to NULL and compute again.
11. Write the grading CASE with the absent check last, and confirm absent students are marked Fail.
12. Compare row counts before and after an inner join, then a join that multiplies rows.
13. Turn on SET STATISTICS IO, run a query with and without a supporting index, and compare logical reads.
14. Write WHERE YEAR(PaidOn) = 2024, check the plan for a scan, rewrite as a range, and compare.
15. Create an N+1 in EF Core, count the queries in the log, then fix it with Include.
16. Open a transaction in SSMS without committing, then run the application and find the block with sys.dm_exec_requests.
17. Create a connection without using, loop 200 times, and exhaust the pool.

Exercises 5, 10 and 12 are the three that produce no error message. Those are the ones this article exists for.

You can now

• Capture the SQL your application actually runs
• Verify it against the data in SSMS
• Recognise the silent data bugs
• Diagnose slowness with logical reads and a plan
• Find blocking and a leaked connection

Review questions

1. What does running the captured SQL in SSMS tell you that the application cannot?
2. Why must an absent student's marks be NULL rather than 0?
3. What two ways can a join make a result wrong without erroring?
4. Why are logical reads a better tuning metric than elapsed time?

Next: Debugging the full flow