Which Locks Count Toward Lock Escalation?

A little while back I wrote about Why Indexes Reduce Locks for Update and Delete Queries.

I got a great question on the post from Zac:

What’s not super clear is why it takes out a lock on the whole table, is this because it does a lock escalation as a result of the Full Scan? Will this always happen, or is there a threshold of record update counts where this will occur?

This was tough to answer in just a comment, so I promised a full post on the topic.

Continue reading

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