My advanced SQL Server Performance Troubleshooting workshop goes to the international level!

(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.)

I’m working my whole professional work life (around 11 years) with SQL Server, and have see so many pitfalls in configuration options, deployments, and database designs. From all this know-how I’ve done in the past a lot of consulting engagements and have spoken at the various SQL Server conferences across the world about performance tuning and optimization. In sum there are so many things that you just HAVE to know about SQL Server, so that you can use SQL Server with the best possible performance. Today I’m very proud to announce that I’m now offering an advanced SQL Server Performance Optimization and Troubleshooting workshop at the international level!

We will be starting with our first public course in Central London/UK from September 12 – 14, and offering a second public course in Vienna/Austria from September 26 – 28. Further information about the detailed agenda and the registration details can be found at We have also several other locations across Europe in our pipeline, like:

  • Zurich/Switzerland
  • Oslo/Norway
  • Germany

If you want to see some feedback about what you can expect in this workshop, please refer to the following weblog post from me, where I have given a deep technical session about SQL Server Internals at the SQLbits conference this year in Brighton/UK:,guid,f1d784c8-ea53-4bc9-99df-6dfaab6f6f8c.aspx

I’ll be looking forward to see some of you at one of these advanced SQL Server workshops!


SQLbits recap

(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.)

It’s already a long time ago, since I spoke at SQLbits (first week of April), but yesterday the speakers got their session evaluation, and I’m very proud about the results of the 2 sessions that I have done at SQLbits:

  • Service Broker: Message in a Bottle
  • SQL Server 2008 Database Internals

The following 2 figures shows you the overall session metrics for both of my sessions:



As you can see my “SQL Server 2008 Database Internals” was very high rated and nearly a top rated session Smile. I want to share you the feedback that I got for this session:

  • Best talk of the day, absolutely fantastic knowledge of the internals of SQL Server.
  • A very good session the presenter was also good, I learn’t a lot and the speaker used a methodical and logical approach to explaining pages/extents and I/O.
  • Although I scored this higher than the previous session,this is on reflection after the event – I’ve had a few ‘A-ha’ moments since attending, and the content itself was brilliant – Klaus really knows his stuff in this area. It was a VERY large room to present in, the audio wasn’t quite as good as some others, and the display screen was quite low so it couldn’t be seen by people more than 5 rows back.
  • Amazing depth – thanks, Klaus! Presentation skills generally good – real passion for the subject.
  • Klaus is obviously very knowledgeable and a very good presenter. I think because of the deep technicality of the subject, it’s easy to lose focus, especially as it was just before lunch. Although I got lots out of the session there were a couple of occasions where I drifted off a bit. This isn’t a critism of Klaus – it was just a very deep session that he was cramming into one hour.
  • Hugely enjoyable and very professional presenter. Conveyed enthusiasm and knowledge in equal measure. Excellent!
  • Really great in depth knowledge.
  • Brilliant speaker. Speaker has a great deal of knowledge in the subject and was able to convey it effortlessly, with a little humor :-) Fantastic session that made the 8 hour journey well worth it.
  • Very entertaining presentation on a somewhat dry subject.
  • Very knowledgeable and engaging.
  • Very good session.
  • Great enthusiasm and knowledge.
  • I was working on FoxPro since last 18 years and I am in view that few features in SQL server 2005 onwards had taken from FoxPro. I am quite aware of FoxPro database/table structure and exactly what looking for SQL in last few years. It does make impact on how you design you database, when you know how it is stored.
  • I also attended Klaus’ sessions on the Thursday; I was very very impressed with his abilities as a speaker and the way in which he went through each issue. Everything was exceptionally clear, well prepared in terms of everything being prescripted in the demos and very clear on the results and advantages to understand each aspect. I would say he was the best speaker of any session I attended during the three days.
  • That was very cool, Klaus. Fascinating. Perhaps not something I *need to know, but I certainly came away with a much deeper and better understanding, feeling a lot more on top of the beast that is SQL server!

