The problem with performance tuning is that it is commonly considered as secondary to the task at hand rather than as an essential element of it.

Unfortunately all too often performance is not even a consideration until there is actually a problem to be fixed. I know I’ve certainly experienced this in the field and I’m sure that you

have too. As Data Professionals we are often tasked with troubleshooting issues that are ultimately down to performance. Whether it is an entire platform, a single SQL Server Instance or a specific individual query, most of the time there is already a problem that needs to be solved.

"The clock is ticking on your performance"

Just think about that for a moment, the performance issue that you are tasked with solving exists already. By definition you are operating in a reaction fashion or rather in Reactive Mode, you are responding to something that has already become a physical problem for you and that has real implications for your business.

What if you could get to things before they became a problem? Well you can! To do so you need to concentrate more of your efforts toward operating in Proactive Mode, a philosophy that we touched upon in The Best Database Administrators Automate Everything.

You can get a real head start on your performance tuning efforts for both yourself and your company by simply following these three principles.

Design with Performance in Mind

It almost sounds too obvious and too simple right. Once you and your teams are accustomed to doing this it will become second nature and a natural part of your development process.

Designing with performance in mind is all about Thinking Defensively. Ask yourself questions such as how the code is going to scale, is the query sargable, have you reviewed the execution plan to really see and understand what’s going on, is the CPU utilisation acceptable, what about IO, do all those tables really need to touched in a single query and so on….

“Make a conscious decision to address performance before it becomes a problem for you by designing with performance in mind.”

Test Specifically for Performance

How can you be sure that your code/application is going to perform at acceptable levels in production? You have to test it of course! An essential component of your overall design process should be performance testing, not to be confused with the standard functional testing that should already be being conducted. How you go about testing the performance of your T-SQL code is another topic in it’s own right but for now the point to take away here is that this is something that you want to be doing as part of your Proactive Performance Tuning efforts.

If this is not something that is currently being practised in your shop then be prepared to take responsibility for this item yourself if it comes down to it. Get hold of a copy of the next production release, and deploy it to your dedicated DBA Sandbox/Testing environment (you do have one of these right). By taking the initiative you will not only be able to produce a solid business case for the value of performance testing to present to your manager but you will also be demonstrating irrefutably that you’re a great player to have on the team.

“Test for performance before it even has a chance to become a problem for you.”

Administer Your Environment Proactively

If you’re a Database Administrator then you want to be managing your environment in a proactive manner in order to minimise the time that you are operating in Fire Fighting Mode.

Activities such as Baselining your environments performance and Query Tuning are your friends here. You could for example make use of freely available resources such as the SQL Server Performance Dashboard Reports in order to regularly review your poorest performing queries, or you could roll your own query review method using the SQL Server Dynamic Management Views such as in this post, How to Identify the Most Costly SQL Server Queries Using DMVs. Regularly reviewing your poorest performing queries means that you can proactively target your performance tuning efforts where they are needed the most, giving you the greatest return on investment.

“Be proactive in your administration to stay on top of performance and focus on delivering improvements with the most value.”

To summarise then, the key to achieving a head start on your performance tuning efforts is to proactively dedicate time and resources to performance tuning before you actually have a performance problem.

These are just some of ways in which you can get a head start on your performance tuning efforts and I’m sure that you have some great ideas of your own. I’d love to hear about them and I’m sure other Data Professionals would too so go ahead and share some of your thoughts in the comments area below.

Credits: Photo by wwarby used under Creative Commons Attribution.