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 drop me an email,

so that we can arrange a customized in-house SQL Server training for your company.

Thanks

-Klaus