|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SQL Server performance-tuning checklist |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|  |
|
|
|
|
|
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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
