Optimising a SQL Server database for performance involves a combination of design best practices, query tuning, index management, and system-level configurations. Here’s a structured approach to help you get started:

1. Database Design Optimisation

• Normalise to reduce redundancy, but de-normalise selectively for performance.
• Use appropriate data types: avoid over-allocating space (e.g., use INT instead of BIGINT if possible).
• Avoid nullable columns in indexes where possible.
• Use constraints (PK, FK, CHECK) to enforce data integrity and improve query plans.

2. Indexing Strategy

• Create clustered indexes on frequently queried columns (usually the PK).
• Use non-clustered indexes for search/filter columns.
• Include covering indexes for queries that select multiple columns.
• Regularly review missing index DMVs:

3. Query Tuning

• Use SET STATISTICS IO, TIME ON to analyse query cost.
• Avoid SELECT *: specify only needed columns.
• Use parameterised queries to improve plan reuse.
• Watch out for implicit conversions and non-SARGable conditions (e.g., WHERE YEAR(date_column) = 2025).

4. Maintenance Tasks

• Update statistics regularly:
• Rebuild or reorganise indexes based on fragmentation:
• Clean up unused indexes and monitor index usage.

5. Monitor Performance

• Use Query Store to track query performance over time.
• Monitor wait stats:
• Use Extended Events or Performance Monitor for deeper insights.

6. Server-Level Tuning

• Configure max memory, CPU affinity, and tempdb settings.
• Make sure I/O subsystem is fast and reliable.
• Use multiple tempdb files to reduce contention.

7. Security and Access

• Limit permissions to reduce unnecessary locks.
• Use connection pooling and efficient session management.

Whether to rebuild or reorganise an index depends on the level of fragmentation and the performance goals. Rebuilding is often the better choice, though not always necessary.

Rebuild vs Reorganise: Key Differences

Feature	Rebuild	Reorganise
Fragmentation Level	Best for >30% fragmentation	Best for 5–30% fragmentation
Operation Type	Heavy: drops and recreates index	Lightweight: defrags leaf level
Locks	Uses schema modification lock	Uses row-level locks
Statistics	Automatically updated	Not updated
Page Allocation	Reclaims space, resets page structure	Does not reclaim space
Online Option	Supported (Enterprise Edition)	Always online
Performance Impact	Higher (CPU, I/O)	Lower

Why Rebuild Is Often Better

• Thorough Defragmentation
  • Rebuild completely recreates the index, eliminating all fragmentation.
• Statistics Update
  • Automatically updates statistics, which helps the query optimiser make better decisions.
• Space Reclamation
  • Rebuild can reclaim unused space and reset fill factors.
• Improved Query Performance
  • Especially beneficial for heavily fragmented indexes or large tables.

When Reorganise Is Preferable

• When system resources are limited.
• During business hours (less disruptive).
• For indexes with moderate fragmentation.
• On Standard Edition, where online rebuilds aren’t available.

From my SQL Server DBA handbook.