sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Training for SQL Server Developers

Issue 6 Number 2 February 2014

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.

Read More About Developer Training

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

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

Training for SQL Server Developers

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator