Bits of .NET

📊 Key Takeaways: SQL Server caches execution plans. Learn how to maximize plan reuse and avoid compilation overhead.

Query compilation is expensive. Plan reuse saves that cost.

🚀 Check Plan Reuse

-- ✅ Check plan reuse statistics
SELECT 
    cacheobjtype,
    objtype,
    usecounts,
    size_in_bytes,
    text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY usecounts DESC;

-- ✅ Check plan reuse rate
SELECT 
    CAST(usecounts AS DECIMAL) / 
    (SELECT COUNT(*) FROM sys.dm_exec_cached_plans) AS ReuseRate
FROM sys.dm_exec_cached_plans;

💡 Optimize for Plan Reuse

-- ✅ Use parameterized queries (reused plan)
CREATE PROCEDURE GetOrdersByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;

-- ✅ Use sp_executesql (reused plan)
EXEC sp_executesql 
    N'SELECT * FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate',
    N'@StartDate DATE, @EndDate DATE',
    @StartDate = '2024-01-01', @EndDate = '2024-01-31';

-- ❌ Avoid: Different values cause recompilation
SELECT * FROM Orders WHERE OrderDate = '2024-01-01';
SELECT * FROM Orders WHERE OrderDate = '2024-01-02'; -- New plan!

💡 Pro Tip: Force Plan Reuse

-- ✅ Force query to use parameterized plan
ALTER DATABASE MyDatabase
SET PARAMETERIZATION FORCED;

-- ✅ Keep plans in cache
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
OPTION (KEEPFIXED PLAN);

-- ✅ Optimize for ad-hoc workloads
ALTER DATABASE MyDatabase
SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;