New live online training class in October: Transactions, Locking, Blocking, Isolation, and Versioning

Continuing our series of live, online classes, Kimberly will be delivering her new IETLB: Immersion Event on Transactions, Locking, Blocking, Isolation, and Versioning in October! The class will be delivered live via WebEx on October 9-11 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end […]

Low priority locking wait types

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations […]

When is fast recovery used?

It’s been a bit light on technical posts here over the last few months but now that summer’s over I’ll be ramping up again with lots of cool stuff planned. First up is a question that came up on the MCM distribution list this morning. There was a discussion of fast recovery (which I explained […]

Database mirroring: avoiding ‘cannot obtain a LOCK resource’ problems

This is an interesting case that came up recently, and that I’ve seen a few times before, but which I haven’t seen explained anywhere. Using database mirroring, you may see 1204 and 1454 errors on the mirror server with no other databases or activity on the mirror server apart from database mirroring: With no activity […]

SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock

Continuing my series on advanced performance troubleshooting – see these two posts for the scripts I’ll be using and an introduction to the series: Wait statistics, or please tell me where it hurts Advanced performance troubleshooting: waits, latches, spinlocks In this blog post I’d like to show you an example of SOS_SCHEDULER_YIELD waits occurring and […]

A SQL Server DBA myth a day: (23/30) lock escalation

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another really commonly-held belief… Myth #23: lock escalation goes row-to-page […]

A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another short one today as I'm still teaching a class. […]

Lock logging and fast recovery

One of the things I love teaching is how the transaction log and logging/recovery work. I presented a session on this at both PASS and SQL Connections in the last two weeks, and in both sessions I promised to write some blog posts about the deep internals of logging operations. This is the first one […]

Investigating locking and deadlocking with %%lockres%%

I’ve just read a very good, very deep, and very interesting blog post by James Rowland-Jones. In the post, James investigates some locking issues using a variety of means and explains about the undocumented %%lockres%% function with you can use to figure out what the wait resource will be for individual table rows (basically the […]

Getting historical deadlock info using extended events

In the TechNet Magazine article I wrote on Advanced Troubleshooting with Extended Events, I mentioned the always-on event session called system_health. Jonathan Kehayias, a fellow MVP and blogging mad-man, posted a great article with SQL Server Central today about how to get historical deadlock graph info from it. His article explains some of the pre-reqs […]

TechNet Magazine: February 2009 SQL Q&A column

OK – last content post today. I forgot that the February TechNet Magazine also has the latest edition of my regular SQL Q&A column. This month's column covers: Should backup compression be enabled at the instance level? Client redirection during database mirroring failovers Partition-level lock escalation in SQL Server 2008 Is it ever safe to […]

Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

Over the last few weeks I've seen (and helped correct) quite a few myths and misconceptions about index rebuild operations. There's enough now to make it worthwhile doing a blog post (and it's too hot here in Orlando for us to go sit by the pool so we're both sitting here blogging)… Myth 1:  index […]

SQL Server 2008 JumpStart

Phew – last week Kimberly and I spent 3 days teaching the ins-and-outs of SQL Server 2008 for DBAs/IT-Pros to about 130 Microsoft SQL Server experts and MVPs (like Kalen Delaney, Adam Machanic and Ron Talmage). This was the (95% complete) Beta delivery of a course we’ve been developing for the last six months for Microsoft […]

SQL Server 2008: Partition-level lock escalation details and examples

  Back in October 2007 I blogged about partition-level lock escalation in SQL Server 2008 (see here) and I promised to do a follow-up once CTP-5 came out with syntax etc. So here it is. A brief recap – lock escalation in SQL Server 2005 and before only allowed table-level lock escalation. If you have […]

Search Engine Q&A #10: When are pages from a truncated table reused?

This is a question I was sent a week or so ago – if a table is truncated inside a transaction, what protects the integrity of the table’s pages in case the transaction rolls back? Let’s find out. First off I’ll create a simple table to experiment with: We can see what pages and extents […]

SQL Server 2008: Lock escalation changes

SQL Server supports lock escalation – when the server decides to move from a large number of row or page locks on an object to a table-level lock. Sunil Agarwal posted a great description of lock escalation in SQL Server 2005 on the Storage Engine blog so I won’t repeat it all here. The problem with […]