In earlier articles, the detect methodology in performance tuning was mentioned, and the Detect methodology was briefly reviewed here.
Discover the problem: finding problems
Explore The conditions: Reasons to explore
Track down possible approaches: providing a possible solution
Execute the most likely approach: perform the best possible solution
Check of Success: Confirm success (if not successful, repeat the above steps)
Tie up Loose Ends: complete Remaining Work
First look at Discover, find the problem
Has the whole problem been succinctly described?
Where is the user's current baseline?
What does the user expect?
It's not all the problems that can be solved.
Look at explore again, explore the reasons
Obtain evidence
- SQLProfiler Trace/sql Trace
- DMV and DMF
- Showplan Execution Plan output
- Performance counters for various systems
- SQL Server-specific performance counters
Check the most obvious questions (don't go into the question first)
Track down provides a possible solution
The first stage, the establishment of a plan to prove the hypothesis
The second stage, the establishment of a problem-solving plan
Execute the most likely solution
The first stage, execute the test plan to prove your hypothesis.
Phase II, implementation of the solution to the problem
Check to confirm success or not
First Stage
Did your plan prove your hypothesis, or did you overthrow him?
Phase II
- Does your plan change the phenomenon?
- Is the bottleneck shifting?
- Does the solution match your original goal?
Remember: The process is often recursive and repeated
Tie up completes the rest of the work, ending
- Is performance tuning up to an unpredictable marginal effect?
- Did the changes really solve the problem, and will they encounter the same problem in a short time?
- What kind of tracking work do you need to do?
It said that the methodology, although very boring, but it is instructive. Here's some practical knowledge.
Bottleneck analysis
Definition of bottlenecks
Bottleneck = Rate of demand > actual throughput
Process:
- Deciding which point to be stuck on
- Determine what is waiting in the queue
- Reduce input (rate of demand) or increase simultaneous throughput
- Determining income
- Determining costs
monitoring tasks for common bottlenecks
- Monitor memory consumption
- Monitor thread and CPU usage
- Monitor HDD IO
- Monitor low performance queries
- Monitoring stored procedures, SQL, and user activity
- Monitor current locks and user interactions
Build a performance tuning plan
Performance tuning is the iterative process, again and again, the again cycle, again and again approaching the revision, to use the text records to explain
- Make a point, highlight the problem and prove
- The approximation target of the system
- Have a consensus, know each other talking about the land
- Ability to summarize comparisons
- When the system comes up with multiple bottlenecks, find the most critical, lowest-cost first-perform tuning
When performing a performance tuning plan, determine the impact on the on-line production environment
Methodology--narrowing
Common tasks in Performance tuning
- Windows Event Viewer
- Windows System Monitor
- Current active window in SSMS
- T-SQL Tools
- SQL Profiler
- Query Analyzer
- Database Engine Tuning Advisor
Windows Event Viewer, primarily to view the following event logs
- Windows Application Log
- Windows system logs
- Windows security Log
Windows System Monitor is able to track:
- SQL Server I/O
- SQL Server memory
- SQL Server user
- SQL Server lock
- Copy activity
Activity Monitor in SSMs:
- Active user Tasks
- Resource Waits
- Data file I/O
- A resource-intensive query
T-SQL Tools:
- System stored Procedures
- Global variables
- T-SQL statements
- Dbcc
- Trace flag
- Dmf/dmf
SQL Profiler, tracking and capturing SQL Server events
- Select the events you want to track
- Select a track template
- Select the data you want to capture
- Make sense of classifying data
Query Analyzer
- Show Query Execution plan
- Show Server Traces
- Displaying server-side statistics
- displaying client statistics
Database Engine Tuning Advisor
- Analyze bottlenecks
- Give the recommended SQL statements (indexes and statistics)
SQL Server performance tuning methodology and common tools