Killing me softly with Service Broker…

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

A few days ago, I found a very interesting behavior regarding Service Broker on a customer system. The main thing is that your activation stored procedure is not allowed to block for a very long time, otherwise you will impact the health of your whole SQL Server instance. In this blog posting I want to demonstrate this behavior to you. In the first step I have to create the whole necessary Service Broker infrastructure objects, nothing special with this – I’ve just stolen that code from Chapter 4 of my Service Broker book:

As you can see from this listing, the target queue is enabled for activation, means that the ProcessRequestMessages stored procedure automatically started by a Queue Monitor as soon as a new message arrives on that queue. The stored procedure itself just inserts the received message in the table ProcessesMessages. Now imagine that this table has a lock from some other query that is executing inside your database:

I know that TABLOCKX is a little bit too much, but I just want to demonstrate the problem to you. When you afterwards send a new message, the activation stored procedure gets started, and finally blocks because the necessary IX lock for the INSERT statement can’t be acquired:

You can now check very easily that your activated stored procedure just blocks and is doing nothing inside SQL Server:

The crazy thing about that behavior is, that you can’t just stop SQL Server, you have to do a SHUTDOWN WITH NOWAIT, otherwise SQL Server will not stop anymore…:

Moral of the story: make sure that you release your locks as fast as possible. The other problem with locking in Service Broker is that it yields to high LOCK_HASH spinlocks, which will burn down a lot of CPU cycles without doing anything in SQL Server – it’s a very crazy scenario and in the first step very hard to explain. You can also crosscheck this with sys.dm_os_spinlock_stats. In that scenario the CPU is up to 100% but doesn’t do any work regarding Service Broker – very contraprodutive! Thanks to Thomas Kejser for that tip regarding the LOCK_HASH spinlock.

Thanks for reading

-Klaus

Do you like this post? Why not share it with the SQL Server community?

My journey finally ends at the SQLPASS 2012 Summit

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Yesterday SQLPASS revealed the sessions that were selected for the upcoming SQLPASS summit in Seattle from November 6 – 9. I’m very happy to announce that I’m presenting 2 regular sessions and a precon about SQL Server Performance Troubleshooting! I’m really happy to have a chance to present a precon, because this was one of my biggest dreams when I first presented about SQL Service Broker back in 2006 in Seattle. I have presented at every summit since then (expect 2008 for some personal reasons), and I have taken one step after one step to finally reach that ultimate goal.

I have already presented last year a session about Advanced SQL Server Troubleshooting (which I’m doing this year again!), and it was a real blast: it was the largest session with more than 900 attendees. I never thought about that back in 2006, when I presented about a very niche technology like Service Broker in front of a few people. So I have really enjoyed my SQLPASS journey through the last years, and I’m really happy to finally ending up with my own precon about one of my favourite topics – SQL Server Performance Troubleshooting:

Practical SQL Server Performance Troubleshooting

Learn how to configure the Windows OS, SQL Server, and your database for optimal performance. In this pre-conference workshop, we’ll start by taking a default installation of SQL Server and running an OLTP workload with several hundred users to generate our initial baseline for performance tuning and troubleshooting. Throughout the day, we’ll work with various areas of SQL Server to implement different performance optimizations and then see how those changes impact the throughput of our initial test workload. At the end of the day, we’ll have a well-performing SQL Server that can handle a much larger workload than the initial (default) installation.

Topics for the day include: 
• Windows OS settings 
• Storage configuration 
• SQL Server instance settings 
• Database settings 
• Index and statistics maintenance 
• Locking, blocking, and deadlocking 
• Memory management

Besides the precon I’m also presenting two regular conference sessions:

ColumnStore Indexes: The Turbobooster inside SQL Server 2012

Can you improve your data warehouse query performance by 100 times just by adding one additional index? With SQL Server 2012′s new columnstore index, the paradigm for DW queries has changed completely. In this session, we will look at the details of the columnstore index, when it does and doesn’t make sense, and current restrictions.

We will explore row store vs. column store and how to change data in tables that have a columnstore index defined. We will also discuss segment elimination, batch mode execution, query memory grants, and how you can ensure that SQL Server will use your columnstore index as effectively as possible. Then, you will see a real-world example of how fast the columnstore index works on a table with more than 2 billion records – on commodity hardware.

Advanced SQL Server Troubleshooting

It’s Friday, 5:00pm, and you just received an email alert that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools does SQL Server give you for this task? 

In this session, we will look at the key resources that SQL Server uses – storage, CPU, memory – and how you can effectively troubleshoot them. You will learn how to identify performance bottlenecks and how to resolve them.

I’m looking forward to see you all very soon in Seattle, and thanks for attending all of my sessions in the past 6 years!

Thanks

-Klaus

Do you like this post? Why not share it with the SQL Server community?