SQL Server performance tuning methodology and common tools

Source: Internet
Author: User
Tags cpu usage server memory

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.