This content is part of the Essential Guide: Microsoft SQL Server training: What you need to know
Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server performance-tuning checklist: Ready, set, go!

When developing a high-performance SQL Server, start with a talented team and follow these steps that will have you continually measuring, tweaking and learning from your mistakes.

What do I need to do? There is so much, where do I start? What should I do next? Wow, my SQL Server is flying! If you start out not knowing where to go and end up with an exceptionally performing SQL Server, you made it. Take a look at this SQL Server performance-tuning checklist to achieve high performance.

Get ready

As you begin to address an existing performance-tuning issue or start off with a new system where high performance is a significant expectation, spend time planning and analyzing how to achieve project goals. Make sure the team understands what the SQL Server is doing now, what it's supposed to do and what it should do well into the future.

It's ALL about the team

    • Build a talented team with the proper technical knowledge.
    • Ensure the technical team understands how individual responsibilities affect the organization.
    • Allot time for team training (conferences, user groups, informal, self paced, class room, etc.).
    • Stock a technical library of books and electronic resources.
    • Set up a sandbox for testing without any strings attached or consequences imposed.
    • Do not forget to reward the team when achievements are accomplished.

The many realizations

    • Establish a set of realistic business and technical requirements for SQL Server in terms of Web, online transaction processing, online analytical processing, mobile and batch applications.
    • Set realistic expectations for the team regarding deliverables and time frames.
    • Make sure the team has a clear and accurate understanding of the current and future performance needs, and avoid any surprises that can significantly impact the future.
    • Get to the lowest level of detail possible and try to leave no rock unturned. Unfortunately, the expression 'the devil is in the details' is extremely accurate.
    • Assess time that's available to address high-performance issues and compare it to the time available to do the remainder of your daily tasks and do not be afraid to communicate realistic time frames.
    • Discuss the benefits of taking time to achieve high performance with a properly developed system versus rushing to develop and push out a system that requires significant maintenance.
    • Define the SQL Server's uptime, and set expectations for costs versus performance needs associated with achieving that uptime.
    • Ask questions from both a technical and business perspective to understand how systems are expected to change and grow in one, three and five years.
    • Before the hardware platform is tossed around, outline a capacity-requirements plan.
    • Make agreements with the entire team for maintaining SQL Servers on a daily, weekly, monthly or quarterly basis.

Get set

Once you and the team understand the requirements, now is the time for your IT skills to shine. Get creative and think about opportunities to leverage the native SQL Server functionality, third-party products and your own development effort.

Stop and think

    • Analyze requirements and begin to sketch out individual components in the application.
    • Schedule formal or informal meetings with your team members to bounce off potentially earth-shattering ideas with them regarding functionality, flexibility and pure speed.
    • Think about issues experienced with other applications in the environment or from your previous experiences and plan to not only avoid those issues, but to make those portions of the application the strongest.

Set up for success

    • Set up the hardware platform to meet individual database component needs with the flexibility to meet additional needs.
    • Build a simple yet comprehensive database design with sufficient indexes that are clearly documented in a data dictionary.
    • Develop the database objects, middle-tier applications and front end with an eye for high performance at high levels of user concurrency.
    • Stress test each functional component in the application independent of the remainder of the application to ensure each component is properly optimized, then stress test the overall application.
    • Based on the stress test results, begin to optimize queries for improved performance, and repeat the stress testing for performance validation.
    • Build a training system for users to learn how to use the system as it was designed and to load test with a subset of system users as a baseline prior to the production release.
    • Finalize documentation prior to rolling out the application. Require IT folks -- rather than the business champion -- to sign off on it and to build a valuable reference guide that will be beneficial six months down the road, after working on 100 other projects and needing to add additional functionality.

Go! Go! Go!

Once the design and development are complete, make sure the application is not lost in the shuffle. Give it the attention it needs to perform as expected in the long term.

Open up the floodgates

    • Pilot the application with a small set of users on the system to validate performance. Then begin to add users to the system on a regular schedule.
    • Develop a performance baseline on the number of users, transactions, application build and hardware versus the perceived user performance.
    • Lock down SQL Server security to prevent unexpected changes from entering into the equation and negatively impacting performance.
    • Establish a change management process to properly manage system changes with the ability to easily roll back to a previous system state.
    • Review the server schedule to prevent multiple applications from bogging down the server at one time and having no processes execute during another.

Keep an eye on it

    • Allocate time to monitor SQL Server on a daily or weekly basis with native tools (SQL Server Profiler, Performance Monitor, Index Tuning Wizard, etc.). Monitoring should not be reserved for times when the SQL Server is perceived to have performance issues.
    • Build internal scripts that can be easily accessed when you are having a locking-and-blocking issue or need to monitor business-related processes, such as flags in user tables or batches in an inventory control process. Having this knowledge of the system will empower you.
    • Capture performance metrics on a regular basis to understand and measure performance.

Start spreading the news

    • Hold a lessons-learned meeting to reinforce the positive aspects of the project, determine areas that require improvement, and set up a plan to implement these items.
    • Schedule a 'lunch and learn' event where team members can share knowledge.

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 Performance Tuning expert.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.