1. Overview

Performance tuning can be broken down into three core areas:

• Hardware & Server Configuration – CPU, memory, storage, and network.
• Database Design – schema, data types, indexes, and partitioning.
• Query Execution – plan selection, statistics, and runtime behavior.

2. Indexing Strategies

2.1 Choose the Right Index Type

• Clustered Index – Ideal for range queries and primary key enforcement.
• Non‑Clustered Index – Best for lookups and covering queries.
• Columnstore Index – Excellent for analytics and large‑scale aggregation.
• Filtered Index – Useful for sparse data sets.

2.2 Common Pitfalls

• Avoid over‑indexing – each index adds write overhead.
• Ensure index columns are in order of selectivity.
• Re‑evaluate indexes after major schema changes.

2.3 Example: Creating a Covering Index

CREATE NONCLUSTERED INDEX IX_OrderDetails_Covering
ON dbo.OrderDetails (OrderID, ProductID)
INCLUDE (Quantity, UnitPrice);

3. Query Optimisation

3.1 Analyze Execution Plans

Use SET SHOWPLAN_XML ON or the SSMS graphical plan to spot scans, spills, and missing indexes.

3.2 Rewrite Inefficient Queries

-- Bad: Using a scalar function in a WHERE clause
SELECT *
FROM dbo.Sales
WHERE dbo.GetFiscalYear(SaleDate) = 2024;

-- Good: Pre‑compute the value
SELECT *
FROM dbo.Sales
WHERE SaleDate >= '2024-01-01' AND SaleDate < '2025-01-01';

3.3 Parameter Sniffing

If a plan works well for one parameter but poorly for another, consider using OPTION (RECOMPILE) or local variables.

4. Statistics Management

Statistics guide the optimizer in estimating row counts. Keep them up to date:

-- Update statistics for a single table
UPDATE STATISTICS dbo.Customers WITH FULLSCAN;

-- Auto‑create and auto‑update (recommended)
ALTER DATABASE [YourDB] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [YourDB] SET AUTO_UPDATE_STATISTICS ON;

5. Memory & Config Settings

• max server memory – Reserve enough memory for the OS; typically 4‑6 GB for the host.
• cost threshold for parallelism – Set to 25–30 on modern servers.
• max degree of parallelism (MAXDOP) – Align with NUMA nodes (e.g., 8 for an 8‑core socket).

EXEC sys.sp_configure N'max server memory (MB)', 24576;
RECONFIGURE WITH OVERRIDE;

EXEC sys.sp_configure N'cost threshold for parallelism', 30;
RECONFIGURE WITH OVERRIDE;

6. Monitoring & Diagnostics

Leverage built‑in DMVs and Extended Events for real‑time insights:

-- Top queries by total CPU time
SELECT TOP 10
    qs.total_worker_time/1000 AS TotalCPU_ms,
    qs.execution_count,
    qs.total_elapsed_time/1000 AS TotalDuration_ms,
    SUBSTRING(qt.text,qs.statement_start_offset/2,
        (CASE WHEN qs.statement_end_offset = -1
              THEN LEN(CONVERT(NVARCHAR(MAX),qt.text)) * 2
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC;

7. Further Reading

• SQL Server Indexing Fundamentals
• Reading Execution Plans
• Performance Tools Overview
• Microsoft Docs – Performance