IDC DevOps Conference – Virtual Event September 24, 2020 – info

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 the post. First, a query with two plans I’ve got a stored procedure, dbo.NameCountByGender. You give it a first name, and it returns a count of people with that name grouped by Gender. I turned on Query Store, and it observed that I’ve had two different execution plans for the NameCountByGender procedure. Plan 2 has a higher average duration than plan 1. I use comic sans for Query Store because it’s a birthday party for execution plans.…
Read More


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 from Michael J. Swart a while back. Most people do this, because it’s what the documentation shows: SET STATISTICS IO ON; GO SET STATISTICS TIME ON; GO But you can just do this, and it works perfectly: SET STATISTICS IO, TIME ON; GO The same trick works for turning the settings off. This shortcut has probably saved me an hour of typing in the last year alone. (I totally made that metric up, but hooray…
Read More

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

Blast from the past – teaching table partitioning in Virginia back in 2010. 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 If you have a non-aligned index on the table, you’ll see an error like this: Msg 3756, Level 16, State 1, Line 1 TRUNCATE TABLE statement failed. Index 'ix_FirstNameByBirthDate_pt_BirthYear_FirstNameId_nonaligned' is not partitioned, but table 'FirstNameByBirthDate_pt' uses partition function 'pf_fnbd'. Index and table must use an equivalent partition function. This isn’t a bug, and it makes total sense from a logical point of view. “Non-aligned” indexes are not partitioned like the base table is– by definition they are either partitioned differently, or not…
Read More

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. You will learn: Why choosing the right key column order can make a huge difference in query performance How included columns can be used in more ways than you might expect Good and bad formulas for clustered indexes and guidelines to keep your table schema efficient When indexed views will make your queries faster and how to avoid pitfalls with them in both Standard and Enterprise Edition How to find and interpret index requests from SQL Server’s “missing index” feature How to…
Read More

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 heap. Here’s what the insert looks like in Live Query Statistics when run with parallelism. Note that the progress on the Sort operator remains at 0% done until the Clustered Index Scan and Compute Scalar operators feeding it are 100% complete: Here’s what that same insert looks like when run at maxdop 1. In this case the operators are ordered differently, and the Sort Operator is able to make progress while the Clustered Index Scan is still running. This insert runs significantly faster at MAXDOP 1,…
Read More