SQL Server Performance Tuning Starter Kit Randolph West

SQL Server Performance Tuning Starter Kit Randolph West | Born SQL

Please Support Our Sponsors SQL Saturday is made possible with the generous support of these sponsors. You can support them by opting-in and visiting them in the sponsor area.

Don’t forget to silence your phone

SQL Server Internals The Quick and Dirty Version for the Database Engine

Basic Internals Transaction Log File (LDF) Virtual Log Files Data File (MDF, NDF) Data Page (8 KB in size) Indexes (Clustered, Non-Clustered) Statistics (Cardinality Estimator) Buffer Pool

Basic Internals: Transaction Log File Group Database Transaction Log File (LDF: Log Data File) VLF VLF VLF Commit on Write VLF

Basic Internals: Data File Transaction Log File (LDF: Log Data File) Database File Group Data File (MDF: Main Data File) (NDF: Non-Primary Data File) 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 x 8 KB pages = 1 extent (64 KB)

Basic Internals: Page Header Rows ID ID ID Paul Randal (SQLskills. com): 1: 2: 3: 4: 6: 5: … … … 5 6 Anatomy of a Page http: //goo. gl/g. WNj. TO 4 3 2 1 Slot Array

Basic Internals: Tables Col. ID | Page. ID 1 | 2 Page 3 7 | 6 Col. ID | Page. ID 1 | 1 Page 2 Col. ID | Page. ID 7 | 5 4 | 4 Col. ID | Province 1 | AB Page 6 10 | 7 Col. ID | Province 4 | ON Col. ID | Province 7 | SK Col. ID | Province 10 | QC 2 | ON 3 | ON 5 | AB 6 | SK 8 | YT 9 | YT 11 | SK 12 | NU Page 1 Page 4 Page 5 Page 7 Clustered on Col. ID

Basic Internals: Indexes Province | Page. ID AB | 1 ON | Province | AB | Col. ID 1 AB | 5 NU | 12 Page 1 Indexed on Province AB | 2 QC | 6 Page 2 Page 3 Province | Page. ID QC | 5 4 SK | Province | ON | Col. ID 2 ON | 3 4 Page 4 Province | Col. ID QC | 10 SK | 6 7 Page 5 Page 6 5 Province | Col. ID SK | 11 YT | 8 9 Page 7

Basic Internals: Heap id | indid First IAM = indid 0 sysindexes Extent 110 111 112 113 | Bitmap | 1 | 0 | 1 Index Allocation Map Table Data xxxxx | xxxxx xxxxx | xxxxx xxxxx | xxxxx Extent 110 Extent 111 Extent 112 Extent 113

Basic Internals: Indexes (Contd. ) Heap No B-tree, no links → all info stored in Index Allocation Map (IAM) Clustered Index B-tree, the index is the data, double-linked list Non-Clustered Index (“a smaller table”) Unique Indexes and Constraints Filtered Index (WHERE clause → “an even smaller table”) Indexed View (persisted data) Fill Factor (it doesn’t always work the way you think it does) https: //goo. gl/E 0 Bsr. B Fragmented Indexes? Don’t bother rebuilding*. Update statistics instead.

Basic Internals: Statistics Query Magic Happens Here Distribution of Data (up to 200 steps) Density of Data (rows per step) Tipping Point (selectivity of data) Auto-updated after 20% plus 500 rows changed SQL Server 2016 is more intelligent All query plan costs are based on these values SQL Server 2014 Cardinality Estimator DBCC SHOW_STATISTICS Read more by Gail Shaw, Kimberly L. Tripp and Joe Sack

How Do You Make SQL Server Go Faster? Hint: put it all in memory

Performance vs Storage CPU Cache (MB in size) L 1 cache (128 KB) - 700 GB/s L 4 cache (128 MB) - 40 GB/s 3 x RAM (GB in size) DDR 3 – 12 GB/s 20 x Disk (TB in size) Solid State – 600 MB/s 4 x Network (∞ in size) 1 Gbps Ethernet – 125 MB/s

Put Your Database in Memory Should I Buy More RAM? Put your database in the Buffer Pool < $10 k for 384 GB* Yes * Canadian dollars No

Where Do I Begin?

Where Do I Begin? Hardware Configuration Operating System SQL Server Configuration Monitoring Tools

Hardware CPU (SMP or NUMA? ) RAM (memory pressure? ) Storage (many options) Network (SAN? TCP offloading? )

Operating System Physical or Virtual Version and Edition Drive allocation Also cluster size, partition alignment Instant File Initialization Power Saving What else is running on the server?

SQL Server Configuration Version, Edition, Build, and Licence Part of a Cluster, Mirror or Availability Group? Trace Flags (1118, 1117*, 3226, possibly 4199) sp_configure Cost Threshold for Parallelism? Appropriate to workload. Max Degree of Parallelism (MAXDOP)? Cores in NUMA node. Optimize for Ad-Hoc Queries? Turn it on. Max Server Memory? Appropriate to environment. Backup Compression? Turn it on. Lightweight Pooling and Priority Boost? Turn it off. Agent Jobs Maintenance tasks System Databases

User Databases Number of Databases Data and Transaction Log file allocation File growth, free space, location, VLFs I/O Performance Latency < 10 ms Recovery Mode Compatibility Level Page Verify Option SQL Server 2014+ Always be CHECKSUM Auto-Update Statistics Always be on

Monitoring Tools DO YOU HAVE A BASELINE? Dynamic Management Views Missing Indexes: sys. dm_db_missing_index_details sys. dm_db_missing_index_groups Wait Statistics: sys. dm_os_wait_stats Sessions: sys. dm_exec_sessions Free Tools sp_Who. Is. Active (Adam Machanic) Diagnostic Scripts (Glenn Berry, SQLskills. com) sp_Blitz, sp_Blitz. Index, sp_Ask. Brent (Brent. Ozar. com)

Tuning Process Temp. DB performance Indexes Unique clustered indexes on all tables Non-clustered covering indexes (avoid key lookups) Included columns (2005+) Filtered indexes (2008+) (WHERE clause) Indexed views (materialized views) Statistics “The public toilet of SQL Server” - Brent Ozar 20% plus 500 rows changed (2016+ has changed) Query Tuning (the last resort)

Query Tuning Examine the longest running queries Memory consumers Sort, hash aggregate, hash join Blocking operators Sort, scalar aggregates, hash join, eager spool, any operation that can spill to disk (e. g. worktable) Types of join nested loop, merge, hash

Query Tuning (Contd. ) Examine the longest running queries Key / Bookmark Lookups (try a covering index) Cursors vs CROSS APPLY Temp tables vs table variables Collation Implicit conversions Craig Freedman: http: //goo. gl/H 7 yw. Sq

Tips and Tricks Stored procedures beat ad-hoc queries any day Statistics are updated at 20% plus 500 rows Avoid query hints at all costs Parameterised dynamic T-SQL can be good Tipping point (scans vs seeks) Parameter sniffing can be bad OPTIMIZE FOR UNKNOWN could be worse Table Valued Function It depends …

Contact Me Blog: https: //bornsql. ca/blog Twitter: @rabryst, @bornsql