🟦 SQL Server – Architecture, T-SQL, Administration & Cloud

Upgrade mindset

• Never upgrade blind: capture workload, top queries, wait stats, indexes, jobs, and maintenance plans first.
• Compatibility level matters: engine version and database compatibility level are related but different.
• Query Store is your friend: it helps detect regressions and force known good plans when needed.
• Test restores: an upgrade plan without restore validation is not serious.

Pre-upgrade checklist

1. Full backup + restore test
                            2. DBCC CHECKDB clean result
                            3. SQL Agent jobs inventory
                            4. Linked servers inventory
                            5. CLR / Service Broker / replication check
                            6. Compatibility level review
                            7. Top SQL baseline from Query Store
                            8. Wait stats baseline
                            9. HA / DR failover test
                            10. Rollback plan validated

OLTP workload:
                            - many small transactions
                            - low latency required
                            - strict locking discipline
                            - indexes must serve selective lookups
                            - transaction log performance is critical

                            Reporting workload:
                            - fewer but heavier queries
                            - scans, aggregations, joins
                            - columnstore can help
                            - replicas or ETL pipelines should isolate load

                            Mixed workload:
                            - most dangerous pattern
                            - reporting can destroy OLTP latency
                            - solution: replicas, warehouse, caching, query governance

Simple workload diagram

             +----------------+
                            Users -----> |  Application   |
                            +--------+-------+
                            |
                            v
                            +---------------+
                            | SQL Server    |  OLTP path
                            | Primary       |
                            +-------+-------+
                            |
                            +--------------+----------------+
                            |                               |
                            v                               v
                            +--------------+                +--------------+
                            | HA Replica   |                | ETL / CDC     |
                            | Read routing |                | Warehouse     |
                            +--------------+                +--------------+
                            |                               |
                            v                               v
                            Reporting users                Power BI / Fabric

Core components

Read/write flow

SELECT flow:
                            1. parse T-SQL
                            2. compile or reuse plan
                            3. read pages from buffer pool
                            4. if page missing, read from data file
                            5. execute operators
                            6. return result set

                            UPDATE flow:
                            1. find target rows
                            2. acquire locks
                            3. modify pages in memory
                            4. write log records to transaction log
                            5. COMMIT waits for log hardening
                            6. dirty pages are written later by checkpoint/lazy writer

When SQL Server is a very good choice

• The company already uses Microsoft identity, Windows Server, Azure, Power BI, and .NET.
• The team wants strong GUI tooling and fast operational onboarding.
• The workload is classic OLTP with reporting and business procedures.
• The DBA team wants Query Store, graphical plans, Agent jobs, and a very readable diagnostic workflow.

When SQL Server may not be ideal

• The organization wants to avoid commercial licensing completely.
• The system requires deep open-source extensibility or PostgreSQL-specific extensions.
• The architecture is designed around cloud-native managed open-source services.
• The workload is mostly document, graph, search, or event streaming rather than relational OLTP.

Example 1: find the current database version

SELECT
                            @@VERSION AS sql_server_version;

                            SELECT
                            SERVERPROPERTY('ProductVersion') AS product_version,
                            SERVERPROPERTY('ProductLevel')   AS product_level,
                            SERVERPROPERTY('Edition')        AS edition,
                            SERVERPROPERTY('EngineEdition')  AS engine_edition;

Example 2: list database files

SELECT
                            DB_NAME(database_id) AS database_name,
                            type_desc,
                            name AS logical_name,
                            physical_name,
                            size * 8 / 1024 AS size_mb,
                            growth,
                            is_percent_growth
                            FROM sys.master_files
                            ORDER BY database_name, type_desc;

Example 3: top active requests

SELECT
                            r.session_id,
                            r.status,
                            r.command,
                            r.cpu_time,
                            r.total_elapsed_time,
                            r.wait_type,
                            r.blocking_session_id,
                            DB_NAME(r.database_id) AS database_name,
                            t.text AS sql_text
                            FROM sys.dm_exec_requests r
                            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                            ORDER BY r.total_elapsed_time DESC;

Example 4: index usage snapshot

SELECT
                            OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
                            OBJECT_NAME(i.object_id) AS table_name,
                            i.name AS index_name,
                            s.user_seeks,
                            s.user_scans,
                            s.user_lookups,
                            s.user_updates
                            FROM sys.indexes i
                            LEFT JOIN sys.dm_db_index_usage_stats s
                            ON s.object_id = i.object_id
                            AND s.index_id = i.index_id
                            AND s.database_id = DB_ID()
                            WHERE i.object_id > 100
                            ORDER BY s.user_updates DESC, s.user_seeks ASC;

Example 5: database backup history

SELECT TOP (50)
                            bs.database_name,
                            bs.type AS backup_type,
                            bs.backup_start_date,
                            bs.backup_finish_date,
                            DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS duration_sec,
                            bs.backup_size / 1024 / 1024 AS backup_size_mb
                            FROM msdb.dbo.backupset bs
                            ORDER BY bs.backup_finish_date DESC;

Example 6: quick blocking check

SELECT
                            session_id,
                            blocking_session_id,
                            wait_type,
                            wait_time,
                            wait_resource,
                            status,
                            command
                            FROM sys.dm_exec_requests
                            WHERE blocking_session_id <> 0
                            ORDER BY wait_time DESC;

Reality from production

Most real incidents are boring:
                            - disk full
                            - log not backed up
                            - bad deployment changed a plan
                            - missing index after new feature
                            - reporting query hits primary database
                            - long transaction blocks users
                            - SQL Agent job failed silently
                            - backup exists but restore was never tested

What a strong DBA does

A strong DBA:
                            1. baselines normal behavior
                            2. automates health checks
                            3. tests restores
                            4. reviews top queries
                            5. controls index growth
                            6. validates HA failover
                            7. documents runbooks
                            8. watches capacity trends
                            9. explains incidents with facts
                            10. reduces operational surprises

SQL Server 2019: the strong modern baseline

SQL Server 2019 remains very relevant in production environments. It is stable, well-known by DBA teams, widely supported by vendors, and modern enough to include several performance and recovery improvements that changed daily operations.

Main reasons to care

• Intelligent Query Processing: multiple optimizer improvements can improve existing workloads with minimal code changes.
• Batch mode on rowstore: analytical-style processing can benefit even without columnstore indexes in some scenarios.
• Table variable deferred compilation: better cardinality estimates for table variables compared with older behavior.
• Scalar UDF inlining: some scalar functions can be transformed to reduce row-by-row execution cost.
• Memory grant feedback: SQL Server can adjust memory grants after executions to reduce spills or waste.
• Accelerated Database Recovery: crash recovery and rollback behavior becomes more predictable, especially with long transactions.
• Linux maturity: SQL Server is no longer only a Windows Server story.

Good production profile

SQL Server 2019 is a strong choice when:
                            - the application vendor supports it
                            - the team wants a stable enterprise release
                            - the company migrates from 2012, 2014 or 2016
                            - the environment is mostly Windows and .NET
                            - the workload is classic OLTP plus reporting
                            - the team needs modern Query Store and IQP features
                            - production risk must remain moderate

Watch points

Do not assume everything is automatic:
                            - IQP depends on compatibility level and feature scope
                            - Query regressions can still happen
                            - tempdb design remains critical
                            - ADR changes recovery internals, so test it
                            - Linux deployment requires OS-specific skills
                            - vendor applications may restrict compatibility level

SQL Server 2022: Query Store and hybrid maturity

SQL Server 2022 is especially important because it strengthens the DBA control loop: observe performance, identify regressions, influence behavior, and stabilize workloads without necessarily changing application code.

Main themes

• Query Store is more central: new databases have Query Store enabled by default in many scenarios.
• Query Store hints: influence query behavior without editing application SQL text.
• Parameter Sensitive Plan optimization: multiple plan variants can be used for different parameter cardinality patterns.
• Ledger: tamper-evident history for compliance and audit scenarios.
• Azure integration: stronger hybrid cloud story with managed services and cloud-connected features.
• ADR improvements: recovery and transaction behavior continue to mature.

What PSP changes

Old parameter sniffing problem:
                            - first execution uses parameter A
                            - optimizer builds a plan for A
                            - cached plan is reused for parameter B
                            - B has very different selectivity
                            - performance collapses

                            SQL Server 2022 PSP idea:
                            - detect sensitive parameter patterns
                            - keep multiple useful plan variants
                            - choose a better variant for future executions
                            - reduce some classic parameter sniffing pain

Query Store mental model

Query Store captures:
                            - query text
                            - plan history
                            - runtime statistics
                            - wait statistics
                            - regressions over time

                            DBA uses it to:
                            - compare before/after upgrade
                            - detect plan regression
                            - force or guide a better plan
                            - produce evidence for tuning decisions

SQL Server 2025: AI-ready database direction

SQL Server 2025 represents a strategic shift. The relational database is no longer positioned only as a transaction engine. It becomes closer to modern AI and semantic search applications by adding native vector-oriented capabilities and stronger developer patterns.

Core ideas

• Vector data type: store embeddings more naturally inside the SQL database engine.
• Vector search: support similarity search patterns closer to relational data.
• AI integration: reduce the distance between business data and AI-assisted retrieval.
• Hybrid and cloud direction: continue the path toward Azure, Fabric, Arc and managed scenarios.
• Developer value: better fit for applications that combine structured filters and semantic search.

Why vector support matters

Classic search:
                            - exact text match
                            - LIKE predicates
                            - full-text search
                            - filters and keywords

                            Vector search:
                            - semantic similarity
                            - embeddings represent meaning
                            - useful for AI search, recommendations, RAG
                            - can combine:
                            business filters
                            security filters
                            relational joins
                            semantic similarity

Prudent production stance

Before adopting 2025 in production:
                            1. confirm vendor support
                            2. validate drivers and tooling
                            3. test compatibility level behavior
                            4. benchmark top queries
                            5. test Query Store baselines
                            6. test HA and DR
                            7. test backup and restore
                            8. validate AI/vector use cases separately
                            9. document rollback
                            10. avoid novelty-driven migration

Why it matters by version

Good Query Store setup

ALTER DATABASE YourDatabaseName
                            SET QUERY_STORE = ON;

                            ALTER DATABASE YourDatabaseName
                            SET QUERY_STORE (
                            OPERATION_MODE = READ_WRITE,
                            CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
                            DATA_FLUSH_INTERVAL_SECONDS = 900,
                            INTERVAL_LENGTH_MINUTES = 60,
                            MAX_STORAGE_SIZE_MB = 2048,
                            QUERY_CAPTURE_MODE = AUTO
                            );

What Query Store gives you

For each important query:
                            - query text
                            - query id
                            - plan id
                            - execution count
                            - duration
                            - CPU
                            - logical reads
                            - memory consumption
                            - waits
                            - plan changes over time

                            This allows:
                            - before/after upgrade comparison
                            - regression detection
                            - plan forcing if justified
                            - evidence-based tuning report

Operational anti-pattern

Bad:
                            - enable Query Store after the incident
                            - keep default storage too small
                            - never review forced plans
                            - force plans without documenting reason
                            - ignore Query Store cleanup

                            Good:
                            - enable before migration
                            - capture baseline
                            - review top regressions
                            - document all hints and forced plans
                            - review monthly

Ledger simplified

Ledger goal:
                            - make data tampering evident
                            - preserve cryptographic history
                            - support audit and compliance scenarios

                            Ledger is useful for:
                            - financial records
                            - sensitive business events
                            - compliance evidence
                            - external audit confidence

                            Ledger is not:
                            - a replacement for backups
                            - a replacement for permissions
                            - a magic security layer
                            - a reason to ignore least privilege

AI and vector security warning

If SQL Server stores embeddings:
                            - embeddings may leak semantic meaning
                            - search results must respect tenant and user permissions
                            - vector queries need business filters
                            - audit must include AI-facing queries
                            - backups contain vectors too
                            - data retention policies must include embeddings

Server version and edition

SELECT
                            @@VERSION AS full_version;

                            SELECT
                            SERVERPROPERTY('MachineName') AS machine_name,
                            SERVERPROPERTY('ServerName') AS server_name,
                            SERVERPROPERTY('InstanceName') AS instance_name,
                            SERVERPROPERTY('ProductVersion') AS product_version,
                            SERVERPROPERTY('ProductLevel') AS product_level,
                            SERVERPROPERTY('ProductUpdateLevel') AS update_level,
                            SERVERPROPERTY('Edition') AS edition,
                            SERVERPROPERTY('EngineEdition') AS engine_edition;

Database compatibility levels

SELECT
                            name AS database_name,
                            compatibility_level,
                            recovery_model_desc,
                            state_desc,
                            is_query_store_on
                            FROM sys.databases
                            ORDER BY compatibility_level, name;

Query Store status

SELECT
                            d.name AS database_name,
                            d.is_query_store_on,
                            q.actual_state_desc,
                            q.desired_state_desc,
                            q.current_storage_size_mb,
                            q.max_storage_size_mb,
                            q.query_capture_mode_desc
                            FROM sys.databases d
                            LEFT JOIN sys.database_query_store_options q
                            ON d.database_id = DB_ID(d.name)
                            ORDER BY d.name;

Top queries by total duration

