Thanks to everyone who attended my 24HOP session on Wednesday, September 7. My favorite part is always the Q&A. I wish there was more time to do live Q&A, but because there’s not, I get to turn it into a blog post!
My session was a snippet of what I will be talking about in my pre-con for the big PASS Summit at the end of next month.
My full day session is called Locking, Blocking, Versions: Concurrency for Maximum Performance and you can read about it here.
In my one-hour 24HOP session, I described the basic aspects of locking and then took at look at the main metadata view: sys.dm_tran_locks. This 45 minute session was just the tip of the iceberg on what I could tell you about locking and blocking and row versions. I could actually talk for a whole day on this topic! Which I am going to get to do, at the Summit, on October 25th.
Here are the questions that came up during the session, and my answers to them. (I have made a couple of grammar edits to some of the questions.)
1. What does the M stand for in LCK_M_IS lock?
When you look in the sys.dm_os_wait_stats view, to see see what processes are waiting for something, the column wait_type will show a string starting with LCK_M if the wait type is a lock of some sort. (There are LOTS of other reasons for waiting that are not because of locks.) We saw LCK_M_SCH_M, LCK_M_X, LCK_M_IS during my demos. All locks start with these characters, and I was told that the M just means ‘mode’. So the three I listed are : lock mode schema modification, lock mode exclusive and lock mode intent shared.
2. Are there any downsides to using RCSI when it comes to locking/blocking/concurrency?
I actually didn’t really talk about RCSI in this short session, but I will discuss it a LOT in the full day presentation. But since you asked, I mention two downsides. First, any use of snapshot isolation (without the new memory-optimized tables) is going to demand a lot more from your tempdb database, so you need to make sure that tempdb can handle the load. Your tempdb needs to be big enough to handle all the versions that it will need to hold, and it should be on a nice, fast, smart drive system. Second, if you are using RCSI to avoid using the NOLOCK hint (a very noble goal, btw) you need to be aware that if you already are using NOLOCK in your application code, the NOLOCK will take priority over RCSI. NOLOCK allows SQL Server to read dirty (uncommitted) data. RSCI allows SQL Server to read the older committed data. With NOLOCK and RSCI, SQL Server will continue to read the dirty data instead of reading the older committed data. SQL Server will use resources to store and manage the old committed data in the version store, but it won’t ever be used.
3. What was the hint to allow parallelism with bcp?
The lock hint that allows parallel BCP operations is the –h “TABLOCK” hint. You can get more information in this article: Controlling Locking Behavior for Bulk Import
4. Will you be writing a follow-up book "Microsoft SQL Server 2016 Internals" with a Deep-Dive into Blocking, Locking, and Deadlocking?
I will not be writing more of the big Internals books anytime soon. Microsoft Press is no longer publishing, and my experience with other publishers for big books like this has been less than optimal, to say the least. It takes almost 2 years to get such a book written, edited and published, and with a new version coming out every 2 years now, it just is not possible. I will be writing more of the shorter books, like the ones Red Gate publishes. I already have one on Locking and Blocking, which was written for SQL Server 2008. I also wrote one on In-memory OLTP which also looks at concurrency issues a lot, and how they are addressed with the new memory-optimized tables.
5. Where can we get this presentation? Where are the scripts?
I am attaching them to this blog post; the presentation PDF and the scripts are in a single ZIP file.