লগইন ফ্রি শুরু করুন

MS SQL Performance Tuning

লেসন ৫০ / ৫০

Performance Tuning Workflow

  1. Slow query identify
  2. Execution plan analyze
  3. Index add/optimize
  4. Query rewrite
  5. Stats update
  6. Hardware/config tune

Slow Query Find

-- Top 10 slowest
SELECT TOP 10
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(qt.text)
              ELSE qs.statement_end_offset/2 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) qt
ORDER BY avg_elapsed DESC;

Execution Plan ⭐

-- SSMS-এ Ctrl+M / Ctrl+L
-- দেখুন:
-- 🔴 Table Scan / Clustered Index Scan = ধীর
-- 🟢 Index Seek = দ্রুত
-- ⚠ Key Lookup = include column add করুন
-- ⚠ Hash Match = sort/index missing

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Products WHERE Price > 1000;
-- Logical reads, CPU time দেখুন

Indexing Strategy

-- 1. Clustered (1 per table) — physical sort
CREATE CLUSTERED INDEX IX_Products_Id ON Products(Id);

-- 2. Non-clustered + Include
CREATE NONCLUSTERED INDEX IX_Products_Category
ON Products(Category)
INCLUDE (Name, Price, Stock);    -- Key Lookup এড়ান

-- 3. Composite (column order important!)
CREATE INDEX IX_Orders_CustDate ON Orders(CustomerId, OrderDate DESC);
-- WHERE CustomerId = ? AND OrderDate >= ? — কাজ করে
-- WHERE OrderDate >= ? — কাজ করবে না (left-most)

-- 4. Filtered (partial)
CREATE INDEX IX_Orders_Pending ON Orders(CreatedAt)
WHERE Status = 'Pending';

-- 5. Columnstore (analytics)
CREATE COLUMNSTORE INDEX IX_Sales_CS ON Sales(Date, Amount, Region);

Query Rewrite Tips

-- ❌ SELECT * — সব column
-- ✅ SELECT Id, Name — শুধু প্রয়োজন

-- ❌ WHERE YEAR(OrderDate) = 2024 — index unusable
-- ✅ WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'

-- ❌ WHERE Name LIKE '%abc%' — full scan
-- ✅ WHERE Name LIKE 'abc%' — index usable

-- ❌ Function on column
WHERE UPPER(Email) = 'X@Y.COM'
-- ✅ Computed column or store normalized

-- ❌ NOT IN with subquery
-- ✅ LEFT JOIN ... WHERE x.Id IS NULL or NOT EXISTS

-- ❌ DISTINCT often hides duplicate JOIN bug
-- ✅ Fix the JOIN condition

Statistics

-- Auto-update on
ALTER DATABASE Shop SET AUTO_UPDATE_STATISTICS ON;

-- Manual update
UPDATE STATISTICS Products WITH FULLSCAN;
EXEC sp_updatestats;

-- View stats
DBCC SHOW_STATISTICS('Products', 'IX_Products_Category');

Partitioning (Large Tables)

-- 1. Partition function (monthly)
CREATE PARTITION FUNCTION pf_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES ('2024-01-01', '2024-02-01', '2024-03-01', ...);

-- 2. Filegroup mapping
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate ALL TO ([PRIMARY]);

-- 3. Apply
CREATE TABLE Orders (
    Id INT IDENTITY,
    OrderDate DATE,
    Total DECIMAL(18,2)
) ON ps_OrderDate(OrderDate);

-- Old partition switch out
ALTER TABLE Orders SWITCH PARTITION 1 TO Orders_Archive;

Other Optimizations

  • Parameter Sniffing — OPTION (RECOMPILE) or OPTIMIZE FOR
  • Read Committed Snapshot — reduce blocking
  • tempdb tuning — multiple files
  • Memory-Optimized Tables (In-Memory OLTP)
  • Query Store — track regression
  • Resource Governor — workload limit

Monitoring Tools

  • SQL Server Profiler / Extended Events
  • Activity Monitor
  • Query Store reports
  • Brent Ozar's sp_BlitzFirst, sp_BlitzIndex
  • Azure SQL Performance Insights
← আগের লেসনMicroservices Architecture কোর্স শেষ! 🎉আরও কোর্স দেখুন