Blogs

Find the Partitioning Key on an Existing Table with Partition_Ordinal

@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

Continue reading

The Case of DATETIME2 and Partition Elimination

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.

Continue reading

What Resets sys.dm_db_index_usage_stats and Missing Index DMVs?

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.

Continue reading

SQL Code Basics: Reusable Event Logging Utility

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.

Continue reading

SQL Server YEAR() Function and Index Performance

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.

Continue reading

Faking Read and Writes in SQL Server Index DMVs (Trainer Resource)

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.

Continue reading

Incorrect Results: Why You Should Not Ignore Patches for SQL Server

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.

Continue reading

How to Choose Between RCSI and Snapshot Isolation Levels

SQL Server offers two flavors of optimistic locking for traditional disk-based tables: Read Committed Snapshot Isolation (RCSI), and Snapshot Isolation. They are each great tools to reduce blocking and make applications faster, particularly in databases that have lots of tiny reads and writes that need to be quick, or mixed-use patterns (lots of little reads and writes + larger reporting queries).

Both isolation levels are available in SQL Server Standard Edition and Web Edition as well as Enterprise. That’s right, they’re cheap and easy. Each of them are controlled by database level settings, and default to disabled for new user databases when you install SQL Server and leave the default settings on the model database.

When should you pick one or the other? And when might you enable both?

Continue reading

Three Inspiring Articles to Read This Week

It’s mid-February. New Year’s is gone, but it’s not springtime yet. Don’t get downtrodden – get inspired. Browse these articles and pick out one way you’re going to finish winter in rockstar style.

Continue reading

The #1 Thing to Never Do to Fix a Performance Problem

Never, ever, disable backups to fix a performance problem.

If you’re not having a performance crisis, you might look at that sentence and say, “Wow, that’s super dumb! Who would ever do that?”

Well, a lot of people, actually. It’s pretty common.

Continue reading