Why Tune?
Performance tuning ensures that your SQL Server workloads run efficiently, reducing latency, conserving resources, and enhancing user experience.
Key Concepts
- Index fragmentation
- Query execution cost
- Memory grants and CPU usage
Clustered Indexes
Use clustered indexes for tables where data retrieval is often range‑based.
CREATE CLUSTERED INDEX IX_Customer_ID ON dbo.Customers (CustomerID);
Non‑Clustered Indexes
Ideal for lookup queries on columns that are not part of the primary key.
CREATE NONCLUSTERED INDEX IX_Order_Date ON dbo.Orders (OrderDate);
Covering Indexes
A covering index includes all columns required by a query, eliminating lookups.
CREATE NONCLUSTERED INDEX IX_Order_Cover ON dbo.Orders (OrderDate) INCLUDE (OrderID, TotalAmount);
Reading Execution Plans
Execution plans show how the SQL engine processes a query. Look for scans, seeks, and costly operators.
Update Statistics
Keeping statistics up‑to‑date helps the optimizer choose optimal plans.
UPDATE STATISTICS dbo.Customers;
Parameter Sniffing
When a cached plan performs poorly for subsequent parameter values, consider using OPTIMIZE FOR or RECOMPILE hints.
SELECT * FROM dbo.Orders WHERE OrderDate = @date OPTION (RECOMPILE);