What is the best way to combat poor performance in your SQL server? After twenty-plus years of working with SQL Server, I would answer “Education”. The most frequent and most difficult cases of poor performance I have seen are the result of inadequate training for application developers.
Formal education for application developers is focused on developing applications using one or more modern development platforms like .Net, Java, etc. Developers often receive little or no training in the practical use of the SQL language itself. This is unfortunate because it is usually SQL code performance, not application code performance that determines whether your application will be fast or slow. While most developers receive enough SQL training to write accurate SQL queries, they are often not schooled in methods that insure optimal performance of the SQL code they write.
As you may have observed, making small changes to SQL queries can make a great deal of difference in performance. A query that has been completing in 15 minutes might complete in less than a second after a slight change in query syntax. Even more remarkable improvements are common. To accomplish these changes, however, requires an understanding of concepts that are not commonly taught in the IT schools.
In my career I have looked at a lot of poorly performing queries and have seen the same performance killers over and over.
- Indexing that leads to the creation of unusable indexes and causes full table scans rather than efficient index seeks. I often find when I examine a database that there are hundreds of indexes that have never been used. This is a sure sign that further education is needed.
- Questionable placement of clustered indexes or no clustered indexes at all on a table.
- Unindexed Foreign Keys
- Unsearchable arguments in the WHERE clause that make a query unable to use the indexes that it should.
- A lack of awareness of implicit conversions that also make a query unable to use the indexes that it needs.
- Heavy use of cursors, table-valued functions, table variables, etc.
All of these issues are avoidable and point to a need for further education.
When I am called to a client’s site to deal with poor SQL performance, I usually take the time to go over this list of issues with the developers. I show a couple of presentations that cover ways to avoid these common performance issues. After the presentations, we use the knowledge attained to work together to optimize the current problem queries.
As well as fixing existing problems, it is very unlikely after this session that the developers will make the same mistakes in future work.
I feel this ad-hoc training for developers using their own databases and fixing their own problems in their office offers the best value for my client. For one thing, it does not require them to be out of the office for a period of days at a training center.
However, there are many other more formal SQL training courses available that may fit your needs better. But no matter how you choose to do it, I think you will find that further SQL training for your developers will pay off in the long run.