In the last months I have done a lot of SQL Server consulting engagements where I have seen the craziest performance problems and learned how to solve them effectively. Sometimes these problems occurred because of misconfigured hardware (mostly because of I/O subsystems), but almost every problem also had some source in the development, maybe a developer hasn’t tested his/her code against a production workload, maybe the developers haven’t thought carefully about indexing strategies or even locking and blocking scenarios with concurrent users.

Because I have a strong background in development in combination with SQL Server I know exactly WHERE and WHY those problems are introduced. The short answer is very simple: for developers SQL Server is just a simple black box: you feed SQL Server with a query, and sometime later SQL Server returns you the result of the query. The key word here is sometime: sometimes it takes a few milliseconds to execute a query, sometimes it takes a few seconds or even minutes to execute the same query! For me, SQL Server was also a big black box for several years. I didn’t have understood the inner workings of SQL Server, but I was able to write SQL Server based applications, because the tool support that Microsoft provides for developers is just amazing. But that’s only the half of the truth. There is more that you HAVE to know about SQL Server, so that you are able to write better, scalable, and great performing SQL Server based applications.

For that reason I’m also offering custom SQL Server trainings, where I’m showing developers (and also DBAs) how SQL Server really works, and what are the pitfalls when you are working and developing with SQL Server. I have done such a training for HiCo (see http://www.hico.com) in Austria. They are developing and selling a large .NET application that is based on SQL Server, and in the last months they wanted to improve the performance and scalability of their application, so we have organized a 5-days long training where we went through all the most important core concepts of the relational engine of SQL Server, and how you can troubleshoot them, when you experience serious performance problems.

But by now I want to stop writing, because I few days ago I had the chance to meet again with Hannes – one of the Senior Software Developers of HiCo – who attended the training. We had a little chat about the training, and how it helped him and his company to improve their SQL Server know how, and how they can now relate this know how when they develop new features for their software. Here are some of the questions that I have asked Hannes, along with his answers.

In which area your company is using SQL Server, and what was your background on it, before you attended the training?

Our usage of SQL Server is primary as a repository for storing structured documents (XML, SGML), in our application. Our application uses current technology like WCF (Windows Communication Foundation) and WPF (Windows Presentation Foundation) and supports a large number of users and workflows to create technical publications, according to standards like S1000D (see http://en.wikipedia.org/wiki/S1000D). It’s a requirement of our customers to support the Oracle database engine too, so we use nHibernate as an O/R-Mapper. Our initial background on SQL Server was “install it and use it to store our data”. We did not care about internal mechanism of SQL Server – in fact we used it as a “black box to store data”.

How would you rate your knowledge about SQL Server after attending the training?

The training gave us a “different look” at SQL Server. After the training it is no longer a “black box” for us.

Why your company has chosen to take an advanced SQL Server training?

We wanted to gain knowledge about how to make performance optimizations and how to solve “database problems” – to be more specific: we had some serious locking problems in our application.

Was it worth enough for you and your company to attend the SQL Server training?

For me, as a developer, it was more than worth enough, because I gained a deeper understanding and know “where to start searching”, if I encounter problems according to the database. Four our company – as far as I can tell – it was worth too. Our mission is to provide high quality software for our customers and this naturally includes great performance and responsiveness of our applications. Since the database has a great impact on performance, it is important for us to be able to solve performance issues and locking problems.

What was the biggest “wow” effect you had during the training?

This in fact – for me – was something, not related to “performance or locking problems”. It was, how important it is to set up a stable backup strategy and what happens, if you set up no backup strategy at all, because that IS, what you do when you use SQL Server as a “repository to store my data” and that’s all you care about. Another very interesting point was the internal handling of indexes and what you should not do, when you want to “optimize” database indexes.

How often you had in your day-to-day work referred back to the provided training material and the notes you have taken during the course?

Because Klaus gave us his training materials, we are able to review them whenever required. Since the training, I did not required to review the training materials, but I know where to look, when I have to. The training definitely had influence on how I design database tables now. In contrast to designing tables before the training, I no longer use VARCHAR(MAX) columns for fields, that store a maximum of 500 bytes J

Which module was the one, that helped you the most in your day-2-day work?

The reason why we took the SQL server training, as already mentioned, was because we encountered locking and performance problems. The recipes how to solve these problems helped us most in our day to day work.

Would you recommend the training to other .NET/SQL Server professionals, and if yes, why?

I would definitely recommend the training to all developers that use SQL Server in their applications. It changes your point of view from “something to store my data in” to “I know how my table design affects performance” and now I can read and understand execution plans, and know how to do troubleshooting with them.

What do you want to tell other blog readers about the training?

If you use SQL Server in your applications – this is the course you have to take J

Wow, that sounds pretty good! Hannes and his co-workers really have enjoyed my SQL Server training, and they have learned a lot that they can now use in their day-2-day job. If you also want to have the experience that Hannes has enjoyed, I’m providing my “Advanced SQL Server Performance Troubleshooting Workshop” to you, starting with September across Europe. See http://www.csharp.at/events.html for further information.

This workshop is a “compact” 3-days long advanced training about serious performance problems/bottlenecks that you WILL encounter in your SQL Server production systems. We will have a look on how to find and identify those problems, and – of course – how to solve them. I’m currently providing the public course training in Austria and UK, but other countries like Switzerland, Germany, Norway, etc. are already in my pipeline.

If you are not able to attend the training, because of time constraints or other reasons, you can also contact me , so that we can arrange a customized in-house SQL Server training for your company.

Thanks

-Klaus