Like most DBA’s I’m sure you often find yourself delivering information to the business concerning the performance of your SQL Server platform but have you ever stopped to consider the wider implications of your actions?
As a SQL Server Database Administrator you are responsible for the overall health and well being of your SQL Server database servers. As part of this, one of your key responsibilities is ensuring that SQL Server is providing a sufficient level of performance.
You probably proactively manage and monitor the performance of your environment in order to validate this, perhaps through the creation and review of baseline performance metrics or with the use of third party monitoring tools.
You Are Monitoring Performance Right?
If you are not currently monitoring the performance of your environment you absolutely should be! As a DBA you have no excuse for not monitoring your SQL Server environment given that you can roll your own performance monitoring solution with very little effort. Doing so is essential to understanding both the resource demands placed on SQL Server and ensuring that it is delivering the performance required by your users.
For details on how to get started and roll your own performance monitoring solutions take a look at Brent Ozar’s excellent SQL Server Perfmon (Performance Monitor) Best Practices.
T-SQL Query Performance Monitoring
As part of your performance monitoring activity you will want to regularly review and identify the poorest performing T-SQL queries in your environment. There are a variety of different methods you can use to do this and a number of these are listed below.
How to Identify Poorly Performing Queries:
- Performance Dashboard Reports
- SQL Server DMV’s - How to identify the most costly SQL Server queries using DMV’s
- SQL Server Profiler
- Third Party Monitoring Tools – (Insert your favourite product here)
So now that you have identified the most poorly performing T-SQL queries, what next?
Providing Feedback to the Business
With your T-SQL query performance metrics and analysis in hand you’re keen to share the details of your findings with the business but before you go any further, just stop and think for a moment about what it is you are about to do.
You have in your hands a conclusive list of information that not only identifies the worst performing T-SQL queries for your database server but you also have a mountain of evidence in support of this, a clear and irrefutable case. The information you posses has the potential to make quite a few people look bad and possibly even stupid, especially if your analysis has identified some particularly poor coding practices.
So before you publicise your findings and publically declare to the entire development team that they’re a bunch of idiots and you have the data to prove it, I implore you to take heed of the number one rule in SQL Server Performance Politics…..
…..No One Likes a Smart Ass
Being the clever DBA that you are I am certain that you have an abundance of great ideas as to how to improve the performance of the queries you have identified. Whether it be through the creation of new indexes or even a complete overhaul of some of the worst T-SQL you have ever laid eyes upon.
Don’t bring attention to your own amazing work by putting down the work of others either. You will not win yourself any friends and there is no gain to be had from directly showcasing the mistakes of others. Simply put, if you are not the author of said source code then the best thing you can initially do is to just keep your mouth shut. This is done to give the author the time to digest the information you have provided.
So how do you go about effectively implementing change? Funny you should ask. Next up, How To Provide Great Feedback.