SELECT TOP (25)
                            qt.query_sql_text,
                            rs.count_executions,
                            rs.avg_duration / 1000.0 AS avg_duration_ms,
                            rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
                            rs.avg_logical_io_reads,
                            p.plan_id
                            FROM sys.query_store_query_text qt
                            JOIN sys.query_store_query q
                            ON qt.query_text_id = q.query_text_id
                            JOIN sys.query_store_plan p
                            ON q.query_id = p.query_id
                            JOIN sys.query_store_runtime_stats rs
                            ON p.plan_id = rs.plan_id
                            ORDER BY rs.avg_duration DESC;

Check latest backups

SELECT
                            d.name AS database_name,
                            MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full_backup,
                            MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff_backup,
                            MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log_backup
                            FROM sys.databases d
                            LEFT JOIN msdb.dbo.backupset b
                            ON d.name = b.database_name
                            GROUP BY d.name
                            ORDER BY d.name;

Find databases not at expected compatibility level

DECLARE @expected_level int = 160;

                            SELECT
                            name AS database_name,
                            compatibility_level,
                            CASE
                            WHEN compatibility_level = @expected_level THEN 'OK'
                            ELSE 'Review'
                            END AS status
                            FROM sys.databases
                            WHERE database_id > 4
                            ORDER BY status DESC, name;

Main SQL Server services

A SQL Server platform can include several services. The Database Engine is the core. Other services provide scheduling, reporting, ETL, analytics, text search or instance discovery. In production, each enabled service must have a reason, a service account, monitoring and a patching policy.

Typical enterprise layout

SQL Server host
                            |
                            +-- SQL Server Database Engine
                            |     +-- master
                            |     +-- model
                            |     +-- msdb
                            |     +-- tempdb
                            |     +-- user databases
                            |
                            +-- SQL Server Agent
                            |     +-- backup jobs
                            |     +-- index maintenance jobs
                            |     +-- integrity checks
                            |     +-- ETL jobs
                            |     +-- alert jobs
                            |
                            +-- Optional services
                            +-- SSIS
                            +-- SSRS
                            +-- SSAS
                            +-- Full-Text Search

Service account checklist

• Dedicated accounts: one account per important SQL Server service when possible.
• No personal accounts: never run a production service with a named user account.
• Least privilege: grant only required rights on files, shares and network resources.
• Password policy: coordinate password rotation with service restart windows.
• SPN management: Kerberos requires correct Service Principal Names in domain environments.
• Monitoring: detect service stopped, failed Agent jobs and startup errors.

From T-SQL text to physical execution

SQL Server does not execute a query as raw text. The relational engine transforms SQL text into a logical tree, validates names and types, estimates cardinalities, explores possible plans, chooses one plan, then the execution engine runs operators against the storage engine.

Execution pipeline

1. Client sends T-SQL batch
                            2. Parser checks syntax
                            3. Algebrizer resolves names and data types
                            4. Optimizer builds candidate plans
                            5. Cost model estimates relative cost
                            6. Best plan is selected
                            7. Memory grant is requested if needed
                            8. Execution engine runs plan operators
                            9. Storage engine reads or writes pages
                            10. Results are returned to client

Pipeline diagram

T-SQL text
                            |
                            v
                            Parser
                            |
                            v
                            Algebrizer / Binder
                            |
                            v
                            Optimizer
                            |
                            +-- statistics
                            +-- indexes
                            +-- constraints
                            +-- cardinality estimator
                            +-- cost model
                            |
                            v
                            Execution Plan
                            |
                            v
                            Execution Engine
                            |
                            +-- scans
                            +-- seeks
                            +-- joins
                            +-- sorts
                            +-- aggregates
                            |
                            v
                            Storage Engine

Memory architecture

SQL Server uses memory for data pages, compiled plans, query execution, locks, metadata, columnstore objects and internal structures. Memory pressure does not always mean the server needs more RAM. It can also mean bad queries, wrong indexes, poor memory grants or excessive ad hoc plan compilation.

Memory pressure diagram

SQL Server memory
                            |
                            +-- Buffer Pool
                            |     +-- data pages
                            |     +-- index pages
                            |
                            +-- Plan Cache
                            |     +-- compiled plans
                            |     +-- ad hoc plans
                            |
                            +-- Query Execution
                            |     +-- sort memory
                            |     +-- hash memory
                            |     +-- exchange memory
                            |
                            +-- Internal Structures
                            +-- locks
                            +-- metadata
                            +-- clerks
                            +-- columnstore

Common memory problems

Problem: queries spill to tempdb
                            Cause:
                            - memory grant too low
                            - cardinality estimate wrong
                            - statistics stale
                            - large sort/hash operation

                            Problem: RESOURCE_SEMAPHORE waits
                            Cause:
                            - queries waiting for memory grant
                            - grants too large
                            - concurrency too high

                            Problem: high compilation CPU
                            Cause:
                            - ad hoc workload
                            - plan cache pollution
                            - parameterization issues

Buffer Pool: why memory saves the database

SQL Server reads data from disk into memory as 8 KB pages. Once a page is in the Buffer Pool, future reads can be served from memory instead of storage. This is why RAM sizing, query design and index strategy are directly connected.

Read path

Query needs a row
                            |
                            v
                            Storage Engine checks Buffer Pool
                            |
                            +-- Page found in memory
                            |       |
                            |       v
                            |    Logical read
                            |
                            +-- Page not found
                            |
                            v
                            Physical read from data file
                            |
                            v
                            Page added to Buffer Pool

Important vocabulary

DBA warning

High logical reads:
                            - query may be inefficient
                            - missing index
                            - broad scan
                            - bad join strategy

                            High physical reads:
                            - memory pressure
                            - cold cache
                            - storage issue
                            - working set larger than memory

File architecture diagram

Database Sales
                            |
                            +-- PRIMARY filegroup
                            |     +-- Sales.mdf
                            |
                            +-- FG_DATA
                            |     +-- Sales_Data_01.ndf
                            |     +-- Sales_Data_02.ndf
                            |
                            +-- FG_INDEX
                            |     +-- Sales_Index_01.ndf
                            |
                            +-- Transaction Log
                            +-- Sales_Log.ldf

Autogrowth mistakes

Bad:
                            - growth by 1 MB
                            - percent growth on large files
                            - no monitoring
                            - log file growing every few minutes
                            - disk nearly full

                            Good:
                            - pre-size files
                            - fixed MB growth
                            - monitor growth events
                            - separate data and log latency
                            - test restore duration
                            - align file layout with workload

Pages and extents: the physical unit of SQL Server storage

SQL Server stores table and index data in 8 KB pages. Eight contiguous pages form an extent of 64 KB. Understanding pages and extents helps explain fragmentation, page splits, fill factor, reads, allocation contention and why row width matters.

Storage hierarchy

Database
                            |
                            +-- Filegroup
                            |
                            +-- Data file
                            |
                            +-- Extent = 64 KB
                            |
                            +-- Page = 8 KB
                            |
                            +-- Rows

Important page concepts

Transaction log: durability and recovery

The transaction log is one of the most critical components of SQL Server. Every data modification is logged before it is considered durable. The log allows rollback, crash recovery, high availability, replication, log shipping and point-in-time restore.

Write path

UPDATE statement
                            |
                            v
                            Acquire locks
                            |
                            v
                            Modify page in Buffer Pool
                            |
                            v
                            Generate log record
                            |
                            v
                            COMMIT
                            |
                            v
                            Flush log block to LDF
                            |
                            v
                            Transaction durable

Log-related terms

Common log incidents

Log grows continuously:
                            - missing log backups
                            - long open transaction
                            - replication or availability issue
                            - recovery model misunderstood

                            Slow commits:
                            - slow log disk
                            - too many tiny transactions
                            - synchronous HA commit latency
                            - log file autogrowth event

SQLOS: the internal operating layer

SQLOS is SQL Server’s internal layer for scheduling, workers, memory, waits and synchronization. It is not an external operating system, but it acts like a specialized runtime that allows SQL Server to coordinate many concurrent tasks efficiently.

Key components

• Scheduler: logical scheduler usually associated with a visible CPU.
• Worker: execution context used to run a task.
• Task: unit of work scheduled by SQL Server.
• Wait: measured time spent waiting for a resource.
• Yielding: cooperative scheduling behavior when a worker gives up CPU.

SQLOS mental model

CPU visible to SQL Server
                            |
                            v
                            Scheduler
                            |
                            +-- runnable queue
                            +-- current worker
                            +-- suspended workers
                            |
                            v
                            Workers execute tasks
                            |
                            +-- running
                            +-- runnable
                            +-- suspended
                            |
                            v
                            Wait stats reveal why work is delayed

Common SQLOS symptoms

SOS_SCHEDULER_YIELD:
                            - CPU pressure
                            - CPU-heavy queries
                            - inefficient plans

                            THREADPOOL:
                            - worker starvation
                            - too many concurrent requests
                            - blocking chains consuming workers

                            RESOURCE_SEMAPHORE:
                            - queries waiting for memory grants
                            - large sort/hash operations
                            - concurrency too high

tempdb: the shared scratch database

tempdb is used by all databases on the instance. It stores temporary tables, table variables, internal worktables, sorts, hash spills, row versioning data and online operation work areas. A weak tempdb design can slow the entire instance.

What uses tempdb?

• Temporary tables: local and global temp tables.
• Table variables: depending on usage and size.
• Sorts and hashes: when memory grant is insufficient.
• Version store: snapshot isolation and read committed snapshot.
• Index rebuilds: depending on options and operations.
• DBCC CHECKDB: can use significant tempdb space.

I/O architecture

SQL Server I/O paths
                            |
                            +-- Data reads
                            |     +-- random reads
                            |     +-- sequential scans
                            |
                            +-- Data writes
                            |     +-- dirty page flush
                            |     +-- checkpoint
                            |
                            +-- Log writes
                            |     +-- sequential writes
                            |     +-- commit latency critical
                            |
                            +-- tempdb I/O
                            +-- spills
                            +-- temp objects
                            +-- version store

tempdb mistakes

Bad:
                            - one tiny tempdb data file
                            - autogrowth every few minutes
                            - tempdb on slow disk
                            - no monitoring of version store
                            - ignoring spills in plans

                            Good:
                            - pre-size tempdb
                            - use multiple data files when needed
                            - fixed-size autogrowth
                            - monitor waits and file usage
                            - tune queries that spill

Active requests

SELECT
                            r.session_id,
                            r.status,
                            r.command,
                            r.cpu_time,
                            r.total_elapsed_time,
                            r.wait_type,
                            r.wait_time,
                            r.blocking_session_id,
                            DB_NAME(r.database_id) AS database_name,
                            t.text AS sql_text
                            FROM sys.dm_exec_requests r
                            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                            ORDER BY r.total_elapsed_time DESC;

Top cached queries by CPU

SELECT TOP (25)
                            qs.total_worker_time / 1000 AS total_cpu_ms,
                            qs.execution_count,
                            qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_cpu_ms,
                            qs.total_logical_reads,
                            qs.total_elapsed_time / 1000 AS total_elapsed_ms,
                            SUBSTRING(
                            st.text,
                            (qs.statement_start_offset / 2) + 1,
                            ((CASE qs.statement_end_offset
                            WHEN -1 THEN DATALENGTH(st.text)
                            ELSE qs.statement_end_offset
                            END - qs.statement_start_offset) / 2) + 1
                            ) AS statement_text
                            FROM sys.dm_exec_query_stats qs
                            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
                            ORDER BY qs.total_worker_time DESC;

Blocking overview

SELECT
                            r.session_id,
                            r.blocking_session_id,
                            r.wait_type,
                            r.wait_time,
                            r.wait_resource,
                            r.status,
                            r.command,
                            DB_NAME(r.database_id) AS database_name,
                            t.text AS sql_text
                            FROM sys.dm_exec_requests r
                            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                            WHERE r.blocking_session_id <> 0
                            ORDER BY r.wait_time DESC;

Database file sizes

SELECT
                            DB_NAME(database_id) AS database_name,
                            type_desc,
                            name AS logical_name,
                            physical_name,
                            size * 8 / 1024 AS size_mb,
                            growth,
                            is_percent_growth
                            FROM sys.master_files
                            ORDER BY database_name, type_desc, logical_name;

Index usage

SELECT
                            OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
                            OBJECT_NAME(i.object_id) AS table_name,
                            i.name AS index_name,
                            s.user_seeks,
                            s.user_scans,
                            s.user_lookups,
                            s.user_updates
                            FROM sys.indexes i
                            LEFT JOIN sys.dm_db_index_usage_stats s
                            ON s.object_id = i.object_id
                            AND s.index_id = i.index_id
                            AND s.database_id = DB_ID()
                            WHERE i.object_id > 100
                            ORDER BY s.user_updates DESC, s.user_seeks ASC;

Daily architecture checks

Daily:
                            1. Failed SQL Agent jobs
                            2. Backup status
                            3. Log reuse wait
                            4. Disk free space
                            5. Blocking sessions
                            6. Severe SQL errors
                            7. tempdb usage
                            8. Top waits
                            9. Long-running requests
                            10. Query Store regressions

Monthly architecture checks

