This methodology has several steps:
1. Make an initial assessment and establish a baseline.
2. Monitor the system.
3. Analyze the results.
4. Create a hypothesis.
5. Propose a solution.
6. Implement changes.
7. Test the solution.
8. If other problems still exist, return to Step 2; if not, exit.
Step 1: Initial Assessment
* Learn about the application
* Ask about how it works and what it does
* Determine what the reported problem is
* Document database size, tuning parameters, and so on
* Look at the system as a whole
* Validate parameters
Step 2: Monitor the System
* Operating system tools: perfmon, task manager, event viewer
* SQL Server tools: error log, sys tables
* Third-party tools
* Analyze operating system and SQL Server configuration parameters
Step 3: Analyze Results
* Analyze monitoring data
* Review error logs
* View customer performance data from their monitoring software
Data that should be include:
* CPU utilization
* I/O utilization and response time
* Memory utilization
* Errors reported in the error log
* Wait stats (if available)
Step 4: Create a Hypothesis
* Formulate a theory: I/O problem, locking problem, and so on
* Document your theory
* Back up that theory with data
Step 5: Propose Solution
* Developing a solution
* Developing a validation plan
* Documenting expected results
Step 6: Implement Change
* A hardware change
* A configuration parameter change
* Adding an index
* Changing a query or using a hint
Step 7: Test Solution
* Change only one thing at a time
* Document the result of the change
* Compare performance after the test to the baseline metrics
* If possible, test the change in a nonproduction environment
* If possible, run load tests
Step 8: Go to Step 2.
Once you have started Step 7, you should return to Step 2;
monitor the system in order to gather data about the state of the system
while the test is going on.
Follow the methodology until you run out of time, budget, or problems.
By documenting each step, you will not only get better results,
but you will be better able to create professional and complete reports
on the engagement, the problem, the solution, and the results.
Source: Microsoft SQL 2005 Administrator's Companion,
Chapter Troubleshooting, Problem Solving and Tuning Methodologies
Page 933-937