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”.
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?
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.
@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
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.
Managing indexes got trickier with SQL Server 2012. SQL Server has tracked and reported statistics on how often indexes are used and requested since SQL Server 2005. As of SQL Server 2012, suddenly all that information started getting reset whenever anyone ran ALTER INDEX REBUILD.
When I created the SQLIndexWorkbook database [which was later renamed to BabbyNames], I purposefully shipped minimal code. I plan to add more as I build it, but I wanted to keep the first version dead simple.
SQL Server’s really clever about a lot of things. It’s not super clever about YEAR() when it comes to indexes, even using SQL Server 2016 – but you can either make your TSQL more clever, or work around it with computed columns.
Trainers and speakers need the code they write to be predictable, re-runnable, and as fast as possible. Faking writes can be useful for speakers and teachers who want to be able to generate some statistics in SQL Server’s index dynamic management views or get some query execution plans into cache. The “faking” bit makes the code re-runnable, and usually a bit faster. For writes, it also reduces the risk of filling up your transaction log.
Patching isn’t fun. It’s time consuming, thankless, and easy to break stuff.
But you can’t skip it. For critical systems, you need to subscribe to patch lists for SQL Server and review issues that are fixed regularly.
Copyright (c) 2024, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.