Here’s the feedback for my Service Broker session that I also want to share with you:

  • Really useful presentation especially as we are attempting to use service broker. He solved several problems I was experiencing with Service broker, including the alternative windows authentication over certification method. I would have liked to know more about the transport level issues but it was a 1 hour session and any more detail would cause the my brain to fry. it has left me wanting to go on the full day session if it is done again at SQL BITs.
  • Excellent presentation, as a newbie i’d not used Service Broker yet but Klaus made this an enjoyable and easy to learn experience.
  • Session was very good but I was expecting about how service broker can be used in HA/DR scenarios.
  • I attended this session to see Service Broker working as an SOA platform. Klaus demonstrated this very well.
  • I would have liked the session framed better in relation to other technologies and decisions to make.
  • The one suggestion I would make is to include a few situations where service broker would be useful vs where it might not be useful and also to compare more with other architectures that are similar (e.g. MSMQ).
  • Content aside (which in itself was still outstanding), this is the best delivery of a presentation I’ve seen in a long time.
  • you could see Klaus has a passion for this :)
  • Brilliant session
  • Klaus’s enthusiasm for his subject is obvious. A really good, demo-heavy session on a subject I am not very familiar with which was engaging and fun. Very enjoyable!

The main problem that I had with my Service Broker session is TIME Winking smile. One hour is just too less to explain a fully distributed, and secure Service Broker application. Presenting a 75min or even 90min session on that topic would make it really awesome Smile. My (positive) learning from SQLbits is that the UK boys and girls like my presentation style and my topics, so I’m planning to do much more within the next months in the UK. Stay tuned for further information on that! Smile


Please vote for my SQLPASS precon and sessions

(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, SQLPASS opened the voting for the precon’s and sessions for the annual SQLPASS community summit. I’ve also submitted 4 sessions and one precon, and I would be very happy if you can vote for them. Here are the 4 session abstracts:

Advanced SQL Server 2008 Troubleshooting
It’s Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this session you will see a lot of different tools with which you can do effective SQL Server troubleshooting. You will learn how to identify performance bottlenecks with those tools, and how to resolve the identified bottlenecks.

Service Broker: Message in a bottle
Service Broker provides several new key features for building SOA applications directly with SQL Server 2008. This “No slides – just code” session shows you, in just 75 minutes, how to build an SOA-based, secure, reliable, and distributed messaging application with SQL Server 2008 Service Broker from scratch. In this session you will see:
*) The core architectural concepts behind Service Broker
*) How you can do SOA with Service Broker – a practical approach
*) Routing between several SQL Server instances
*) Implementing reliable messaging
*) Securing a Service Broker conversation between you and your trading partners
*) Using managed code in Service Broker applications

SQL Server Database Internals
Do you already wanted to know how SQL Server 2008 stores a database file physically on the hard drive? In this session you will learn the internal structure of a SQL Server 2008 database file, and how SQL Server stores tables and indices internally in the file system. Furthermore you will see the DBCC T-SQL command in action, with which you can analyze the internal storage format of your database files. All these information from this session helps you in your next database design to get out the last percent of performance from your new database solution.

Troubleshooting SQL Server TempDb
For the most DBAs and DEVs the TempDb is a crystal ball. But the TempDb is the most critical component in a SQL Server installation and is used by your applications and also internally by SQL Server. TempDb is also one of the performance bottlenecks by design, because it is shared across the whole SQL Server instance. In this session we will take a closer look into the TempDb, how it is used by SQL Server, and how you can troubleshoot performance problems inside TempDb and how you can resolve them.

I’ve also submitted my workshop titled “Advanced SQL Server 2008 Performance Monitoring and Troubleshooting” as a precon for SQLPASS:
It’s Monday, 10:30am. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this workshop you will see you can do effective performance monitoring and troubleshooting with SQL Server. You will learn how to identify performance bottlenecks with tools and information provided by SQL Server and how to resolve the identified performance bottlenecks.
We will start by giving a general overview about SQL Server and the life-time of a query inside SQL Server. You will learn about Wait Statistics and how you can use them to identify your performance bottlenecks. After laying out the foundation we will move on with SQL Server Profiler and the Windows Performance Monitor. We will also have a look into the TempDb, because TempDb is a performance bottleneck by design, because it is shared across a whole SQL Server instance. We will also have a deep look into Memory Management and how SQL Server interacts with the Windows OS to acquire and release memory. Finally you will learn about SQLDiag and SQL Nexus which are 2 tools for advanced performance monitoring and troubleshooting.

