NO_PLAN and NO_INDEX: Breaking a Forced Query Store Plan
Category:
query-performance
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.
3 Tricks with STATISTICS IO and STATISTICS TIME in SQL Server
Category:
query-performance
, sql-server
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.
Truncate Table with Partitions Fails if You Have Non-Aligned Indexes
Category:
indexing
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”.
Learn Indexing from Kendra in Huntington Beach on April 1 for $99!
Category:
query-performance
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?
Live Query Statistics Do Not Replace Actual Execution Plans
Category:
query-performance
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.
Finding: Both are useful. And both can impact query performance.
Find the Partitioning Key on an Existing Table with Partition_Ordinal
Category:
indexing
@SQLSista asked a fun question on Twitter recently about how to figure out the partitioning key on an existing table:
Bad Partitioned HEAP predates me. No clustered index, Primary Key on ID column and multiple datetime columns. How to find the partitioning key? #sqlhelp
The Case of DATETIME2 and Partition Elimination
Category:
query-performance
Data types are hard.
I’ve been working on some demo code for table partitioning. I have a table partitioned by a column named FakeBirthDateStamp, which is a DATETIME2(0) column. The table is a partitioned heap. At this point in the demo, I hadn’t built any indexes. I wanted to show that partition elimination could occur on a partitioned table, even without a clustered index.