Links and Notes from “SQL Server Index Formulas”, Huntington Beach

I gave a day long session, “SQL Server Index Formulas, Problems and Solutions” in Huntington Beach, CA on April 1. The class was a great group of students, and we had a lively discussion and lots of questions. Here’s a topic we diagrammed in class, as well as links to extra resources What are the costs/overhead of each nonclustered B tree index? (List made by the class)Inserts, updates, deletes –… (Read Me)

Can I Force A Hinted Plan with Query Store in SQL Server 2016?

SQL Server 2016’s Query Store feature promises to be better than Plan Guides ever were. The Query Store lets  you track query performance, collect execution plans, and force a specific plan if you notice that a query is sometimes fast, and sometimes slow. But what if we want to hint a query? Can Query Store force a hinted plan? We’ve got a slow query that runs for 35 seconds. We can’t change the code… (Read Me)

Training Plan for Junior DBAs Learning SQL Server

You’re just getting started as a SQL Server Database Administrator — or you’re trying to get there. Here’s a learning plan and links to free articles and scripts that will equip you to tackle the three most critical skills to for DBAs. Skill 1: Design Backup Strategies that Meet RPO and RTO Secret: the DBA is always responsible for preventing data loss, even if it’s not part of their job description.… (Read Me)

NO_PLAN and NO_INDEX: Breaking a Forced Query Store Plan

Whenever you’ve got a new feature, one of the first things to ask is, “What happens when I break it?” Because we’re going to break stuff. With 2016 Query Store, a natural question is, “What happens if I force an execution plan, and that plan is no longer valid?” We’ll take a tour in some detail, and then I’ll sum up all the takeaways in a nice little list at the bottom of… (Read Me)
When STATISTICS IO gets opinionated


When  you need to measure how long a query takes and how many resources it uses, STATISTICS TIME and STATISTICS IO are great tools for interactive testing in SQL Server. I use these settings constantly when tuning indexes and query. Here’s three tricks that come in really handy to up your STATISTICS game. 1. You can turn both STATISTICS IO and STATISTICS TIME on and off with a single line of code I learned this trick… (Read Me)

Truncate Table with Partitions Fails if You Have Non-Aligned Indexes

SQL Server 2016 brought in a cool new little feature for table partitioning: you can now truncate individual partitions. There’s one little gotcha, though: you can only do this if all the indexes on the tables are “aligned”. Here’s what the syntax looks like: TRUNCATE TABLE dbo.FirstNameByBirthDate_pt WITH (PARTITIONS (4)); go Meet Error Msg 3765 Blast from the past – teaching table… (Read Me)

Learn Indexing from Kendra in Huntington Beach on April 1 for $99!

It’s just two weeks until I’ll be teaching index tuning in Huntington Beach, California. This day long session is $99 — and it’s a great time of year to plan a quick visit to California, no? In this day we’ll work entirely on problems and solutions. You’ll get to think through different problems and step through the answers to see why some index designs are much better than others.… (Read Me)

Live Query Statistics Don’t Replace Actual Execution Plans

I like SQL Server’s new Live Query Statistics feature a lot for testing and tuning large queries. One of my first questions was whether this could replace using actual execution plans, or if it’s useful to use both during testing. Spoiler: Both are useful. And both can impact query performance. Live Query Statistics Gives Insight into Plan Processing I’m loading a bunch of rows into a partitioned… (Read Me)