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!
4 thoughts on “Survey results: Common causes of performance problems”
Paul,
I really like all the surveys that you have been doing. Its one thing when we have to go into a client or a boss and talk about what the common issues are and how to correct issues, but the independent surveys are really helpful.
Thanks,
Chris Shaw
Thanks Chris – that’s why I do them – so I can use them as references and so can everyone else. Cheers
Is there any way to test performance of queries or procedures with high workload and large number of connections.
Yes, check out Distributed Reply in SS2012 or you’ll need to simulate the real workload in your test environment.