Performance of a SQL Server seems to be one of the main concerns for most developers and DBAs. We are under pressure to constantly squeeze more performance out of our applications and reduce the time it takes for users to interact with systems. Reports can be especially problematic as users don’t want to wait for results, but certainly data entry tasks can’t be impacted either.
Whether or not we can actually make changes to the system, or even have time to bother might be irrelevant. I’m sure there are plenty of databases that some of us don’t even bother to try to tune because we’re busy elsewhere. Likely writing more code for additional features or reports that have been requested. As a result, we may pile up lots of code that isn’t necessarily run often. However when that code is executed, we’ll receive no shortage of criticism if the code doesn’t perform well.
This week I wanted to know how you might go about finding the code that is problematic if there isn’t a complaint. If you do have time to pro-actively tune your system, what are the techniques you use to examine a system. I suspect the more advanced people will have answers, and I hope they share them as there’s no shortage of readers that may struggle to improve the impressions of their database server.
What’s the best way to find the SQL statements you need to tune?
It’s a simple question, but imagine that you know there are complaints, but don’t know what is the best way to focus your time. You, as a new or accidental DBA, want to improve the system, but where do you start? What code is the problem? Is the report that your boss complains about that’s the issue or are there other queries that slow down the report execution?
Share the way you’d tackle a system when you don’t have a specific query in mind to tune.