Geek City: What gets logged for index rebuild operations?

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class asked for some information on a current problem he was having with transaction log writes causing excessive wait times during index rebuild operations when run in ONLINE mode. He wanted to know if switching to BULK_LOGGED recovery could help.

Read More

Geek City: What do you intend with that lock?

Way back in the olden days, prior to SQL Server 7, I already knew that the lock manager was one of the most complex and resource intensive parts of SQL Server. Keeping track of every lock held, who was waiting for that lock, who could be granted the lock, and who was next in line for the data if the lock was released was a lot for SQL Server to keep track of. I admit, I was worried about the promised feature of row-level locking, thinking that it was going to be orders of magnitude more expensive. Without going into all the details of the complete rewrite of the locking implementation for SQL 7, let’s just say that my fears were quite exaggerated.

Read More

Geek City: sp_cacheobjects for SQL Server 2017

Several times over the last decade, I've posted a version of a view that lists all the compiled plans in SQL Server's plan cache. It's based on the old pseudo-table syscacheobjects, returning basically the same columns with extra filters added. Over the versions, Microsoft keeps showing me more and more internal operations in the plan cache, that usually just clutter up the output, so I keep having to add filters to get rid of the unwanted rows.

Read More

Geek City: Changing How To Change Your Database Properties — ALTER DATABASE

Long ago (in SQL Server years) many metadata changes were implemented with special one-off stored procedures. For example, we had sp_addindex and sp_dropindex, as well as sp_addtypesp_droptypesp_addlogin and sp_droplogin. For changing certain database properties, we had sp_changedbowner and sp_dbcmptlevel, to name a few.

Gradually, Microsoft has started replacing these procedures with the more generic DDL commands ADD, DROP and ALTER. This is both good news and bad news.

Read More