Indexes

Performance Tuning Like a Pro – with Hypothetical Indexes

2021-12-27
SQL Server Performance, T-SQL, Tools and Scripts

Every once in a while there comes an opportunity to “upgrade” your abilities with newly acquired knowledge that lets you “step up your game” and possibly add some “wow factor” to your work, leaving your peers awe-struck by your amazing new “magic trick”.

As a SQL Server consultant, one such opportunity that I had in my line of work, is when I learned about “Hypothetical Indexes” and how to use them.

How to Un-Heap Your Heaps?

Today’s script has something very interesting to do with Heap tables. Specifically, how to turn them into something that’s not a heap. Continue reading for more info…

The LOOP, HASH and MERGE Join Types

2018-10-28
SQL Server Database Engine, SQL Server Performance

Today I’ll talk about the main physical JOIN operator types in SQL Server (Nested Loops, Hash and Merge Joins), their differences, best practices, and complexity (which determines when the SQL Server optimizer would use them in execution plans).

These are not the duplicates you're looking for

The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes

Unlike foreign keys, primary keys and unique indexes cannot be “untrusted”. There’s no “NOCHECK” option for unique constraints. The closest… Read More »The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes