My first post on this blog detailed a scenario where a read uncommitted select statement could ultimately block an insert/update/delete. In this scenario, a long running read uncommitted select is executed requiring a schema stability lock. That lock prevented the online rebuild from grabbing the schema modification lock necessary and caused the update statement to get queued up behind it.
SQL Server 2014 introduced an option that will allow more control over how online rebuilds behave in a scenario such as the one I described. The WAIT_AT_LOW_PRIORITY option gives you a few different choices in dealing with blocking scenarios. The syntax, from Books Online, is below.
Well it has been a few months since I have posted here but it’s time for me to share an update. I recently accepted a new position as a SQL Server DBA and I am excited to continue moving forward in my career. My previous role doing database support for a software vendor provided me with a great deal of experience working in both SQL Server and Oracle environments across many industries. In my new position I will get to experience taking ownership of an environment of my own and facing all the challenges that come with that responsibility. I believe this move will allow me to become a more well rounded SQL Server professional and I am incredibly excited for the opportunity.
Between buying a house, planning a wedding, and accepting my new position I have been quite busy lately and that has resulted in me being pretty quiet on here. I aim to be more active on this blog and more active in the community once the chaos settles down.