39. Troubleshooting & Debugging -- Finding and Fixing Issues

Level: Guided beginner to advanced

ℹ️ What You'll Learn

• Error message interpretation
• Query debugging techniques
• Data validation and corruption detection
• Deadlock investigation and resolution
• Performance analysis workflow
• Blocking and lock analysis
• Transaction state analysis
• Root cause identification

Problems happen. Finding root cause fast saves time. This article teaches systematic debugging.

Scenario 1: "Missing Student" Bug

Student inserted but SELECT returns nothing:

-- Symptom: Insert successful but SELECT returns 0 rows
INSERT INTO Student (Name, ClassName) VALUES ('Ravi', '10-A');
SELECT @@ROWCOUNT;  -- Returns 1

SELECT * FROM Student WHERE Name = 'Ravi';
-- Returns: 0 rows (Why?)

-- Debug Step 1: Check exact values
SELECT * FROM Student WHERE Name = 'Ravi';  -- No matches

-- Debug Step 2: Check with wildcards
SELECT * FROM Student WHERE Name LIKE '%Ravi%';  -- Still nothing

-- Debug Step 3: Check all students
SELECT * FROM Student;  -- 'Ravi' IS there! But WHERE Name = 'Ravi' doesn't match

-- Debug Step 4: Check data type/length
SELECT NAME, LEN(Name), DATALENGTH(Name) FROM Student WHERE StudentId = SCOPE_IDENTITY();
-- Aha! Name = 'Ravi ' (has trailing space, length 5 not 4)

-- Root Cause: Trailing space in data
-- Solution:
TRIM(Name) = 'Ravi'
-- Or clean data:
UPDATE Student SET Name = TRIM(Name);

Debugging approach:

1. Verify insert count
2. Simple query (exact match)
3. Wildcard query
4. Check data type/format
5. Inspect actual values
6. Apply fix

Scenario 2: "Slow Query" Performance Issue

-- Symptom: Query takes 30 seconds
SELECT s.Name, COUNT(r.ExamResultId) AS Exams
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
GROUP BY s.StudentId, s.Name;
-- Slow!

-- Debug Step 1: Enable statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT ... (same query);

-- Output shows: 500,000 logical reads (too many!)
-- Index missing on StudentId in ExamResult

-- Debug Step 2: Check execution plan
-- Shows Table Scan on ExamResult (red flag)

-- Debug Step 3: Add missing index
CREATE INDEX IX_ExamResult_StudentId ON ExamResult(StudentId);

-- Debug Step 4: Re-run, compare
SET STATISTICS IO ON;
SELECT ... (same query);
-- Output: 5,000 logical reads (100x faster!)

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Performance debugging:

1. Enable statistics (IO, TIME)
2. Check execution plan (scans vs seeks)
3. Look for missing indexes
4. Add index
5. Re-test, compare metrics

Scenario 3: "Deadlock" in Payment Processing

-- Symptom: Random "Deadlock" errors in sp_ProcessFeePayment

-- Debug Step 1: Check deadlock graph
-- SQL Server logs deadlocks to error log

-- Check recent deadlocks:
SELECT * FROM sys.dm_exec_query_stats
WHERE query_hash = (SELECT query_hash FROM ... WHERE text LIKE '%ProcessFeePayment%');

-- Debug Step 2: Identify conflicting locks
-- Scenario: 
-- Process A: Lock Student -> Lock FeeAccount
-- Process B: Lock FeeAccount -> Lock Student (circular wait)

-- Debug Step 3: Reproduce issue
-- Run sp_ProcessFeePayment for Student1 and Student2 simultaneously

-- Debug Step 4: Analyze lock order
-- Process A locks Student(1), waiting for FeeAccount(1)
-- Process B locks FeeAccount(1), waiting for Student(1)

-- Root Cause: Inconsistent lock order

-- Solution: Always lock in same order
CREATE PROCEDURE sp_ProcessFeePayment_Fixed
    @StudentId INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        -- ALWAYS lock Student first
        SELECT * FROM Student WITH (XLOCK) WHERE StudentId = @StudentId;
        
        -- THEN lock FeeAccount
        SELECT * FROM FeeAccount WITH (XLOCK) WHERE StudentId = @StudentId;
        
        -- Rest of logic...
        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END;

Deadlock resolution:

1. Identify conflicting processes
2. Analyze lock order in each
3. Find circular dependency
4. Fix: Always lock in same order

Scenario 4: "Data Corruption" Check