Monthly:
                            1. Restore test
                            2. File growth review
                            3. Index usage review
                            4. Query Store report
                            5. Patch level review
                            6. Security permission review
                            7. HA failover drill
                            8. Capacity forecast
                            9. Service account review
                            10. Documentation update

Pages and extents: the physical base

SQL Server stores data in 8 KB pages. A page is the basic unit of I/O. Even if a query needs only one small row, SQL Server reads the page containing that row. This is why row width, index design and page density directly affect performance.

Hierarchy

Database
                            |
                            +-- Filegroup
                            |
                            +-- Data file
                            |
                            +-- Extent = 64 KB
                            |
                            +-- Page 1 = 8 KB
                            +-- Page 2 = 8 KB
                            +-- Page 3 = 8 KB
                            +-- Page 4 = 8 KB
                            +-- Page 5 = 8 KB
                            +-- Page 6 = 8 KB
                            +-- Page 7 = 8 KB
                            +-- Page 8 = 8 KB

Important concepts

Allocation maps: how SQL Server tracks space

SQL Server uses special internal pages to track allocated extents, mixed extents, free space, object ownership and changed pages. These allocation maps are essential for space management, backups, page allocation and internal navigation.

Main allocation map pages

Allocation map mental model

Data file
                            |
                            +-- Allocation map pages
                            |     +-- PFS: page free space
                            |     +-- GAM: allocated extents
                            |     +-- SGAM: mixed extents
                            |     +-- IAM: object allocation map
                            |     +-- DCM: differential backup tracking
                            |     +-- BCM: bulk operation tracking
                            |
                            +-- Data pages
                            +-- Index pages
                            +-- LOB pages
                            +-- Row-overflow pages

Why DBAs care

Allocation maps explain:
                            - why tempdb can have allocation contention
                            - why differential backups know what changed
                            - how SQL Server tracks object space
                            - why page allocation is not random
                            - why file layout matters under high concurrency

Bad file layout

Single volume:
                            C:\SQL\
                            Sales.mdf
                            Sales_log.ldf
                            tempdb.mdf
                            tempdb_log.ldf
                            backups\Sales.bak

                            Risks:
                            - OS, data, log, tempdb and backups compete
                            - log latency affects commits
                            - backup can saturate production disk
                            - tempdb can hurt every database
                            - disk full becomes catastrophic

Better file layout

Separated paths:
                            D:\SQLData\
                            Sales.mdf
                            Sales_Data01.ndf

                            L:\SQLLog\
                            Sales_log.ldf

                            T:\SQLTempDB\
                            tempdb.mdf
                            tempdb_02.ndf
                            tempdb_log.ldf

                            B:\SQLBackups\
                            Sales_full.bak
                            Sales_log.trn

                            Benefits:
                            - clearer capacity monitoring
                            - better latency isolation
                            - easier troubleshooting
                            - safer backup strategy

Filegroups: logical organization of data files

A filegroup is a logical container for one or more data files. Filegroups are useful when a database becomes large, when partitioning is needed, when some data is read-only, when backup and restore strategies need more granularity, or when different storage tiers are used.

Why use filegroups?

• Separate large objects: isolate tables, indexes or archives.
• Support partitioning: place partitions on different filegroups.
• Improve restore strategy: partial restore can reduce recovery time for large databases.
• Support read-only data: historical filegroups can become read-only.
• Improve administration: make capacity and ownership clearer.

Filegroup architecture

Database SalesDB
                            |
                            +-- PRIMARY
                            |     +-- SalesDB.mdf
                            |
                            +-- FG_ACTIVE_DATA
                            |     +-- SalesDB_Active_01.ndf
                            |     +-- SalesDB_Active_02.ndf
                            |
                            +-- FG_ARCHIVE_2022
                            |     +-- SalesDB_Archive_2022.ndf
                            |
                            +-- FG_ARCHIVE_2023
                            +-- SalesDB_Archive_2023.ndf

Typical strategy

Active data:
                            - fast storage
                            - frequent backup
                            - full read/write

                            Archive data:
                            - cheaper storage
                            - possible read-only filegroup
                            - less frequent access
                            - separate restore strategy

                            Indexes:
                            - sometimes separate filegroup
                            - useful for administration
                            - not magic for performance by itself

B-tree simplified

Index root page
                            |
                            +-- intermediate pages
                            |
                            +-- leaf pages
                            |
                            +-- key values
                            +-- row locator or included columns

                            Index seek:
                            root -> intermediate -> leaf -> row

                            Index scan:
                            many leaf pages read sequentially

Storage cost mindset

One new index can mean:
                            - more pages in data files
                            - more pages in memory
                            - more log records on writes
                            - more backup size
                            - more index maintenance
                            - more statistics to update
                            - slower inserts and updates

                            Therefore:
                            Every index must have evidence.

Transaction log storage

The transaction log is not just another file. It is the sequential history of database changes. SQL Server uses it for rollback, crash recovery, high availability, replication, log shipping and point-in-time restore. A slow or full log can stop production.

Commit path

Application sends COMMIT
                            |
                            v
                            SQL Server has dirty pages in memory
                            |
                            v
                            Log records are flushed to LDF
                            |
                            v
                            Commit is acknowledged
                            |
                            v
                            Data pages can be written later

Log design rules

• Use low-latency storage: log writes are commit-critical.
• Pre-size the log: avoid repeated growth during workload.
• Use fixed growth: avoid tiny growth and percent growth on large logs.
• Monitor VLF count: too many VLFs can hurt recovery and operations.
• Back up the log: in full recovery model, log backups are mandatory.
• Watch log_reuse_wait_desc: it explains why the log cannot be reused.

Common incident pattern

Database in FULL recovery model
                            |
                            +-- no transaction log backups
                            |
                            v
                            Log cannot be truncated
                            |
                            v
                            LDF file grows
                            |
                            v
                            Disk becomes full
                            |
                            v
                            Database stops accepting writes

tempdb: the shared pressure point

tempdb is recreated at every SQL Server startup and is shared by the whole instance. It is used by user objects, internal objects, row versioning, sorts, hash joins, index operations, triggers, snapshot isolation, online operations and DBCC commands.

tempdb users

tempdb architecture

tempdb
                            |
                            +-- Data files
                            |     +-- tempdb.mdf
                            |     +-- tempdb_02.ndf
                            |     +-- tempdb_03.ndf
                            |     +-- tempdb_04.ndf
                            |
                            +-- Log file
                            |     +-- templog.ldf
                            |
                            +-- Consumers
                            +-- temp tables
                            +-- worktables
                            +-- spills
                            +-- version store
                            +-- DBCC
                            +-- online operations

tempdb design rules

• Pre-size files: avoid growth during workload.
• Use fixed growth: predictable expansion.
• Use multiple data files when needed: helps reduce allocation contention.
• Keep equal file sizes: proportional fill works better.
• Monitor version store: long transactions can retain versions.
• Fix spills: do not hide query problems by only adding disk.

Autogrowth: emergency mechanism, not capacity planning

Autogrowth prevents immediate failure when a file runs out of space, but it should not be the normal way a database grows. Growth events can pause workload, generate latency, fragment files at the storage level and create unpredictable incidents.

Bad pattern

Database file size:
                            50 GB

                            Autogrowth:
                            1 MB

                            Workload:
                            bulk load inserts 20 GB

                            Result:
                            thousands of growth events
                            production latency
                            storage fragmentation
                            angry users
                            slow maintenance window

Good pattern

Database file size:
                            pre-sized for next 3 to 6 months

                            Autogrowth:
                            fixed size, for example 512 MB or 1 GB

                            Monitoring:
                            alert at 70 percent
                            warning at 80 percent
                            critical at 90 percent

                            Capacity:
                            reviewed weekly or monthly
                            growth forecast documented

Rules

• Use fixed MB or GB growth: avoid percent growth for large files.
• Avoid tiny growth: 1 MB growth is almost always wrong for production.
• Pre-size files: especially data, log and tempdb files.
• Monitor growth frequency: frequent growth means bad capacity planning.
• Keep log growth controlled: log autogrowth can block commits.

I/O pattern map

Data files:
                            - random reads
                            - sequential scans
                            - checkpoint writes
                            - index maintenance writes

                            Log files:
                            - sequential writes
                            - commit critical
                            - sensitive to latency

                            tempdb:
                            - mixed read/write
                            - spills
                            - temp tables
                            - version store

                            Backups:
                            - large sequential reads from data
                            - large sequential writes to backup target

Storage design model

Recommended separation:
                            D:\SQLData
                            user database MDF/NDF

                            L:\SQLLog
                            user database LDF

                            T:\SQLTempDB
                            tempdb data and log

                            B:\SQLBackups
                            backup output

                            M:\SQLMaintenance
                            exports, scripts, staging files

                            Goal:
                            isolate latency
                            simplify monitoring
                            reduce blast radius
                            improve recovery operations

Database file overview

SELECT
                            DB_NAME(database_id) AS database_name,
                            type_desc,
                            name AS logical_name,
                            physical_name,
                            size * 8 / 1024 AS size_mb,
                            max_size,
                            growth,
                            is_percent_growth
                            FROM sys.master_files
                            ORDER BY database_name, type_desc, logical_name;

Database size summary

SELECT
                            DB_NAME(database_id) AS database_name,
                            SUM(size) * 8 / 1024 AS total_size_mb
                            FROM sys.master_files
                            GROUP BY database_id
                            ORDER BY total_size_mb DESC;

Object space usage

SELECT TOP (50)
                            s.name AS schema_name,
                            o.name AS object_name,
                            SUM(a.total_pages) * 8 / 1024 AS total_mb,
                            SUM(a.used_pages) * 8 / 1024 AS used_mb,
                            SUM(a.data_pages) * 8 / 1024 AS data_mb
                            FROM sys.objects AS o
                            JOIN sys.schemas AS s
                            ON o.schema_id = s.schema_id
                            JOIN sys.partitions AS p
                            ON o.object_id = p.object_id
                            JOIN sys.allocation_units AS a
                            ON p.partition_id = a.container_id
                            WHERE o.is_ms_shipped = 0
                            GROUP BY s.name, o.name
                            ORDER BY total_mb DESC;

Log reuse wait

SELECT
                            name,
                            recovery_model_desc,
                            log_reuse_wait_desc
                            FROM sys.databases
                            ORDER BY name;

tempdb usage

SELECT
                            SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
                            SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
                            SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
                            SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
                            FROM tempdb.sys.dm_db_file_space_usage;

Backup history size

SELECT TOP (100)
                            database_name,
                            type AS backup_type,
                            backup_start_date,
                            backup_finish_date,
                            DATEDIFF(second, backup_start_date, backup_finish_date) AS duration_sec,
                            backup_size / 1024 / 1024 AS backup_size_mb,
                            compressed_backup_size / 1024 / 1024 AS compressed_backup_size_mb
                            FROM msdb.dbo.backupset
                            ORDER BY backup_finish_date DESC;

Daily checks

Daily:
                            1. Disk free space
                            2. Failed backups
                            3. Log backup chain
                            4. Log reuse wait
                            5. tempdb usage
                            6. Recent autogrowth events
                            7. File latency
                            8. Blocking caused by storage waits
                            9. Backup duration
                            10. SQL Agent storage alerts

Monthly checks

Monthly:
                            1. Database growth trend
                            2. Top tables by size
                            3. Top indexes by size
                            4. Unused large indexes
                            5. tempdb peak usage
                            6. Restore test duration
                            7. Filegroup review
                            8. VLF review
                            9. Autogrowth setting review
                            10. Storage capacity forecast

Core T-SQL syntax

T-SQL extends standard SQL with variables, control flow, batches, temporary objects, system functions and SQL Server-specific expressions. The most important habit is to write set-based, deterministic and measurable code.

Variables and basic flow

DECLARE @customer_id int = 1001;
                            DECLARE @min_amount decimal(12,2) = 500.00;
                            DECLARE @status varchar(20) = 'ACTIVE';

                            IF @status = 'ACTIVE'
                            BEGIN
                            SELECT
                            order_id,
                            order_date,
                            total_amount
                            FROM dbo.orders
                            WHERE customer_id = @customer_id
                            AND total_amount >= @min_amount;
                            END
                            ELSE
                            BEGIN
                            SELECT 'Customer is not active' AS message;
                            END;

CASE expression

SELECT
                            order_id,
                            total_amount,
                            CASE
                            WHEN total_amount >= 10000 THEN 'HIGH'
                            WHEN total_amount >= 1000 THEN 'MEDIUM'
                            ELSE 'LOW'
                            END AS order_band
                            FROM dbo.orders;

Common building blocks

CTE and window function

WITH ranked_orders AS (
                            SELECT
                            customer_id,
                            order_id,
                            order_date,
                            total_amount,
                            ROW_NUMBER() OVER (
                            PARTITION BY customer_id
                            ORDER BY order_date DESC
                            ) AS rn
                            FROM dbo.orders
                            )
                            SELECT
                            customer_id,
                            order_id,
                            order_date,
                            total_amount
                            FROM ranked_orders
                            WHERE rn = 1;

Stored procedures as database-side APIs

Stored procedures are one of the most important SQL Server programming tools. They can encapsulate validation, transactions, security, logging and stable access patterns. A clean procedure is predictable, parameterized, small enough to maintain, and measurable through Query Store or execution plans.

