Performance & Indexing

1. Indexes

Indexes make lookups much faster, but they're not a free lunch — they cost storage space and slow down write operations because they need to be kept up to date too.

Without an index = a full table scan: the database goes through every record, not just for = but also for <, > and sometimes LIKE.

Design Guidelines

Create an index for columns that frequently appear in JOIN or WHERE
Limit indexes with multiple columns
Avoid indexing small tables (overkill)
Avoid indexing very large tables with many inserts/updates

Types of Indexes

Type	Description
Heap	No index — data unorganized
Clustered	Data sorted and stored in a tree structure
Nonclustered	Separate tree structure with pointers to the data

2. Heap Index

A simple data structure with no logical order.

Contains a number of pages of data
Finding data = scanning all the data
New data can be stored anywhere (an existing page with room, or a new page)
→ Data fragments quickly → worse performance

3. Clustered Index

Organizes the data in a balanced tree structure (B-tree).

Starts from a single root page
Contains 1 or more intermediate levels
The data itself sits in the leaf pages, sorted by the clustered key
Max. 16 columns in 1 key
Combined key max. 900 bytes

Downside: Page Split

When a page is full: 1. The page is split 2. Half the records move to a new page 3. → Fragmentation is possible

Solutions for Fragmentation

Solution	Description
`FILLFACTOR`	Determines what % of a page may be filled before splitting
`PAD_INDEX`	Also applies FILLFACTOR to intermediate index pages

-- Check fragmentation
ALTER INDEX ALL ON dbo.Employees REORGANIZE;

-- Full rebuild
ALTER INDEX ALL ON dbo.Employees REBUILD;

4. Nonclustered Index

Has a tree structure similar to the clustered index, but:

No data in the index — only a pointer to the location in the clustered index
To find a row: traverse the index → determine the location → fetch the data via an RID lookup

5. Creating an Index

Via SSMSVia T-SQL

Object Explorer → table → Indexes → right-click → New Index → choose a type

Types: Clustered Index, Non-Clustered Index, Clustered Columnstore Index, ...

USE ClassicModels;
GO

CREATE NONCLUSTERED INDEX idx_nc_lastname
    ON dbo.Employees(lastName);
GO

6. Using an Index

Efficient queries start with WHERE clauses to filter data — WHERE clauses make use of indexes.

The execution plan shows how the data was looked up (Index Scan, Clustered Index Scan, Index Seek, ...).

Query Optimizer

The query optimizer decides independently whether an available index is used, based on how the WHERE clause is written.

Tip

Use IN (value1, value2) or = value instead of OR or calculations on the column — the optimizer will pick an index more often.

Forcing an Index

SELECT *
FROM   Employees WITH (INDEX([idx_nc_lastname]))
WHERE  lastName = 'Patterson';

Querying Index Usage

USE ClassicModels;
GO

SELECT object_name(S.object_id) AS table_name,
       I.name                   AS index_name,
       S.user_seeks,
       S.user_scans,
       S.user_lookups
FROM   sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS I
    ON S.object_id = I.object_id
    AND S.index_id = I.index_id
WHERE  S.object_id = object_id(N'dbo.Employees', N'U');
GO

7. Performance

Speed

Query performance depends on much more than just the query itself: - The server (CPU, RAM, I/O, ...) - Amount of data - Load (queries/second) - Contention (locking) - Query and schema optimization (including via indexes) - Caching

Storage Space

How much space a database takes up depends on: - Amount of data - Data types - Indexes - Cleanup (removing old data, freeing resources)

8. Caching

The database caches data automatically — this can't be turned off.

To test a query without cache:

-- 1. Write dirty pages to disk
CHECKPOINT;

-- 2. Clear the memory buffer (without restarting the service)
DBCC DROPCLEANBUFFERS;

-- 3. Remove all execution plans from memory
DBCC FREEPROCCACHE;

9. Querying Query History

Method	Description
`sys.dm_exec_requests`	Currently running queries
`sys.dm_exec_query_stats`	Statistics for cached query plans
`sys.dm_exec_sql_text`	Text of cached SQL queries
`sys.dm_exec_query_plan`	Execution plan of cached queries
Activity Monitor	Graphical tool in SSMS
SQL Server Profiler	Trace tool
Extended Events	Lightweight event monitoring
Query Store	Built-in query history (from SQL Server 2016 on)

Querying Running Queries

SELECT r.start_time          [Start Time],
       session_id             [SPID],
       db_name(database_id)  [Database],
       substring(t.text, (r.statement_start_offset/2)+1,
           case when statement_end_offset = -1 OR statement_end_offset = 0
               then (datalength(t.Text)-r.statement_start_offset/2)+1
               else (r.statement_end_offset-r.statement_start_offset)/2+1
           end) [Executing SQL],
       status, command, wait_type,
       wait_time, wait_resource, last_wait_type
FROM   sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE  session_id != @@SPID
AND    session_id > 50
ORDER BY r.start_time;

10. Activity Monitor

A graphical overview of server activity in SSMS.

Via SSMS: right-click the server → Activity Monitor

Sections: - Overview (CPU, waiting tasks, batch requests per second) - Processes — all active processes; right-click → details for the T-SQL of that session - Resource Waits - Data File I/O - Recent Expensive Queries — high CPU or logical reads - Active Expensive Queries

11. Query Store

Provides insight into query plan selection and performance — available from SQL Server 2016 on.

Keeps a record of: - Executed queries - Query plans used - Runtime statistics

Information is broken into time windows → comparisons are possible.

3 storage components: 1. Execution plan information 2. Execution statistics 3. Wait statistics

Enabling It

Via SSMSVia T-SQL

Right-click the database → Properties → Query Store → Operation Mode: Read write

USE ClassicModels;
GO

ALTER DATABASE ClassicModels
    SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
GO

Query Store Reports (via SSMS)

Report	Contents
Regressed Queries	Queries whose performance has degraded
Overall Resource Consumption	Total resource usage per time period
Top Resource Consuming Queries	The heaviest queries (CPU, duration, memory)
Queries With Forced Plans	Queries with a forced execution plan
Queries With High Variation	Queries with widely varying performance
Query Wait Statistics	Wait times per category
Tracked Queries	Specifically tracked queries

12. Event Viewer

The SQL Server event viewer contains user-defined events and system events.

Via SSMS: Object Explorer → Management → SQL Server Logs → right-click → View → SQL Server Log

Contains: startup timing info, configuration settings, error messages, warnings.