Don't just release new software and hope for the best. Contributor Jeremy Kadlec explains the importance of establishing a performance baseline in this three-part series. In part one, below, he explains how to validate a baseline. In part two he covers the top 10 performance monitor counters. In part three Kadlec discusses how to capture a performance baseline with SQL Server Profiler.
Does this conversation sound familiar?
Tony: So how is the system doing today after the code release?
Angie: Not too bad. I have not heard the users complain yet, so no news is good news.
Tony: We will see how things go at 9:15 a.m. when all the users are on the system.
Angie: I hope we don't have any calls.
Tony: Yeah, me too. I am not too sure what we are going to tell them this time if they are having problems.
Angie: Let's just say it is an application problem, not a database problem.
Rather than release software and hope no issues occur, a performance baseline would be a much less stressful option. A performance baseline is a set of metrics to determine the performance of the SQL Server during normal circumstances. You would use it to determine if the system is performing in accordance with the baseline or if the system resources are exceeding the baseline and require your attention regarding the performance issue. Since no system is stagnate, the performance baseline should be recalculated for accurate comparisons after each major change is made to the system, whether it's a change in software, hardware, number of users and data, processes and so on.
From a SQL Server operations perspective, most organizations have an informal performance baseline that is typically accurate and based on user and IT team experiences. This is a critical first step that normally evolves as IT team members spend more time with their department. To expedite this process, it is imperative to formalize the performance baseline to test an application prior to its release and then determine if a performance problem occurs in the production environment.
Internal knowledge
To baseline by perception rather then definitive metrics, you must have internal knowledge about your application and users to serve as the first line of defense. To validate the performance baseline, the IT team could start the application and personally test the application's performance or go to a user's desk to see the performance problem. This pseudo baseline would include information from the following observations:
- Application response time
- The amount of locks per table
- Frequency of blocking and the cause
- Number of users
- Typical business hours for the users
- Batch processing schedule
- Database size and growth
- User (good or bad) habits
Although this information is neither the most scientific nor sophisticated, it can typically serve as a base set of information to further troubleshoot the performance issue as compared to normal operations.
Stay tuned for more of Jeremy Kadlec's tips on performance baselining.
About the author:
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
More information from SearchSQLServer.com
Tip: SQL Server performance-tuning worst practices
Tip: Performance tuning freebies to add to your favorites
Topic: Research performance and tuning best practices