Good stored procedure design

• Clear name: use a verb and business object.
• Typed parameters: match column data types to avoid implicit conversions.
• SET NOCOUNT ON: reduce unnecessary row count messages.
• Transaction scope: keep it as short as possible.
• Error handling: use TRY/CATCH and THROW.
• Security: grant EXECUTE instead of direct table access when appropriate.
• Observability: log business errors and monitor performance.

Procedure template

CREATE OR ALTER PROCEDURE dbo.create_customer_order
                            @customer_id int,
                            @order_date datetime2(0),
                            @created_by sysname
                            AS
                            BEGIN
                            SET NOCOUNT ON;
                            SET XACT_ABORT ON;

                            BEGIN TRY
                            BEGIN TRAN;

                            IF NOT EXISTS (
                            SELECT 1
                            FROM dbo.customers
                            WHERE customer_id = @customer_id
                            AND status = 'ACTIVE'
                            )
                            BEGIN
                            THROW 50001, 'Customer is not active or does not exist.', 1;
                            END;

                            INSERT INTO dbo.orders (
                            customer_id,
                            order_date,
                            created_by,
                            created_at
                            )
                            VALUES (
                            @customer_id,
                            @order_date,
                            @created_by,
                            SYSUTCDATETIME()
                            );

                            SELECT SCOPE_IDENTITY() AS new_order_id;

                            COMMIT;
                            END TRY
                            BEGIN CATCH
                            IF XACT_STATE() <> 0
                            ROLLBACK;

                            THROW;
                            END CATCH;
                            END;

Functions: useful, but dangerous if misunderstood

T-SQL functions help reuse logic, but not all functions have the same performance profile. Inline table-valued functions are often excellent because the optimizer can expand them. Multi-statement table-valued functions and scalar functions can be problematic when they hide row-by-row execution, poor estimates or expensive logic.

Function types

Inline TVF example

CREATE OR ALTER FUNCTION dbo.get_customer_orders
                            (
                            @customer_id int,
                            @from_date date
                            )
                            RETURNS TABLE
                            AS
                            RETURN
                            (
                            SELECT
                            o.order_id,
                            o.customer_id,
                            o.order_date,
                            o.total_amount,
                            o.status
                            FROM dbo.orders AS o
                            WHERE o.customer_id = @customer_id
                            AND o.order_date >= @from_date
                            );

Usage

SELECT
                            order_id,
                            order_date,
                            total_amount
                            FROM dbo.get_customer_orders(1001, '2026-01-01')
                            WHERE status = 'PAID'
                            ORDER BY order_date DESC;

Transactions: ACID in practice

Transactions make a group of changes atomic. Either all changes are committed, or the work is rolled back. In SQL Server, transaction design directly affects locks, blocking, log growth, deadlocks, recovery time and application latency.

ACID reminder

Safe transfer example

CREATE OR ALTER PROCEDURE dbo.transfer_funds
                            @from_account_id int,
                            @to_account_id int,
                            @amount decimal(19,4)
                            AS
                            BEGIN
                            SET NOCOUNT ON;
                            SET XACT_ABORT ON;

                            BEGIN TRY
                            BEGIN TRAN;

                            UPDATE dbo.accounts
                            SET balance = balance - @amount
                            WHERE account_id = @from_account_id
                            AND balance >= @amount;

                            IF @@ROWCOUNT <> 1
                            THROW 50010, 'Insufficient funds or source account missing.', 1;

                            UPDATE dbo.accounts
                            SET balance = balance + @amount
                            WHERE account_id = @to_account_id;

                            IF @@ROWCOUNT <> 1
                            THROW 50011, 'Target account missing.', 1;

                            INSERT INTO dbo.account_movements (
                            from_account_id,
                            to_account_id,
                            amount,
                            created_at
                            )
                            VALUES (
                            @from_account_id,
                            @to_account_id,
                            @amount,
                            SYSUTCDATETIME()
                            );

                            COMMIT;
                            END TRY
                            BEGIN CATCH
                            IF XACT_STATE() <> 0
                            ROLLBACK;

                            THROW;
                            END CATCH;
                            END;

TRY/CATCH and reliable error handling

Error handling is not decoration. It decides whether a failed operation leaves the database clean or inconsistent. SQL Server procedures that modify data should normally use TRY/CATCH, XACT_ABORT, XACT_STATE and THROW.

Core functions

Robust error template

BEGIN TRY
                            SET XACT_ABORT ON;

                            BEGIN TRAN;

                            -- Write work here
                            INSERT INTO dbo.audit_event (
                            event_type,
                            event_payload,
                            created_at
                            )
                            VALUES (
                            'ORDER_CREATED',
                            N'{"status":"created"}',
                            SYSUTCDATETIME()
                            );

                            COMMIT;
                            END TRY
                            BEGIN CATCH
                            IF XACT_STATE() <> 0
                            ROLLBACK;

                            INSERT INTO dbo.error_log (
                            error_number,
                            error_message,
                            error_line,
                            error_procedure,
                            created_at
                            )
                            VALUES (
                            ERROR_NUMBER(),
                            ERROR_MESSAGE(),
                            ERROR_LINE(),
                            ERROR_PROCEDURE(),
                            SYSUTCDATETIME()
                            );

                            THROW;
                            END CATCH;

Triggers: powerful but dangerous

Triggers execute automatically after or instead of data modifications. They are useful for audit, history, validation and synchronization patterns. But they are also invisible to many developers, can create unexpected workload, and can turn simple DML into complex side effects.

Trigger rules

• Think set-based: inserted and deleted can contain many rows.
• Keep trigger short: no slow remote calls, no complex workflow.
• Never assume one row: multi-row INSERT, UPDATE and DELETE are normal.
• Document side effects: application teams must know they exist.
• Monitor performance: trigger cost is paid by the original DML statement.

Audit trigger example

CREATE OR ALTER TRIGGER dbo.trg_orders_audit
                            ON dbo.orders
                            AFTER UPDATE
                            AS
                            BEGIN
                            SET NOCOUNT ON;

                            INSERT INTO dbo.orders_audit (
                            order_id,
                            old_status,
                            new_status,
                            changed_at
                            )
                            SELECT
                            i.order_id,
                            d.status AS old_status,
                            i.status AS new_status,
                            SYSUTCDATETIME()
                            FROM inserted AS i
                            JOIN deleted AS d
                            ON i.order_id = d.order_id
                            WHERE ISNULL(i.status, '') <> ISNULL(d.status, '');
                            END;

MERGE and UPSERT logic

MERGE is designed to synchronize source and target sets: update matching rows, insert missing rows, and optionally delete rows absent from the source. It is elegant, but in production it must be used carefully because concurrency, duplicate source rows and complex logic can create subtle bugs.

When MERGE is tempting

• Synchronizing staging table into target table.
• Loading dimension tables in a warehouse.
• Applying changes from an external system.
• Building idempotent import routines.

MERGE warnings

• Source must be unique: duplicate source keys can break logic.
• Concurrency matters: two sessions can race without proper locking.
• Test triggers: MERGE can fire triggers with inserted and deleted sets.
• Keep it simple: complex MERGE statements become hard to audit.

MERGE example

MERGE dbo.product AS target
                            USING dbo.stage_product AS source
                            ON target.product_code = source.product_code
                            WHEN MATCHED THEN
                            UPDATE SET
                            target.product_name = source.product_name,
                            target.price = source.price,
                            target.updated_at = SYSUTCDATETIME()
                            WHEN NOT MATCHED BY TARGET THEN
                            INSERT (
                            product_code,
                            product_name,
                            price,
                            created_at
                            )
                            VALUES (
                            source.product_code,
                            source.product_name,
                            source.price,
                            SYSUTCDATETIME()
                            )
                            OUTPUT
                            $action AS merge_action,
                            inserted.product_code,
                            inserted.product_name;

JSON and XML in SQL Server

SQL Server can store and process semi-structured data. JSON is commonly used for API payloads, application metadata, audit details, configuration and flexible attributes. XML is older and more strongly typed, with XML data type, XQuery and XML indexes.

JSON functions

Extract JSON values

DECLARE @payload nvarchar(max) = N'{
                            "customer": "ACME",
                            "amount": 1200.50,
                            "items": [
                            { "sku": "A-100", "qty": 2 },
                            { "sku": "B-200", "qty": 1 }
                            ]
                            }';

                            SELECT
                            JSON_VALUE(@payload, '$.customer') AS customer_name,
                            TRY_CAST(JSON_VALUE(@payload, '$.amount') AS decimal(12,2)) AS amount,
                            JSON_QUERY(@payload, '$.items') AS items_json;

Parse JSON array

SELECT
                            sku,
                            qty
                            FROM OPENJSON(@payload, '$.items')
                            WITH (
                            sku varchar(30) '$.sku',
                            qty int '$.qty'
                            );

Dynamic SQL: flexibility with strict safety

Dynamic SQL is useful when table names, column lists, filters or administrative statements must be built at runtime. It is also one of the easiest ways to create SQL injection, plan cache pollution and unreadable systems.

Good dynamic SQL rules

• Use sp_executesql: parameterize values.
• Use QUOTENAME: protect identifiers such as schema, table or column names.
• Never concatenate user values: values must be parameters.
• Log generated SQL: helpful for debugging and audits.
• Keep scope limited: dynamic SQL should solve a real problem, not become default style.

Safe parameterized dynamic SQL

DECLARE @sql nvarchar(max);
                            DECLARE @customer_id int = 1001;
                            DECLARE @from_date date = '2026-01-01';

                            SET @sql = N'
                            SELECT
                            order_id,
                            order_date,
                            total_amount
                            FROM dbo.orders
                            WHERE customer_id = @p_customer_id
                            AND order_date >= @p_from_date
                            ORDER BY order_date DESC;
                            ';

                            EXEC sys.sp_executesql
                            @sql,
                            N'@p_customer_id int, @p_from_date date',
                            @p_customer_id = @customer_id,
                            @p_from_date = @from_date;

Non-sargable predicate

-- Bad: function applied to column
                            SELECT order_id
                            FROM dbo.orders
                            WHERE YEAR(order_date) = 2026;

                            -- Better: range predicate
                            SELECT order_id
                            FROM dbo.orders
                            WHERE order_date >= '2026-01-01'
                            AND order_date <  '2027-01-01';

Implicit conversion risk

-- Bad if customer_code is varchar
                            DECLARE @customer_code nvarchar(30) = N'ACME';

                            SELECT customer_id
                            FROM dbo.customers
                            WHERE customer_code = @customer_code;

                            -- Better: parameter type matches column type
                            DECLARE @customer_code_v varchar(30) = 'ACME';

                            SELECT customer_id
                            FROM dbo.customers
                            WHERE customer_code = @customer_code_v;

Covering index example

CREATE INDEX IX_orders_customer_date
                            ON dbo.orders(customer_id, order_date)
                            INCLUDE (total_amount, status);

                            SELECT
                            order_date,
                            total_amount,
                            status
                            FROM dbo.orders
                            WHERE customer_id = 1001
                            AND order_date >= '2026-01-01'
                            ORDER BY order_date DESC;

Parameter sniffing symptom

Same procedure:
                            - fast for small customer
                            - slow for large customer
                            - same cached plan reused
                            - data distribution is skewed

                            Possible actions:
                            - better indexes
                            - update statistics
                            - Query Store analysis
                            - PSP on SQL Server 2022+
                            - OPTION RECOMPILE for specific cases
                            - procedure redesign

Code review checklist

Before production:
                            1. Check execution plan
                            2. Check logical reads
                            3. Check CPU and duration
                            4. Check missing or excessive indexes
                            5. Check transaction scope
                            6. Check error handling
                            7. Check parameter data types
                            8. Check concurrency behavior
                            9. Check tempdb spills
                            10. Check Query Store after deployment

Incident checklist

When T-SQL causes an incident:
                            1. Identify SQL text or procedure
                            2. Capture plan
                            3. Check waits
                            4. Check blocking
                            5. Check Query Store history
                            6. Compare previous plan
                            7. Check statistics and indexes
                            8. Check parameter values
                            9. Apply safe mitigation
                            10. Document permanent fix

B-tree rowstore index model

Most traditional SQL Server indexes are B-tree structures. The root page points to intermediate pages, which point to leaf pages. For a clustered index, the leaf level is the actual data. For a nonclustered index, the leaf level contains index keys plus a row locator or included columns.

B-tree diagram

Root page
                            |
                            +-- Intermediate page A
                            |     |
                            |     +-- Leaf page A1
                            |     +-- Leaf page A2
                            |
                            +-- Intermediate page B
                            |
                            +-- Leaf page B1
                            +-- Leaf page B2

                            Index seek:
                            root -> intermediate -> leaf -> matching rows

                            Index scan:
                            read many or all leaf pages

Seek, scan, lookup

Access path intuition

Selective predicate:
                            WHERE customer_id = 1001
                            -> index seek can be excellent

                            Broad predicate:
                            WHERE order_date >= '2020-01-01'
                            -> scan may be legitimate

                            Important:
                            seek is not always good
                            scan is not always bad
                            the real question is:
                            how many pages are read for the business result?

Clustered index: the table’s main physical path

