The Case of the Blocking Online Index Create- the Shared Lock that Would Not Quit

I recently got an interesting question from a reader about running a CREATE INDEX statement with DROP_EXISTING

Continue reading

Columnstore Index Returns Zero Rows... Which is One Row

I’ve never claimed to be great at math, but until recently I thought I knew how to count to one. Zero… one. That’s what we learned in kindergarten.

Apparently SQL Server didn’t go to kindergarten.

Continue reading

Why You Should Switch in Staging Tables Instead of Renaming Them (Dear SQL DBA Episode 26)

Over the years, I’ve come across a pattern fairly frequently: an application in an OLTP database periodically creates new tables, loads some data into them and fixes it up, then does a switcheroo and replaces old tables with the new tables.

This can cause major problems with blocking if anyone else is querying the table.

Continue reading

Administering COTS databases (ISVs / Third Party Vendors)

I recently received a question from a vendor about databases created by software vendors.

Continue reading

Does Truncate Table Reset Statistics?

Short answer: the SQL Server optimizer will know that the table was truncated, but statistics might not update when you expect.

For the long answer, let’s walk through an example using the WideWorldImporters sample database.

Continue reading

When Did SQL Server Last Update That Statistic? How Much Has Been Modified Since? And What Columns are in the Stat?

Whether I’m working as a DBA, a consultant, a teacher, or just answering questions in my inbox, I always end up needing a script to inspect statistics one way or another.

Here are some freshly written scripts for a classic DBA question: what’s going on in my stats?

Continue reading

Should You Rebuild or Reorganize Indexes on Large Tables? (Dear SQL DBA Episode 19)

The bigger your indexes are, the harder your index maintenance falls. Is it better to rebuild big indexes? Or should you reorganize?

Continue reading