Bits of .NET

Your application is slow, and you suspect the database. Execution plans reveal exactly what SQL Server is doing and where the bottlenecks are.

1. Capture Actual Execution Plan:

-- ✅ Capture actual execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run your query
SELECT 
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    p.ProductName,
    od.Quantity,
    od.UnitPrice
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= '2024-01-01'
  AND o.OrderDate < '2024-02-01'
  AND o.Status = 'Shipped'
  AND c.Country = 'USA'
  AND p.Category = 'Electronics'
ORDER BY o.OrderDate DESC;

-- Look at output:
-- (1) Actual execution plan (in SSMS, Execution Plan tab)
-- (2) STATISTICS IO: Logical reads, physical reads
-- (3) STATISTICS TIME: CPU time, elapsed time

2. Understanding the Execution Plan:

Execution Plan Operators (Most Common):

1. Table Scan / Clustered Index Scan (??? Worst)
   - Reading entire table/index
   - Cost: High (proportional to table size)
   - Fix: Add WHERE clause or index

2. Index Seek (✅ Best)
   - Using index to find specific rows
   - Cost: Low (proportional to result set)
   - Good! Keep it.

3. Key Lookup / RID Lookup (⚠️ Moderate)
   - Getting extra columns after index seek
   - Cost: Low to Medium
   - Fix: Use covered index (include columns)

4. Nested Loops (⚠️ Moderate)
   - Loop join for small tables
   - Cost: Low (small tables)

5. Hash Match (⚠️ Moderate)
   - Hash join for large tables
   - Cost: Medium

6. Merge Join (✅ Best)
   - Merge sorted inputs
   - Cost: Medium but efficient

7. Sort (⚠️ Moderate)
   - Sorting operation
   - Cost: Medium to High
   - Fix: Use index to avoid sort

8. Filter (⚠️ Moderate)
   - Predicate filtering
   - Cost: Low to Medium

9. Compute Scalar (✅ Low)
   - Computed values
   - Cost: Low

3. Identify Missing Indexes:

-- Missing Index Recommendation
SELECT 
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Impact,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.user_seeks,
    migs.user_scans,
    'CREATE INDEX idx_' + 
        REPLACE(REPLACE(REPLACE(mid.statement, ']', ''), '[', ''), '.', '_') + 
        '_' + 
        CAST(migs.last_user_seek AS VARCHAR(50)) + 
        ' ON ' + mid.statement + 
        ' (' + ISNULL(mid.equality_columns,'') + 
        CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + 
        ISNULL(mid.inequality_columns,'') + 
        ')' + 
        ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS CreateIndexStatement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE database_id = DB_ID()
ORDER BY Impact DESC;

4. Query Rewriting for Performance:

-- ❌ Bad: Functions on indexed columns
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 1;
-- Index on OrderDate cannot be used

-- ✅ Good: Range search
SELECT *
FROM Orders
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2024-02-01';
-- Index on OrderDate will be used

-- ❌ Bad: Leading wildcard
SELECT *
FROM Customers
WHERE CustomerName LIKE '%Smith%';
-- Index cannot be used efficiently

-- ✅ Good: Full text search
SELECT *
FROM Customers
WHERE CONTAINS(CustomerName, 'Smith');
-- Full text index used

-- ❌ Bad: OR conditions
SELECT *
FROM Products
WHERE Category = 'Electronics'
   OR Category = 'Computers';
-- May use table scan

-- ✅ Good: IN list or UNION
SELECT *
FROM Products
WHERE Category IN ('Electronics', 'Computers');
-- OR:
SELECT * FROM Products WHERE Category = 'Electronics'
UNION
SELECT * FROM Products WHERE Category = 'Computers';

-- ❌ Bad: Distinct on large table
SELECT DISTINCT CustomerID, OrderDate
FROM Orders;
-- Heavy sort operation

-- ✅ Good: Use EXISTS or GROUP BY
SELECT CustomerID, OrderDate
FROM Orders
GROUP BY CustomerID, OrderDate;

-- ❌ Bad: Subquery with large result
SELECT *
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'USA');
-- May be inefficient

-- ✅ Good: JOIN instead
SELECT o.*
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.Country = 'USA';

5. Index Design Best Practices:

-- 1. Clustered Index (Primary Key)
CREATE CLUSTERED INDEX PK_Orders ON Orders(OrderID);
-- ✅ Good: Unique, narrow, ever-increasing

-- 2. Non-Clustered Index
CREATE INDEX IX_Orders_OrderDate_Status 
ON Orders(OrderDate, Status)
INCLUDE (CustomerID, TotalAmount);
-- Covered index - includes all needed columns

-- 3. Filtered Index (for specific queries)
CREATE INDEX IX_Orders_Recent 
ON Orders(OrderDate)
WHERE OrderDate > '2024-01-01';
-- Smaller, faster for recent data

-- 4. Include columns for SELECT
CREATE INDEX IX_Orders_Customer_Date
ON Orders(CustomerID, OrderDate)
INCLUDE (Status, TotalAmount, ShippingAddress);
-- All query columns are in the index

-- 5. Index on computed column
ALTER TABLE Orders
ADD OrderMonth AS MONTH(OrderDate) PERSISTED;

CREATE INDEX IX_Orders_Month 
ON Orders(OrderMonth);

6. Analyze Query Cost:

-- Get query execution statistics
DECLARE @queryHash VARCHAR(100);

SELECT 
    qs.sql_handle,
    qs.plan_handle,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE execution_count > 100  -- Frequently executed
ORDER BY total_worker_time DESC;  -- Most CPU

-- Top 10 queries by cost
SELECT TOP 10
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.total_logical_reads,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu,
    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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

7. Parameter Sniffing – Hidden Performance Killer:

-- ❌ Parameter sniffing problem
CREATE PROCEDURE GetOrdersByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;

-- First call: 1 row (fast plan)
EXEC GetOrdersByDate '2024-01-01', '2024-01-01';
-- Plan cached for 1 row (Nested Loops)

-- Second call: 1,000,000 rows (slow!)
EXEC GetOrdersByDate '2024-01-01', '2024-12-31';
-- Uses same Nested Loops plan → Performance disaster!

-- ✅ Fix 1: OPTIMIZE FOR UNKNOWN
CREATE PROCEDURE GetOrdersByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    OPTION (OPTIMIZE FOR UNKNOWN);
END;

-- ✅ Fix 2: Query Hints
SELECT *
FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
OPTION (RECOMPILE);  -- Recompile every time

-- ✅ Fix 3: Local variables (no sniffing)
CREATE PROCEDURE GetOrdersByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    DECLARE @s DATE = @StartDate;
    DECLARE @e DATE = @EndDate;
    
    SELECT *
    FROM Orders
    WHERE OrderDate BETWEEN @s AND @e;
END;

8. Monitoring and Alerting:

-- Create alert for long-running queries
CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.username)
    WHERE ([duration] >= 10000000)  -- 10 seconds
)
ADD TARGET package0.event_file(SET filename = 'LongRunningQueries.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS);
-- Start the event session
ALTER EVENT SESSION [LongRunningQueries] ON SERVER STATE = START;

-- Query the event data
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [Time],
event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [Duration(ms)],
event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') AS[CPU(ms)],
event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS[Reads],
event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS[Writes],
event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS[SQL],
event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(100)') AS[User]
FROM(
SELECT CAST(event_data AS XML) AS event_data

FROM sys.fn_xe_file_target_read_file('LongRunningQueries*.xel', NULL, NULL, NULL)
) AS events
ORDER BY[Time] DESC;