A clustered index defines the logical order of rows at the leaf level. In SQL Server, a table can have only one clustered index because the data rows themselves live at the leaf level of that structure. The clustered key is also stored in nonclustered indexes as the row locator, so choosing it badly has a global cost.

Good clustered key qualities

• Narrow: small key means smaller nonclustered indexes.
• Stable: avoid updates to the clustered key.
• Unique or made unique: duplicate clustered keys require internal uniquifier overhead.
• Usually increasing: reduces random page splits for heavy insert workloads.
• Useful for access pattern: should support the way the table is queried.

Clustered index diagram

Clustered index on orders(order_id)

                            Root
                            |
                            +-- Intermediate
                            |
                            +-- Leaf pages
                            |
                            +-- Full data row order_id = 1
                            +-- Full data row order_id = 2
                            +-- Full data row order_id = 3

                            The leaf level IS the table data.

Create clustered index

CREATE TABLE dbo.orders
                            (
                            order_id bigint NOT NULL,
                            customer_id int NOT NULL,
                            order_date datetime2(0) NOT NULL,
                            total_amount decimal(12,2) NOT NULL,
                            status varchar(20) NOT NULL,
                            CONSTRAINT PK_orders
                            PRIMARY KEY CLUSTERED (order_id)
                            );

Nonclustered indexes: secondary access paths

A nonclustered index is a separate B-tree structure. Its leaf pages contain the nonclustered key, optional included columns, and a row locator. It is designed to help specific query patterns without changing the clustered structure of the table.

Nonclustered index anatomy

Nonclustered index:
                            key columns:
                            customer_id, order_date

                            included columns:
                            total_amount, status

                            row locator:
                            clustered key value
                            or RID if table is a heap

                            Query can be covered if all required columns are in the index.

Example

CREATE INDEX IX_orders_customer_date
                            ON dbo.orders(customer_id, order_date DESC)
                            INCLUDE (total_amount, status);

Query it supports

SELECT
                            order_date,
                            total_amount,
                            status
                            FROM dbo.orders
                            WHERE customer_id = 1001
                            AND order_date >= '2026-01-01'
                            ORDER BY order_date DESC;

Why it works

WHERE:
                            customer_id = equality predicate
                            order_date = range predicate

                            ORDER BY:
                            order_date DESC matches index order

                            SELECT:
                            total_amount and status are included

                            Result:
                            seek + ordered read + no key lookup

Included columns: covering without changing key order

Included columns live at the leaf level of a nonclustered index. They do not participate in the B-tree navigation order, but they can satisfy SELECT columns and avoid key lookups. They are excellent when used carefully, but dangerous when they turn every index into a copy of the table.

Key versus included columns

Covering index example

CREATE INDEX IX_invoice_customer_date
                            ON dbo.invoice(customer_id, invoice_date)
                            INCLUDE (invoice_number, total_amount, payment_status);

Covered query

SELECT
                            invoice_number,
                            invoice_date,
                            total_amount,
                            payment_status
                            FROM dbo.invoice
                            WHERE customer_id = @customer_id
                            AND invoice_date >= @from_date;

Effect

Without included columns:
                            seek index -> key lookup for missing columns

                            With included columns:
                            seek index -> return directly from index leaf

                            Benefit:
                            fewer random lookups
                            fewer logical reads
                            lower CPU
                            better latency

Filtered indexes: small, precise and powerful

A filtered index indexes only rows that match a predicate. This can be extremely efficient for sparse or status-based data, such as active rows, unprocessed jobs, open orders, non-deleted records or rows with a specific type.

Good filtered index cases

• Soft delete: WHERE is_deleted = 0
• Open workflow: WHERE status = 'OPEN'
• Queue processing: WHERE processed_at IS NULL
• Sparse data: WHERE optional_code IS NOT NULL
• Hot subset: small active subset inside a huge historical table

Filtered index example

CREATE INDEX IX_orders_open_customer_date
                            ON dbo.orders(customer_id, order_date)
                            INCLUDE (total_amount, status)
                            WHERE status = 'OPEN'
                            AND is_deleted = 0;

Query must match filter

SELECT
                            order_id,
                            order_date,
                            total_amount
                            FROM dbo.orders
                            WHERE customer_id = @customer_id
                            AND status = 'OPEN'
                            AND is_deleted = 0
                            ORDER BY order_date DESC;

Why it is efficient

Full table:
                            100,000,000 rows

                            Open active orders:
                            350,000 rows

                            Filtered index:
                            only stores open active rows

                            Result:
                            smaller index
                            less memory
                            less I/O
                            faster maintenance
                            better selectivity

Columnstore indexes: analytics and compression

Columnstore indexes store data by columns instead of rows. They are excellent for analytical workloads: large scans, aggregations, fact tables, reporting and data warehouses. They can compress strongly and use batch mode execution, which can greatly reduce CPU for large sets.

Columnstore concepts

Columnstore architecture

Fact table
                            |
                            +-- Rowgroup 1
                            |     +-- column segment: date_key
                            |     +-- column segment: product_key
                            |     +-- column segment: quantity
                            |     +-- column segment: amount
                            |
                            +-- Rowgroup 2
                            |     +-- column segment: date_key
                            |     +-- column segment: product_key
                            |     +-- column segment: quantity
                            |     +-- column segment: amount
                            |
                            v
                            Compression + batch mode + segment elimination

Create clustered columnstore

CREATE CLUSTERED COLUMNSTORE INDEX CCI_fact_sales
                            ON dbo.fact_sales;

Create nonclustered columnstore

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_orders_analytics
                            ON dbo.orders (
                            customer_id,
                            order_date,
                            status,
                            total_amount
                            );

Statistics: the optimizer’s eyesight

Statistics describe data distribution. The optimizer uses them to estimate how many rows will flow through each operator. Bad estimates lead to bad joins, wrong memory grants, bad parallelism decisions, unnecessary scans and tempdb spills.

Statistics contain

• Header: metadata such as update time and row count.
• Density vector: information about uniqueness and combinations.
• Histogram: distribution of values for the leading statistics column.

Critical idea

The optimizer does not know the data perfectly.
                            It estimates.

                            Bad estimate:
                            expected rows = 1
                            actual rows   = 1,000,000

                            Possible consequences:
                            wrong join type
                            wrong memory grant
                            spills to tempdb
                            nested loops disaster
                            underestimated parallelism
                            plan regression

Inspect statistics

DBCC SHOW_STATISTICS (
                            'dbo.orders',
                            'IX_orders_customer_date'
                            );

Update statistics

UPDATE STATISTICS dbo.orders IX_orders_customer_date;

                            UPDATE STATISTICS dbo.orders WITH FULLSCAN;

                            EXEC sp_updatestats;

Statistics metadata

SELECT
                            s.name AS statistics_name,
                            s.auto_created,
                            s.user_created,
                            s.no_recompute,
                            sp.last_updated,
                            sp.rows,
                            sp.rows_sampled,
                            sp.modification_counter
                            FROM sys.stats AS s
                            CROSS APPLY sys.dm_db_stats_properties(
                            s.object_id,
                            s.stats_id
                            ) AS sp
                            WHERE s.object_id = OBJECT_ID('dbo.orders')
                            ORDER BY sp.last_updated DESC;

Fragmentation: real, but often over-obsessed

Fragmentation means index pages are not physically or logically ordered as efficiently as they could be, or that page density is poor. It can hurt scans and increase I/O, but it is not always the first cause of poor performance. Statistics, bad plans and missing indexes often matter more.

Types of issues

Maintenance options

REORGANIZE:
                            - lighter operation
                            - online
                            - incremental cleanup
                            - useful for moderate fragmentation

                            REBUILD:
                            - rebuilds index structure
                            - updates statistics with fullscan for index stats
                            - more expensive
                            - more log generation
                            - online availability depends on edition/options

                            UPDATE STATISTICS:
                            - often more important than defragmentation
                            - cheaper than rebuild
                            - can fix bad estimates

Example commands

ALTER INDEX IX_orders_customer_date
                            ON dbo.orders
                            REORGANIZE;

                            ALTER INDEX IX_orders_customer_date
                            ON dbo.orders
                            REBUILD WITH (
                            FILLFACTOR = 90,
                            SORT_IN_TEMPDB = ON
                            );

                            UPDATE STATISTICS dbo.orders IX_orders_customer_date;

Overlapping indexes example

IX_orders_customer:
                            (customer_id)

                            IX_orders_customer_date:
                            (customer_id, order_date)

                            IX_orders_customer_date_status:
                            (customer_id, order_date, status)

                            Possible issue:
                            first two may be redundant depending on workload.

                            DBA action:
                            compare usage
                            compare included columns
                            compare query patterns
                            consolidate carefully
                            test before dropping

Non-sargable query problem

-- Bad: function on indexed column
                            SELECT order_id
                            FROM dbo.orders
                            WHERE YEAR(order_date) = 2026;

                            -- Better: range predicate
                            SELECT order_id
                            FROM dbo.orders
                            WHERE order_date >= '2026-01-01'
                            AND order_date <  '2027-01-01';

                            Reason:
                            the second predicate can use index order.

Index usage stats

SELECT
                            OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
                            OBJECT_NAME(i.object_id) AS table_name,
                            i.name AS index_name,
                            i.type_desc,
                            s.user_seeks,
                            s.user_scans,
                            s.user_lookups,
                            s.user_updates
                            FROM sys.indexes AS i
                            LEFT JOIN sys.dm_db_index_usage_stats AS s
                            ON s.object_id = i.object_id
                            AND s.index_id = i.index_id
                            AND s.database_id = DB_ID()
                            WHERE i.object_id > 100
                            ORDER BY s.user_updates DESC, s.user_seeks ASC;

Index size report

SELECT
                            s.name AS schema_name,
                            o.name AS table_name,
                            i.name AS index_name,
                            i.type_desc,
                            SUM(a.total_pages) * 8 / 1024 AS total_mb,
                            SUM(a.used_pages) * 8 / 1024 AS used_mb,
                            SUM(a.data_pages) * 8 / 1024 AS data_mb
                            FROM sys.indexes AS i
                            JOIN sys.objects AS o
                            ON i.object_id = o.object_id
                            JOIN sys.schemas AS s
                            ON o.schema_id = s.schema_id
                            JOIN sys.partitions AS p
                            ON i.object_id = p.object_id
                            AND i.index_id = p.index_id
                            JOIN sys.allocation_units AS a
                            ON p.partition_id = a.container_id
                            WHERE o.is_ms_shipped = 0
                            GROUP BY s.name, o.name, i.name, i.type_desc
                            ORDER BY total_mb DESC;

Missing index suggestions

SELECT TOP (50)
                            DB_NAME(mid.database_id) AS database_name,
                            OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
                            migs.user_seeks,
                            migs.avg_total_user_cost,
                            migs.avg_user_impact,
                            mid.equality_columns,
                            mid.inequality_columns,
                            mid.included_columns
                            FROM sys.dm_db_missing_index_group_stats AS migs
                            JOIN sys.dm_db_missing_index_groups AS mig
                            ON migs.group_handle = mig.index_group_handle
                            JOIN sys.dm_db_missing_index_details AS mid
                            ON mig.index_handle = mid.index_handle
                            WHERE mid.database_id = DB_ID()
                            ORDER BY migs.avg_user_impact DESC;

Warning about missing index DMVs

Missing index DMVs:
                            - are suggestions, not truth
                            - ignore existing index consolidation
                            - can recommend many overlapping indexes
                            - reset after restart
                            - do not know write cost
                            - do not replace plan analysis

                            Use them as clues, not commands.

Foreign keys without supporting index

SELECT
                            OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name,
                            OBJECT_NAME(fk.parent_object_id) AS table_name,
                            fk.name AS foreign_key_name
                            FROM sys.foreign_keys AS fk
                            WHERE NOT EXISTS (
                            SELECT 1
                            FROM sys.index_columns AS ic
                            JOIN sys.foreign_key_columns AS fkc
                            ON fkc.parent_object_id = ic.object_id
                            AND fkc.parent_column_id = ic.column_id
                            WHERE ic.object_id = fk.parent_object_id
                            AND fkc.constraint_object_id = fk.object_id
                            );

Weekly index review

Weekly:
                            1. Top slow queries from Query Store
                            2. Top logical read queries
                            3. Missing index suggestions
                            4. Unused large indexes
                            5. Duplicate or overlapping indexes
                            6. Fragmentation on large scanned indexes
                            7. Statistics freshness
                            8. Write-heavy tables
                            9. tempdb spills caused by bad plans
                            10. Index changes after deployment

Before creating a new index

Ask:
                            1. Which query needs it?
                            2. What is the current plan?
                            3. What are current logical reads?
                            4. Is the predicate sargable?
                            5. Are statistics correct?
                            6. Does an existing index almost solve it?
                            7. Can we modify an existing index?
                            8. What is the write overhead?
                            9. How large will it be?
                            10. How will we prove success?

Query pipeline: from text to execution

SQL Server transforms a query through several stages before returning rows. Understanding this pipeline helps distinguish syntax problems, binding problems, optimization problems, execution problems and storage problems.

Pipeline

