A HI (Human Intelligence) Technique to Manage Missing Index Hints

We get missing index hints from many sources: execution plan suggestions, missing index scripts, and Third Party Apps. But in all these cases, is that what you need to improve performance? In this session, we will look at an extensive AdventureWorks2022 demo query with missing index suggestions and analyze the suggested indexes to determine if they are the best solution or if there is another technique to get better performance. The attendee will leave this session with a practical approach to managing Missing Indexes.

A Self-Tuning Fill Factor Technique for SQL Server

This session explores a early AI technique for assigning fill factor values to almost all indexes in a database. It is based on a brute force technique I used back in exploring performance characteristics for a rocket motor with specified performance parametrics. Applying this technique to a fairly active OLAP database has shown about a 30% improvement in performance (and reduced some of those 2 am calls). All scripts are provided through GitHub.

Climbing The SQL Server Index B-Tree

This session dives down and looks at the important items on data pages of clustered, non-clustered, and unique non-clustered indexes using the DBCC Page command. It will also look at the effect of row data compression on indexes. In all demos, we will try to ascertain how the SQL Server optimizer might use them and also strive to gain some insight from a performance advantage. Its purpose is mainly to try and understand how SQL Server implements indexes, their underlying structure, and where performance benefits might lie from a layman’s viewpoint. The session is 80% demos. This is an intermediate-level presentation.

Really a detailed dive into how SQL Server stores and uses index data.