I’ve already done this precon at SQLbits a few weeks ago in Brighton/UK with about 40 people in it, and got amazing feedback on it. Here are some feedbacks, so that you can get a picture what you can expect from this precon:

  • “I attended the one-day workshop that Klaus ran at the recent SQLBits conference in Brighton, and it has been the most useful training I have attended in years. It was not just the level of knowledge presented, but the way that Klaus explains concepts in ways that really make them stick, which makes it so valuable. I came away from the course as a better DBA, armed with a better understanding and new practical tools which I can immediately use, plus he has opened my eyes to different ways to approach the whole area of performance tuning and troubleshooting. I can recommend Klaus very highly as a trainer, and I have no doubts about his ability to deliver as a consultant.”
  • “I just want to say that I think it was an amazing workshop and gave me  a lot of tips and a better understanding on SQL Server. I work as an administrator and only have 20 % of my time to SQL Server so the workshop helped a lot.”
  • “Thank you very much. I really enjoyed this training. I learned a lot of good things that I can apply at work now.”

Here’s the link for the session/precon voting:


RCSI/SI doesn’t work with rows larger than 8046 bytes

(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.)

During this week I have prepared a workshop about Locking & Blocking for one of my customers. One big part of this workshop was also the new Optimistic Concurrency model that SQL Server offers us since the inception of SQL Server 2005. Since SQL Server 2005 we have the READ COMMITTED SNAPSHOT ISOLATION level (RCSI) and the SNAPSHOT ISOLATION level (SI). When you are using these new isolation levels readers (SELECT statements) doesn’t acquire (S)hared Locks during their reading. Writers (UPDATE, DELETE statements) are versioning the old image of the records they are changing into the TempDb. They are creating a version chain where the actual version of the record (which is stored on a data page inside the database) points to older versions that are stored on pages in the TempDb. The following picture illustrates this concept.


To make this work, SQL Server has to add a 14 bytes long pointer to each record on the data page inside the database. This means that each record gets 14 bytes longer. As you might know, a record inside SQL Server can’t be longer than 8060 bytes when you are using fixed length data types. This means that enabling RCSI/SI could lead to records that are larger than these 8060 bytes. Let’s have a look on a very simple example.

USE master

-- Create a new database
CREATE DATABASE VersionStoreRestrictions

-- Enable RCSI

-- Use it
USE VersionStoreRestrictions

-- Create a table where each record is 8047 bytes large
   Column1 CHAR(40),
   Column2 CHAR(8000)

As you can see from the previous code I’m creating a table with two CHAR columns of a length of 8040 bytes. SQL Server also needs internally at least 7 bytes overhead for each record. In this case one record needs 8047 bytes on a data page. Because we have enabled RCSI on the database level, SQL Server has to add the additional 14 bytes for the Row Version Pointer, which expands each record in the table to 8061 bytes. This means that each record is 1 byte too long for SQL Server. Let’s now insert one record into the table:

-- Insert a initial row

When you now try to update the row (SQL Server now tries to version the old record into TempDb), the connection to the database is broken by a 208 error:

SET Column1 = REPLICATE(‘B’, 40)
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘TableB

The error message is not very meaningful, because the database context is wrong (SSMS shows that you are now in the master database). But when you fully qualify the table during the UPDATE statement, you are able to get back the actual error message:

UPDATE VersionStoreRestrictions.dbo.TableB
SET Column1 = REPLICATE(‘B’, 40)
Msg 682, Level 22, State 214, Line 2
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

Wow, that’s an internal error, because the buffer that is used by SQL Server is only 8060 bytes large, and now we try to store in that buffer 8061 byte – Voila that’s a bug inside SQL Server! You can also reproduce this behavior on each version of SQL Server starting with SQL Server 2005 which means that this bug is almost 6 years old! Interesting enough the bug is already fixed in SQL Server Denali CTP1, where a page dump shows that SQL Server stores the expected 8061 bytes. I’ve also already filed a bug on Connect – so please feel free to vote for it!

When you are enabling RCSI/SI for existing databases, please keep this bug in mind, because it means that RCSI/SI doesn’t work in any possible scenario. When you have one table in your database that exceeds the 8046 bytes limit, then you are in real troubles! With this nasty bug you can also see how important it is to know the internals of SQL Server, and how SQL Server internally stores its data.