1. Client sends T-SQL batch
                            2. Parser validates syntax
                            3. Algebrizer resolves:
                            - object names
                            - column names
                            - data types
                            - permissions
                            4. Optimizer estimates costs
                            5. Optimizer selects execution plan
                            6. Execution engine runs operators
                            7. Storage engine reads or writes pages
                            8. Results are returned to client

Compilation versus execution

Important distinction

Estimated execution plan:
                            - based on optimizer estimates
                            - does not run the query
                            - useful before execution

                            Actual execution plan:
                            - collected during execution
                            - includes actual rows
                            - reveals estimate errors
                            - reveals spills and warnings

                            For tuning, actual plan is usually essential.

Execution plans: the DBA’s X-ray

An execution plan shows how SQL Server intends to access data and process the query. It exposes access methods, join types, row estimates, memory grants, sorts, scans, seeks, lookups, parallelism and warnings.

Important plan operators

Plan reading workflow

Read a plan like this:

                            1. Start with expensive operators
                            2. Check estimated rows vs actual rows
                            3. Look for warnings
                            - spills
                            - missing indexes
                            - implicit conversions
                            4. Check access methods
                            - seek
                            - scan
                            - lookup
                            5. Check join choices
                            6. Check memory grant
                            7. Check parallelism
                            8. Check predicates
                            9. Check sort operations
                            10. Validate with logical reads and duration

Estimated versus actual rows

Good estimate:
                            estimated rows = 1000
                            actual rows    = 1100

                            Bad estimate:
                            estimated rows = 1
                            actual rows    = 850000

                            Consequences:
                            wrong join type
                            wrong memory grant
                            wrong parallelism
                            spills
                            bad plan reuse

Cardinality estimation: the root of many plan choices

Cardinality estimation is the process of estimating how many rows will be returned by each operator. It is one of the most important parts of optimization. If row estimates are wrong, the optimizer can choose the wrong join type, wrong memory grant, wrong index strategy and wrong parallelism.

Why estimates go wrong

• Stale statistics: data changed but stats did not reflect it.
• Data skew: one value is extremely common, another is rare.
• Correlated columns: optimizer assumes independence where data is related.
• Non-sargable predicates: functions or expressions hide selectivity.
• Implicit conversions: column conversion prevents normal index use.
• Table variables: historically poor estimates in older compatibility levels.
• Local variables: unknown values can produce generic estimates.

Cardinality error diagram

Predicate:
                            WHERE customer_id = @customer_id

                            Actual data:
                            customer_id = 1001 -> 12 rows
                            customer_id = 9000 -> 4,500,000 rows

                            If optimizer compiles for 1001:
                            chooses nested loops + seeks

                            If reused for 9000:
                            nested loops become catastrophic

                            This is one form of parameter sniffing.

Estimate error impact

Underestimate:
                            - memory grant too small
                            - hash/sort spills to tempdb
                            - nested loops chosen incorrectly
                            - parallelism not chosen

                            Overestimate:
                            - memory grant too large
                            - concurrency reduced
                            - hash join chosen unnecessarily
                            - excessive parallelism

Statistics: the optimizer’s eyesight

Statistics describe the distribution of values in columns or indexes. Without good statistics, the optimizer is almost blind. It may choose the wrong join order, wrong join type, wrong memory grant or wrong access method.

Statistics components

Important rule

Statistics histogram exists only on the leading column.

                            Index:
                            (customer_id, order_date)

                            Histogram:
                            customer_id

                            Density:
                            may include combinations

                            Consequence:
                            key order matters for both access and estimation.

Inspect statistics

DBCC SHOW_STATISTICS (
                            'dbo.orders',
                            'IX_orders_customer_date'
                            );

Statistics metadata

SELECT
                            OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
                            OBJECT_NAME(s.object_id) AS table_name,
                            s.name AS stats_name,
                            s.auto_created,
                            s.user_created,
                            s.no_recompute,
                            sp.last_updated,
                            sp.rows,
                            sp.rows_sampled,
                            sp.modification_counter
                            FROM sys.stats AS s
                            CROSS APPLY sys.dm_db_stats_properties(
                            s.object_id,
                            s.stats_id
                            ) AS sp
                            WHERE s.object_id = OBJECT_ID('dbo.orders')
                            ORDER BY sp.last_updated DESC;

Update statistics

UPDATE STATISTICS dbo.orders IX_orders_customer_date;

                            UPDATE STATISTICS dbo.orders WITH FULLSCAN;

                            EXEC sp_updatestats;

Parameter sniffing: feature, not always bug

Parameter sniffing means SQL Server uses parameter values available at compilation time to optimize the plan. This is usually good: the optimizer can build a plan for the real value. It becomes a problem when the first compiled value is not representative and the cached plan is reused for very different values.

Classic scenario

Stored procedure:
                            dbo.get_orders_by_customer @customer_id

                            Data distribution:
                            customer 1001 -> 10 orders
                            customer 9000 -> 5,000,000 orders

                            Compilation:
                            first execution uses 1001
                            optimizer chooses nested loops + seeks

                            Later:
                            execution uses 9000
                            same plan reused
                            nested loops become disastrous

Symptoms

• Procedure is fast for one parameter and slow for another.
• Clearing plan cache temporarily changes performance.
• Recompiling fixes one case and breaks another.
• Query Store shows multiple plans with very different performance.
• Estimated rows and actual rows vary dramatically by parameter.

Remediation options

Query Store: the flight recorder for SQL Server performance

Query Store captures query text, plans, runtime statistics and wait statistics over time. It is essential for upgrade validation, regression detection, plan comparison, production tuning and evidence-based troubleshooting.

What Query Store stores

• Query text: normalized query statements.
• Query IDs: stable identifiers for tracked queries.
• Plan IDs: different execution plans for the same query.
• Runtime stats: duration, CPU, reads, writes, executions.
• Wait stats: wait categories associated with query execution.
• Plan forcing state: whether a plan is forced and whether forcing succeeded.

Enable Query Store

ALTER DATABASE YourDatabaseName
                            SET QUERY_STORE = ON;

                            ALTER DATABASE YourDatabaseName
                            SET QUERY_STORE (
                            OPERATION_MODE = READ_WRITE,
                            CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
                            DATA_FLUSH_INTERVAL_SECONDS = 900,
                            INTERVAL_LENGTH_MINUTES = 60,
                            MAX_STORAGE_SIZE_MB = 2048,
                            QUERY_CAPTURE_MODE = AUTO
                            );

Query Store workflow

Normal operations:
                            capture baseline

                            Incident:
                            find regressed query

                            Analysis:
                            compare old plan vs new plan

                            Mitigation:
                            tune query / update stats / index / force plan

                            Validation:
                            monitor runtime stats after fix

                            Governance:
                            review forced plans regularly

Plan forcing: useful, but not a cure-all

Plan forcing tells SQL Server to use a specific known plan for a query. It can be a powerful emergency mitigation when a regression appears after statistics changes, upgrade, parameter sniffing or plan cache churn. But it should not replace root cause analysis.

When plan forcing is useful

• A query suddenly regressed and Query Store shows a previous stable plan.
• Application code cannot be changed quickly.
• A production incident requires fast mitigation.
• The forced plan is tested and monitored.
• The root cause will be addressed later.

When plan forcing is risky

• Data distribution is changing rapidly.
• The old plan is good only for one parameter value.
• Indexes used by the plan may change or disappear.
• Forcing hides the real issue permanently.
• No one reviews forced plans after deployment.

Force a plan

EXEC sys.sp_query_store_force_plan
                            @query_id = 123,
                            @plan_id = 456;

Unforce a plan

EXEC sys.sp_query_store_unforce_plan
                            @query_id = 123,
                            @plan_id = 456;

Find forced plans

SELECT
                            q.query_id,
                            p.plan_id,
                            p.is_forced_plan,
                            p.force_failure_count,
                            p.last_force_failure_reason_desc,
                            qt.query_sql_text
                            FROM sys.query_store_plan AS p
                            JOIN sys.query_store_query AS q
                            ON p.query_id = q.query_id
                            JOIN sys.query_store_query_text AS qt
                            ON q.query_text_id = qt.query_text_id
                            WHERE p.is_forced_plan = 1
                            ORDER BY p.force_failure_count DESC;

Hints: surgical tools, not default style

Hints influence optimizer behavior. They can be useful when the optimizer lacks information or when an emergency mitigation is needed. But hints can also freeze a bad assumption and make future data changes dangerous.

Common hint categories

Examples

-- Compile for current parameter values
                            SELECT
                            order_id,
                            order_date,
                            total_amount
                            FROM dbo.orders
                            WHERE customer_id = @customer_id
                            OPTION (RECOMPILE);

                            -- Optimize for a representative value
                            SELECT
                            order_id,
                            order_date,
                            total_amount
                            FROM dbo.orders
                            WHERE customer_id = @customer_id
                            OPTION (OPTIMIZE FOR (@customer_id = 1001));

                            -- Limit parallelism for one query
                            SELECT
                            customer_id,
                            SUM(total_amount) AS revenue
                            FROM dbo.orders
                            GROUP BY customer_id
                            OPTION (MAXDOP 4);

Hint governance

Before adding a hint:
                            1. prove the problem
                            2. check statistics
                            3. check indexes
                            4. check Query Store history
                            5. test with representative parameters
                            6. document the reason
                            7. add review date
                            8. monitor after deployment

Waits and plans: connect symptoms to operators

Execution plans show the chosen strategy. Wait stats show where time is lost during execution. A good DBA uses both. A plan can look reasonable but wait on I/O. A query can wait on locks because another transaction is blocking it. A memory spill can appear both in the plan and through tempdb-related waits.

Wait interpretation

Tuning workflow

1. Identify user symptom
                            2. Find active requests
                            3. Check wait type
                            4. Capture actual execution plan
                            5. Compare estimated and actual rows
                            6. Check logical reads
                            7. Check Query Store history
                            8. Check recent plan changes
                            9. Validate stats and indexes
                            10. Test fix with representative workload

Wait stats query

SELECT TOP (30)
                            wait_type,
                            waiting_tasks_count,
                            wait_time_ms,
                            signal_wait_time_ms,
                            wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
                            FROM sys.dm_os_wait_stats
                            WHERE wait_type NOT LIKE 'SLEEP%'
                            AND wait_type NOT LIKE 'BROKER%'
                            AND wait_type NOT LIKE 'XE%'
                            AND wait_type NOT LIKE 'SQLTRACE%'
                            ORDER BY wait_time_ms DESC;

Top cached queries by CPU

SELECT TOP (25)
                            qs.execution_count,
                            qs.total_worker_time / 1000 AS total_cpu_ms,
                            qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_cpu_ms,
                            qs.total_elapsed_time / 1000 AS total_elapsed_ms,
                            qs.total_logical_reads,
                            qs.total_logical_writes,
                            SUBSTRING(
                            st.text,
                            (qs.statement_start_offset / 2) + 1,
                            ((CASE qs.statement_end_offset
                            WHEN -1 THEN DATALENGTH(st.text)
                            ELSE qs.statement_end_offset
                            END - qs.statement_start_offset) / 2) + 1
                            ) AS statement_text
                            FROM sys.dm_exec_query_stats AS qs
                            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
                            ORDER BY qs.total_worker_time DESC;

Current memory grants

SELECT
                            session_id,
                            request_id,
                            scheduler_id,
                            dop,
                            requested_memory_kb,
                            granted_memory_kb,
                            required_memory_kb,
                            used_memory_kb,
                            max_used_memory_kb,
                            wait_time_ms,
                            is_next_candidate
                            FROM sys.dm_exec_query_memory_grants
                            ORDER BY requested_memory_kb DESC;

Query Store plans for one query

SELECT
                            q.query_id,
                            p.plan_id,
                            p.is_forced_plan,
                            p.last_execution_time,
                            rs.count_executions,
                            rs.avg_duration / 1000.0 AS avg_duration_ms,
                            rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
                            rs.avg_logical_io_reads,
                            qt.query_sql_text
                            FROM sys.query_store_query AS q
                            JOIN sys.query_store_query_text AS qt
                            ON q.query_text_id = qt.query_text_id
                            JOIN sys.query_store_plan AS p
                            ON q.query_id = p.query_id
                            JOIN sys.query_store_runtime_stats AS rs
                            ON p.plan_id = rs.plan_id
                            WHERE q.query_id = 123
                            ORDER BY rs.avg_duration DESC;

Forced plans review

SELECT
                            q.query_id,
                            p.plan_id,
                            p.is_forced_plan,
                            p.force_failure_count,
                            p.last_force_failure_reason_desc,
                            qt.query_sql_text
                            FROM sys.query_store_plan AS p
                            JOIN sys.query_store_query AS q
                            ON p.query_id = q.query_id
                            JOIN sys.query_store_query_text AS qt
                            ON q.query_text_id = qt.query_text_id
                            WHERE p.is_forced_plan = 1
                            ORDER BY p.force_failure_count DESC;

Slow query workflow

1. Identify the exact query
                            2. Capture actual execution plan
                            3. Measure duration, CPU, reads
                            4. Check waits
                            5. Compare estimated and actual rows
                            6. Check Query Store history
                            7. Check stats freshness
                            8. Check index design
                            9. Test fix in representative conditions
                            10. Deploy with rollback option

Regression workflow