-- Symptom: Student count inconsistent (StudentCount in Class doesn't match)

-- Debug Step 1: Verify data integrity
SELECT c.ClassName, c.StudentCount, COUNT(s.StudentId) AS ActualCount
FROM Class c
LEFT JOIN Student s ON c.ClassName = s.ClassName
GROUP BY c.ClassName, c.StudentCount
HAVING COUNT(s.StudentId) != c.StudentCount;
-- Shows mismatches

-- Debug Step 2: Identify orphaned records
SELECT * FROM ExamResult
WHERE StudentId NOT IN (SELECT StudentId FROM Student);
-- Shows exam results for deleted students

-- Debug Step 3: Identify NULL values where not allowed
SELECT * FROM Student WHERE Name IS NULL;

-- Root Cause: Trigger maintaining StudentCount failed
-- Exam results inserted for student that was deleted

-- Solution:
-- Add ON DELETE CASCADE to foreign key
ALTER TABLE ExamResult
DROP CONSTRAINT FK_ExamResult_Student;

ALTER TABLE ExamResult
ADD CONSTRAINT FK_ExamResult_Student
FOREIGN KEY (StudentId) REFERENCES Student(StudentId)
ON DELETE CASCADE;  -- Auto-delete exams when student deleted

-- Or fix data
DELETE FROM ExamResult WHERE StudentId NOT IN (SELECT StudentId FROM Student);

-- Repair counts
UPDATE Class SET StudentCount = (SELECT COUNT(*) FROM Student WHERE ClassName = Class.ClassName);

Data corruption debugging:

1. Compare computed vs stored values
2. Check for orphaned records
3. Verify constraints
4. Fix relationships
5. Repair data or add constraints

Scenario 5: "Transaction Timeout" Issue

-- Symptom: "Transaction timeout" error randomly

-- Debug Step 1: Check transaction duration
SELECT 
    r.session_id,
    r.status,
    r.command,
    DATEDIFF(SECOND, s.login_time, GETDATE()) AS SessionDurationSec,
    st.text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50;

-- Shows: sp_ProcessFeePayment running 300 seconds (!)

-- Debug Step 2: Check for blocking
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0;

-- Shows: Session 55 blocked by Session 52
-- Session 52 is sp_ProcessFeePayment

-- Root Cause: Long transaction holding locks, blocking others

-- Solution 1: Optimize query (add index)
-- Solution 2: Break into smaller transactions
-- Solution 3: Increase timeout (last resort)

CREATE PROCEDURE sp_ProcessFeePayment_Optimized
    @StudentId INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    DECLARE @FeeAccountId INT;
    
    -- Short transaction 1: Get data
    SELECT @FeeAccountId = FeeAccountId FROM FeeAccount
    WHERE StudentId = @StudentId AND Status != 'Paid';
    
    -- Processing (NO locks held)
    IF @FeeAccountId IS NULL
        THROW 50001, 'No pending fees', 1;
    
    -- Short transaction 2: Update
    BEGIN TRANSACTION;
    UPDATE FeePayment SET ... WHERE FeeAccountId = @FeeAccountId;
    COMMIT;
    
    -- Result: Minimal lock time
END;

Timeout debugging:

1. Identify long-running queries
2. Check for blocking
3. Optimize query or break into smaller transactions
4. Monitor lock duration

---

🎯 Q1: How do I debug 'no results' issue?

1. Verify insert/update happened
2. Simple SELECT (exact match)
3. Wildcard SELECT
4. Check data format (spaces, type)
5. Compare actual vs expected values

Use TRIM(), check DATALENGTH.

🎯 Q2: How do I diagnose slow queries?

SET STATISTICS IO ON;
SELECT ...; -- Check logical reads
-- Table Scan: Need index
-- High reads: Optimize query or add index

Enable stats, check execution plan, add indexes.

🎯 Q3: How do I debug deadlocks?

1. Identify conflicting processes
2. Analyze lock order
3. Find circular dependency
4. Fix: Lock in consistent order

Use XLOCK, always same sequence.

🎯 Q4: How do I detect data corruption?

-- Compare computed vs stored:
SELECT c.Count, (SELECT COUNT(*) FROM Table) AS Actual
WHERE c.Count != (SELECT COUNT(*) ...);

-- Check for orphans:
SELECT * FROM Child WHERE Parent NOT IN (SELECT Parent);

Validate referential integrity.

🎯 Q5: How do I fix transaction timeouts?

1. Break into smaller transactions (reduce lock time)
2. Add index (speed up)
3. Optimize query (fewer rows)
4. Last resort: Increase timeout

Minimize lock duration.

🎯 Q6: How do I monitor blocking?

SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
-- Shows blocked sessions and blockers

Kill blocker or wait. Add index to speed query.

---

🤖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 SQL Server troubleshooting and debugging techniques. Try these prompts:

• "Show debugging approach: data inserted but SELECT returns nothing"
• "How do I diagnose slow query performance?"
• "How do I detect and fix deadlocks?"
• "How do I detect data corruption (orphaned records)?"
• "Quiz me: 5 questions about debugging, diagnosis, root cause"

💡 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

40. Performance Optimization Case Studies ->