A couple of week ago I kicked off a survey about common causes of performance problems – see here for the survey.
Firstly I asked what was the root cause for the most recent performance problems you looked at – here are the results:
Secondly I asked what you think the overall most common cause of performance problems is – here are the results:
The results are not surprising, but it's good to have empirical confirmation from the community – T-SQL code and then poor indexing strategy are the top two in both surveys, accounting for roughly 50% of all performance issues.
No matter how powerful the hardware and how much memory or IOPS capacity you have, if you write crappy code that means SQL Server has to use really non-optimal query plans then performance is going to be poor. It's really quite a low proportion of the time that hardware (including the I/O subsystem) is the root-cause of a performance problem – usually it's just a symptom of a deeper problem.
One of the most common causes of poorly-performing T-SQL code and indexing strategy is that developers don't write and test the code in an environment that simulates a true production environment and workload. What works for 2 connections with 100 rows in a table isn't necessarily going to work for 1000 concurrent connections and millions of rows in a table. Another cause is making code changes directly in production without testing their impact.
Nothing much else to say here as the theme has been hit on by many people in the past, but now we have some numbers.
Thanks to all those who responded!