1. Open Query Store
                            2. Find regressed query
                            3. Compare old plan and new plan
                            4. Check recent changes:
                            - deployment
                            - stats update
                            - index change
                            - compatibility level
                            - parameter pattern
                            5. Apply mitigation:
                            - update stats
                            - tune index
                            - Query Store force plan
                            - Query Store hint
                            - code rewrite
                            6. Monitor after fix

ACID: the foundation of transaction correctness

Transactions exist to guarantee that a set of changes is applied correctly, even with many users, errors, crashes and concurrent operations. SQL Server uses the transaction log, locks, isolation rules and recovery mechanisms to implement ACID behavior.

Transaction design rules

• Keep transactions short: do not wait for user input inside a transaction.
• Touch data in consistent order: reduces deadlock probability.
• Use proper error handling: TRY/CATCH, XACT_STATE and THROW.
• Index predicates: reduce lock footprint and scan duration.
• Batch large modifications: avoid massive log, blocking and rollback pain.

Safe transaction template

CREATE OR ALTER PROCEDURE dbo.transfer_funds
                            @from_account_id int,
                            @to_account_id int,
                            @amount decimal(19,4)
                            AS
                            BEGIN
                            SET NOCOUNT ON;
                            SET XACT_ABORT ON;

                            BEGIN TRY
                            BEGIN TRAN;

                            UPDATE dbo.accounts
                            SET balance = balance - @amount
                            WHERE account_id = @from_account_id
                            AND balance >= @amount;

                            IF @@ROWCOUNT <> 1
                            THROW 50010, 'Source account missing or insufficient funds.', 1;

                            UPDATE dbo.accounts
                            SET balance = balance + @amount
                            WHERE account_id = @to_account_id;

                            IF @@ROWCOUNT <> 1
                            THROW 50011, 'Target account missing.', 1;

                            INSERT INTO dbo.account_movement (
                            from_account_id,
                            to_account_id,
                            amount,
                            created_at
                            )
                            VALUES (
                            @from_account_id,
                            @to_account_id,
                            @amount,
                            SYSUTCDATETIME()
                            );

                            COMMIT;
                            END TRY
                            BEGIN CATCH
                            IF XACT_STATE() <> 0
                            ROLLBACK;

                            THROW;
                            END CATCH;
                            END;

Set isolation level

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

                            BEGIN TRAN;

                            SELECT
                            order_id,
                            status,
                            total_amount
                            FROM dbo.orders
                            WHERE customer_id = 1001;

                            COMMIT;

Serializable example

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

                            BEGIN TRAN;

                            SELECT
                            *
                            FROM dbo.orders
                            WHERE customer_id = 1001
                            AND status = 'OPEN';

                            -- Range is protected from phantom inserts

                            COMMIT;

Isolation decision guide

Need maximum correctness for a small critical range?
                            -> SERIALIZABLE may be justified

                            Need normal OLTP behavior?
                            -> READ COMMITTED is common

                            Need fewer reader/writer blocks?
                            -> RCSI can help

                            Need transaction-level consistent snapshot?
                            -> SNAPSHOT can help

                            Need dirty reads for speed?
                            -> Usually a bad idea

                            Need reporting over OLTP?
                            -> Prefer replica, warehouse, RCSI or readable secondary

Lock types: what SQL Server protects

Locks are logical concurrency controls. They protect data correctness when multiple sessions access the same resources. Locks can be held on rows, keys, pages, tables, databases, metadata and ranges. The lock mode defines compatibility with other locks.

Common lock modes

Lock compatibility intuition

Shared locks:
                            many readers can coexist

                            Exclusive lock:
                            writer blocks other readers/writers
                            depending on isolation level

                            Update lock:
                            one session declares intent to update
                            reduces conversion deadlock risk

                            Intent locks:
                            table-level signals that lower-level locks exist

                            Range locks:
                            protect gaps between keys
                            used under SERIALIZABLE

Basic lock DMV

SELECT
                            request_session_id,
                            resource_type,
                            resource_database_id,
                            resource_associated_entity_id,
                            request_mode,
                            request_status,
                            request_owner_type
                            FROM sys.dm_tran_locks
                            ORDER BY request_session_id, resource_type, request_mode;

Lock hierarchy and escalation

SQL Server can lock at multiple levels: row, key, page, object, database and metadata. Fine-grained locks improve concurrency but consume memory. When too many locks are taken, SQL Server may escalate to a larger lock, commonly a table-level lock.

Lock hierarchy

Database
                            |
                            +-- Schema / metadata
                            |
                            +-- Table / object
                            |
                            +-- HoBT / partition
                            |
                            +-- Page
                            |
                            +-- Key / row

Why escalation matters

Before escalation:
                            update many rows
                            row locks on many keys
                            other sessions can still access other rows

                            After escalation:
                            table lock
                            many sessions blocked
                            concurrency drops sharply

Escalation risk factors

• Large updates or deletes: many rows touched in one transaction.
• Missing indexes: scan locks many rows instead of seeking targeted rows.
• Long transaction: locks are held longer.
• Serializable isolation: range locks increase footprint.
• Foreign key checks: missing FK indexes can broaden locking.
• Partitioning strategy: escalation behavior may interact with partitions.

Batching pattern

WHILE 1 = 1
                            BEGIN
                            DELETE TOP (5000)
                            FROM dbo.audit_log
                            WHERE created_at < DATEADD(day, -180, SYSUTCDATETIME());

                            IF @@ROWCOUNT = 0
                            BREAK;

                            WAITFOR DELAY '00:00:01';
                            END;

Blocking: normal mechanism, abnormal when excessive

Blocking happens when one session holds a lock that another session needs. This is not automatically bad. It is how SQL Server protects correctness. The problem appears when blocking lasts too long, forms chains, consumes workers, or stops critical business operations.

Blocking chain example

Session 51
                            holds X lock on dbo.orders row
                            transaction open for 8 minutes
                            |
                            blocks
                            v
                            Session 62
                            waiting to update same customer
                            |
                            blocks
                            v
                            Session 74
                            report waiting behind session 62

                            Root blocker:
                            session 51

Blocking causes

Deadlocks: when sessions block each other in a cycle

A deadlock occurs when two or more sessions hold resources that the others need, creating a cycle that cannot resolve by waiting. SQL Server detects the cycle and kills one victim with error 1205 so the other transaction can continue.

Classic deadlock

Session A:
                            UPDATE customer 1001
                            then wants order 500

                            Session B:
                            UPDATE order 500
                            then wants customer 1001

                            Cycle:
                            A waits for B
                            B waits for A

                            SQL Server:
                            chooses deadlock victim
                            returns error 1205 to victim session

Common causes

• Different access order: sessions update tables in different sequences.
• Missing indexes: scans take more locks and increase overlap.
• Long transactions: locks are held longer.
• Serializable range locks: gap protection increases conflicts.
• Triggers: hidden extra access paths create cycles.
• Foreign key validation: missing indexes on child tables can increase locking.

Prevention rules

Reduce deadlocks:
                            1. Standardize object access order
                            2. Keep transactions short
                            3. Add targeted indexes
                            4. Avoid user interaction in transaction
                            5. Reduce scan footprint
                            6. Use retry logic in application
                            7. Review triggers
                            8. Review isolation level
                            9. Batch large modifications
                            10. Capture and analyze deadlock graph

RCSI and SNAPSHOT: reducing reader/writer blocking

Row versioning allows readers to read older committed versions of rows instead of waiting behind writers. SQL Server stores row versions in tempdb version store. This can dramatically reduce reader/writer blocking, but it increases tempdb dependency and changes concurrency behavior.

RCSI versus SNAPSHOT

When row versioning helps

• Reporting queries block OLTP writers or are blocked by writers.
• Read-heavy applications suffer from read/write contention.
• Business accepts committed snapshot semantics.
• tempdb is sized and monitored seriously.

Row versioning diagram

Writer updates row
                            |
                            +-- new row version in data page
                            |
                            +-- old committed version stored in tempdb
                            |
                            v
                            Reader under RCSI
                            |
                            +-- does not wait for writer
                            +-- reads older committed version
                            |
                            v
                            Less blocking
                            but more tempdb version store usage

Enable options

ALTER DATABASE YourDatabaseName
                            SET READ_COMMITTED_SNAPSHOT ON
                            WITH ROLLBACK IMMEDIATE;

                            ALTER DATABASE YourDatabaseName
                            SET ALLOW_SNAPSHOT_ISOLATION ON;

Use SNAPSHOT isolation

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

                            BEGIN TRAN;

                            SELECT
                            order_id,
                            status,
                            total_amount
                            FROM dbo.orders
                            WHERE customer_id = 1001;

                            COMMIT;

Latches: internal lightweight synchronization

Locks protect logical data consistency. Latches protect internal memory structures while SQL Server is reading or changing them. Latch waits are not solved by changing isolation level. They often indicate hot pages, allocation contention, tempdb contention, or very high insert pressure on the same physical area.

Locks versus latches

Common latch scenarios

PAGELATCH on tempdb:
                            - temp table allocation contention
                            - heavy tempdb object creation
                            - allocation map contention

                            PAGELATCH on user database:
                            - hot last page insert
                            - sequential clustered key under extreme concurrency
                            - hot index page
                            - queue table hotspot

                            PAGEIOLATCH:
                            - waiting for page from disk
                            - storage latency
                            - memory pressure
                            - scan-heavy workload

Latch diagnosis mindset

Do not confuse:
                            LCK_M_*      -> logical blocking
                            PAGELATCH_* -> in-memory page contention
                            PAGEIOLATCH_* -> waiting for disk I/O

                            Different waits, different fixes.

Optimized locking and modern concurrency improvements

Recent SQL Server versions introduce improvements that reduce lock memory, lock duration, and concurrency overhead in some workloads. These features can help, but they do not replace proper transaction design, correct indexing, short transactions and workload testing.

What modern locking improvements try to solve

• Reduce lock footprint: fewer locks held for some operations.
• Improve concurrency: less reader/writer or writer/writer interference in supported scenarios.
• Reduce memory pressure: fewer lock objects can reduce lock memory consumption.
• Improve high-throughput OLTP: especially when combined with good indexing and short transactions.

Important warning

Optimized locking is not a license to write bad transactions.

                            Still mandatory:
                            - short transactions
                            - good indexes
                            - consistent access order
                            - no user interaction inside transaction
                            - no massive unbatched updates
                            - deadlock monitoring
                            - realistic load testing

Concurrency checklist for modern versions

Before relying on modern locking improvements:

                            1. Identify current blocking pattern
                            2. Capture wait stats
                            3. Capture deadlock graphs
                            4. Check isolation level
                            5. Check RCSI / SNAPSHOT usage
                            6. Check transaction duration
                            7. Check index access path
                            8. Check lock escalation
                            9. Check tempdb version store
                            10. Replay real workload after upgrade

Version-sensitive topics

Active blocking

SELECT
                            r.session_id,
                            r.blocking_session_id,
                            r.wait_type,
                            r.wait_time,
                            r.wait_resource,
                            r.status,
                            r.command,
                            r.cpu_time,
                            r.total_elapsed_time,
                            DB_NAME(r.database_id) AS database_name,
                            t.text AS sql_text
                            FROM sys.dm_exec_requests AS r
                            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
                            WHERE r.blocking_session_id <> 0
                            ORDER BY r.wait_time DESC;

Current locks

SELECT
                            request_session_id,
                            resource_type,
                            resource_database_id,
                            resource_description,
                            request_mode,
                            request_status,
                            request_owner_type
                            FROM sys.dm_tran_locks
                            ORDER BY request_session_id, resource_type, request_mode;

Sessions with open transactions

SELECT
                            s.session_id,
                            s.login_name,
                            s.host_name,
                            s.program_name,
                            s.open_transaction_count,
                            r.status,
                            r.command,
                            r.wait_type,
                            r.total_elapsed_time
                            FROM sys.dm_exec_sessions AS s
                            LEFT JOIN sys.dm_exec_requests AS r
                            ON s.session_id = r.session_id
                            WHERE s.open_transaction_count > 0
                            ORDER BY s.open_transaction_count DESC;

Wait stats for locks and latches

SELECT TOP (50)
                            wait_type,
                            waiting_tasks_count,
                            wait_time_ms,
                            signal_wait_time_ms,
                            wait_time_ms - signal_wait_time_ms AS resource_wait_ms
                            FROM sys.dm_os_wait_stats
                            WHERE wait_type LIKE 'LCK_M%'
                            OR wait_type LIKE 'PAGELATCH%'
                            OR wait_type LIKE 'PAGEIOLATCH%'
                            OR wait_type IN ('THREADPOOL', 'WRITELOG')
                            ORDER BY wait_time_ms DESC;

tempdb version store

SELECT
                            SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
                            SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
                            SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
                            SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
                            FROM tempdb.sys.dm_db_file_space_usage;

Transaction log usage

SELECT
                            DB_NAME(database_id) AS database_name,
                            database_transaction_log_bytes_used / 1024 / 1024 AS log_used_mb,
                            database_transaction_log_bytes_reserved / 1024 / 1024 AS log_reserved_mb
                            FROM sys.dm_tran_database_transactions
                            ORDER BY log_used_mb DESC;

Daily checks

Daily:
                            1. Top blocking sessions
                            2. Deadlock count
                            3. Long open transactions
                            4. LCK_M waits
                            5. PAGELATCH waits
                            6. tempdb version store size
                            7. Failed jobs caused by blocking
                            8. Query Store regressions
                            9. Log reuse wait
                            10. Application timeout reports

Design review checklist

Before deploying write-heavy code:
                            1. Transaction scope reviewed
                            2. Access order documented
                            3. Required indexes present
                            4. Isolation level justified
                            5. Deadlock retry implemented
                            6. Batch size tested
                            7. Lock footprint measured
                            8. tempdb impact tested
                            9. Rollback duration considered
                            10. Monitoring added

RPO and RTO: the business contract

RPO and RTO are not technical decoration. They are the business contract that decides the architecture. A system with RPO near zero and RTO under minutes needs very different design, budget, automation and testing from a system where several hours of downtime are acceptable.

Definitions

RPO / RTO diagram

Timeline:

                            T0 ---------------- T1 ---------------- T2
                            |                  |                   |
                            last protected     failure             service restored
                            transaction        occurs

                            RPO:
                            data gap between last protected point and failure

                            RTO:
                            time between failure and restored service

                            Example:
                            last log backup: 10:55
                            failure:         11:00
                            service back:    11:12

                            RPO = 5 minutes
                            RTO = 12 minutes

Business categories

Tier 0:
                            payment, orders, critical production
                            RPO: near zero
                            RTO: seconds/minutes

                            Tier 1:
                            operational business apps
                            RPO: minutes
                            RTO: minutes/hour

                            Tier 2:
                            reporting, internal tools
                            RPO: hours
                            RTO: hours

                            Tier 3:
                            archive, non-critical
                            RPO: day
                            RTO: day or more

Always On Availability Groups

Always On Availability Groups provide database-level high availability and disaster recovery. A primary replica sends transaction log records to secondary replicas. Depending on commit mode, secondaries can be synchronous for local HA or asynchronous for remote DR.

Main components

• Availability Group: logical group of databases that fail over together.
• Primary replica: accepts read/write workload.
• Secondary replica: receives log blocks and hardens/replays them.
• Availability database: database participating in the AG.
• Listener: virtual network name used by applications.
• Endpoint: communication channel between replicas.
• Commit mode: synchronous or asynchronous.
• Failover mode: automatic or manual depending on design.

AG architecture

Application
                            |
                            v
                            AG Listener
                            |
                            v
                            Primary Replica
                            |
                            +-- DB_A
                            +-- DB_B
                            +-- DB_C
                            |
                            | synchronous commit
                            v
                            Secondary Replica - local HA
                            |
                            | asynchronous commit
                            v
                            Secondary Replica - remote DR

                            Optional:
                            readable secondary
                            backup preference
                            read-only routing

Commit flow simplified

Transaction on primary
                            |
                            v
                            Log record generated
                            |
                            v
                            Primary hardens log locally
                            |
                            +-- sync replica:
                            |     waits for secondary harden
                            |
                            +-- async replica:
                            does not wait for remote harden

                            Commit acknowledged based on commit mode.

Synchronous versus asynchronous commit

Commit mode is one of the most important design choices. Synchronous commit can reduce or eliminate data loss, but adds latency because the primary waits for the synchronous secondary to harden log records. Asynchronous commit is better for long distance DR, but the remote replica can lag behind.

Commit latency model

Synchronous commit latency includes:
                            - primary log write
                            - network send
                            - secondary log harden
                            - acknowledgement back to primary

                            If network or secondary log disk is slow:
                            primary transactions slow down.

Topology examples

Local HA:
                            Primary A
                            |
                            | synchronous commit
                            v
                            Secondary B
                            same site or low-latency zone

                            Remote DR:
                            Primary A
                            |
                            | asynchronous commit
                            v
                            Secondary C
                            remote region or DR site

                            Hybrid:
                            A -> B synchronous for HA
                            A -> C asynchronous for DR

Replica lag concepts

Important measurements:
                            - log send queue
                            - redo queue
                            - synchronization state
                            - synchronization health
                            - last hardened LSN
                            - last redone LSN
                            - redo rate
                            - send rate

                            Large queue:
                            secondary is behind
                            RPO risk increases
                            failover may take longer

AG Listener: the application entry point

The listener is a virtual network name and IP endpoint that allows applications to connect to the current primary replica without hardcoding a physical server name. It is one of the key pieces that makes failover manageable from the application point of view.

Listener responsibilities

• Abstract primary replica: application connects to listener, not server node.
• Support failover: listener moves with the AG role.
• Support read-only routing: direct read-only connections to readable secondaries.
• Reduce manual changes: no connection string rewrite during failover.
• Require client retry logic: active connections can still break during failover.

Listener diagram

Application connection string
                            |
                            v
                            tcp:SalesAGListener,1433
                            |
                            v
                            Current primary replica
                            |
                            +-- before failover: SQLNODE01
                            |
                            +-- after failover:  SQLNODE02

                            Application should:
                            - use listener name
                            - set connection timeout properly
                            - retry transient failures
                            - avoid hardcoded node names

Connection string ideas

Server=SalesAGListener,1433;
                            Database=SalesDB;
                            Integrated Security=True;
                            MultiSubnetFailover=True;
                            ApplicationIntent=ReadWrite;

                            For readable secondary:
                            ApplicationIntent=ReadOnly;

Failover Cluster Instance

A Failover Cluster Instance protects the SQL Server instance as a whole. The instance can fail over between cluster nodes, usually with shared storage. From the application viewpoint, the SQL Server instance name remains stable while the active node changes.

FCI architecture

Windows Server Failover Cluster
                            |
                            +-- Node 1
                            |     +-- SQL Server active
                            |
                            +-- Node 2
                            |     +-- SQL Server passive
                            |
                            +-- Shared storage
                            +-- data files
                            +-- log files
                            +-- system databases

                            Failover:
                            SQL Server stops on Node 1
                            shared storage moves
                            SQL Server starts on Node 2

FCI versus AG

FCI strengths

• Protects the entire SQL Server instance.
• Application connection model can be simple.
• Good for applications expecting instance-level continuity.
• SQL Agent jobs and instance-level objects remain with the instance.

Log Shipping: simple, robust DR

Log shipping is one of the most reliable and understandable SQL Server DR patterns. The primary database takes transaction log backups. Those backups are copied to a secondary server and restored there with NORECOVERY or STANDBY mode.

Log shipping pipeline

Primary database
                            |
                            +-- Full backup initializes secondary
                            |
                            +-- Transaction log backup job
                            |
                            v
                            Backup share
                            |
                            v
                            Copy job on secondary
                            |
                            v
                            Restore job on secondary
                            |
                            v
                            Secondary database
                            - restoring mode
                            - or standby read-only mode

Why log shipping still matters

• Simple: backup, copy, restore.
• Robust: less moving parts than complex HA architectures.
• Cheap: often lower license and infrastructure complexity.
• Delayed restore possible: useful against accidental delete or bad deployment.
• Good for DR: especially when RTO/RPO are not ultra-aggressive.

Limitations

• Failover is usually manual.
• RPO depends on log backup frequency and copy/restore delay.
• RTO includes tail-log backup, restore final logs, application redirection.
• Secondary is not continuously writable.

Backup and restore: the foundation of all recovery

No HA architecture replaces backups. Backups are the only reliable answer to many incidents: accidental delete, bad deployment, corruption, ransomware, logical error, data poisoning, application bug or delayed discovery of a problem.

Backup types

Restore chain example

Restore sequence for point-in-time recovery:

                            1. Restore full backup WITH NORECOVERY
                            2. Restore latest differential WITH NORECOVERY
                            3. Restore log backups in order WITH NORECOVERY
                            4. Restore final log to target time WITH RECOVERY

                            Example:
                            full: Sunday 00:00
                            diff: Monday 12:00
                            logs: every 15 minutes
                            restore target: Monday 14:37

Restore commands

RESTORE DATABASE SalesDB
                            FROM DISK = 'B:\backup\SalesDB_full.bak'
                            WITH NORECOVERY;

                            RESTORE DATABASE SalesDB
                            FROM DISK = 'B:\backup\SalesDB_diff.bak'
                            WITH NORECOVERY;

                            RESTORE LOG SalesDB
                            FROM DISK = 'B:\backup\SalesDB_log_1430.trn'
                            WITH NORECOVERY;

                            RESTORE LOG SalesDB
                            FROM DISK = 'B:\backup\SalesDB_log_1445.trn'
                            WITH STOPAT = '2026-05-06T14:37:00',
                            RECOVERY;

Logical corruption timeline

10:00 deployment starts
                            10:05 bad script deletes rows
                            10:06 AG replicates delete
                            10:10 users report missing data

                            Failover result:
                            delete already exists on secondary

                            Real recovery:
                            restore backup to 10:04
                            extract missing data
                            or point-in-time restore
                            or use delayed log shipping copy

Ransomware recovery mindset

Required protections:
                            - immutable backups
                            - offline backup copy
                            - separate credentials
                            - backup encryption
                            - restore test in isolated network
                            - documented clean-room recovery
                            - identity recovery plan
                            - application secrets recovery
                            - DNS and network recovery
                            - communication plan

Failover runbook: planned AG failover

Planned AG failover runbook:

                            1. Announce maintenance window
                            2. Confirm replicas are healthy
                            3. Confirm synchronous replica is synchronized
                            4. Pause non-critical SQL Agent jobs
                            5. Check application connection string uses listener
                            6. Run pre-failover smoke test
                            7. Perform planned failover
                            8. Confirm new primary role
                            9. Confirm databases are online
                            10. Test application login and critical transaction
                            11. Check SQL Agent jobs on new primary
                            12. Check linked servers and external dependencies
                            13. Monitor waits, errors and AG health
                            14. Document real RTO
                            15. Communicate completion

Emergency DR activation runbook

Emergency DR runbook:

                            1. Declare incident severity
                            2. Confirm primary site status
                            3. Determine data health
                            4. Estimate data loss
                            5. Get business approval if data loss possible
                            6. Promote DR replica or restore backups
                            7. Redirect application traffic
                            8. Validate identity, network, DNS and secrets
                            9. Execute smoke tests
                            10. Resume critical jobs only
                            11. Monitor error rates and performance
                            12. Communicate new operating mode
                            13. Preserve evidence and logs
                            14. Plan failback or rebuild primary
                            15. Produce incident report

AG replica health

SELECT
                            ag.name AS availability_group_name,
                            ar.replica_server_name,
                            ars.role_desc,
                            ars.synchronization_health_desc,
                            ars.connected_state_desc,
                            ars.operational_state_desc,
                            ar.availability_mode_desc,
                            ar.failover_mode_desc
                            FROM sys.availability_groups AS ag
                            JOIN sys.availability_replicas AS ar
                            ON ag.group_id = ar.group_id
                            JOIN sys.dm_hadr_availability_replica_states AS ars
                            ON ar.replica_id = ars.replica_id
                            ORDER BY ag.name, ar.replica_server_name;

AG database queues

SELECT
                            DB_NAME(drs.database_id) AS database_name,
                            ar.replica_server_name,
                            drs.synchronization_state_desc,
                            drs.synchronization_health_desc,
                            drs.database_state_desc,
                            drs.is_suspended,
                            drs.suspend_reason_desc,
                            drs.log_send_queue_size,
                            drs.redo_queue_size,
                            drs.log_send_rate,
                            drs.redo_rate
                            FROM sys.dm_hadr_database_replica_states AS drs
                            JOIN sys.availability_replicas AS ar
                            ON drs.replica_id = ar.replica_id
                            ORDER BY database_name, ar.replica_server_name;

Last backups

SELECT
                            d.name AS database_name,
                            d.recovery_model_desc,
                            MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full_backup,
                            MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff_backup,
                            MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log_backup
                            FROM sys.databases AS d
                            LEFT JOIN msdb.dbo.backupset AS b
                            ON d.name = b.database_name
                            GROUP BY d.name, d.recovery_model_desc
                            ORDER BY d.name;

Log reuse wait

SELECT
                            name AS database_name,
                            recovery_model_desc,
                            log_reuse_wait_desc
                            FROM sys.databases
                            ORDER BY name;

SQL Agent failed jobs

SELECT TOP (50)
                            j.name AS job_name,
                            h.run_date,
                            h.run_time,
                            h.run_duration,
                            h.message
                            FROM msdb.dbo.sysjobhistory AS h
                            JOIN msdb.dbo.sysjobs AS j
                            ON h.job_id = j.job_id
                            WHERE h.run_status = 0
                            ORDER BY h.instance_id DESC;

Daily checks

Daily:
                            1. Replica health
                            2. Database synchronization state
                            3. Log send queue
                            4. Redo queue
                            5. Failed SQL Agent jobs
                            6. Last full/diff/log backup
                            7. Log reuse wait
                            8. Listener availability
                            9. Error log HA messages
                            10. Monitoring alert status

Monthly checks

Monthly:
                            1. Restore test
                            2. Planned failover test
                            3. Application reconnect test
                            4. SQL Agent job validation after failover
                            5. Login and credential sync review
                            6. Linked server validation
                            7. RPO/RTO report
                            8. DR capacity review
                            9. Backup retention review
                            10. Runbook update