Shulou(Shulou.com)05/31 Report--

This article is to share with you about SQL Server performance tuning methodology and common tools, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article, without saying much, follow the editor to have a look.

When it comes to "tuning", it may make many technicians passionate, or it may make many people feel distressed. SQL Server performance tuning is a broad topic. This paper briefly introduces a methodology of SQL Server performance tuning and introduces several common tools.

First look at Discover and find the problem.

Has the whole problem been described succinctly?

Where is the user's current baseline?

What do users expect?

Not all problems can be solved.

Take a look at Explore to explore the reasons.

Obtain evidence

SqlProfiler tracking / Sql Trace

DMV and DMF

ShowPlan execution Plan output

Performance counters for various systems

Performance counters specific to sqlserver

Examine the most obvious problems (don't delve into them yet)

Track down offers possible solutions

* phase, establish a plan to prove the hypothesis

In the second stage, establish a plan to solve the problem.

Implement the most likely solution

* phase, execute a test plan to prove your hypothesis

In the second stage, implement the solution to the problem.

Check, confirm the success or not

* stage

Does your plan prove your hypothesis, or does it overturn him?

The second stage

Has your plan changed the phenomenon?

Has the bottleneck shifted?

Does the solution meet your original goals?

Remember: this process is usually repeated recursively

Tie up finishes the rest of the work.

Does performance tuning have unpredictable marginal effects?

Do the changes really solve the problem, and will you encounter the same problem in a short period of time?

What other tracking work needs to be done?

The methodology mentioned above is boring, but it is still instructive. Let's have some practical knowledge.

Bottleneck analysis

Definition of bottleneck

Bottleneck = rate reached by demand > actual processing capacity

Process:

Decide at which point it is stuck.

Determine the condition of waiting in the queue

Reduce input (the rate at which demand is reached) or increase simultaneous processing capacity

Determine the income

Determine cost

Monitoring tasks for common bottlenecks

Monitor memory footprint

Monitoring thread and CPU usage

Monitor hard disk IO

Monitor low-performance queries

Monitor stored procedures, sql, and user activity

Monitor current locking and user interaction

Establish a plan for performance tuning

Performance tuning is an iterative process, repeated cycles, approaching corrections again and again, to be explained by written records.

Sort things out, highlight the problem and prove

Approaching target of the system

Have a consensus and know the landmarks we are talking about.

Be able to summarize and compare

When there are multiple bottlenecks in the system, find out the most critical, cost tuning first.

When implementing a performance tuning plan, determine the impact on the online production environment

Methodology-shrinking

Common work in performance tuning

Windows event Viewer

Windows system Monitor

Current active window in SSMS

T-SQL tool

Sql Profiler

Query analyzer

Database engine tuning consultant

Windows event Viewer, mainly to view the following event logs

Windows Application Log

Windows Syslog

Windows Security Log

The windows system Monitor can track:

Sql server I/O

Sql server memory

Sql server user

Sql server lock

Replication activity

Activity Monitor in SSMS:

Active user task

Resource waiting

Data file Icano

Resource-intensive queries

T-SQL tool:

System stored procedure

Global variable

T-SQL statement

DBCC

Tracking mark

DMF/DMF

Sql Profiler, tracking and capturing sqlserver events

Select the events to be tracked

Select tracking template

Select the data to be captured

It makes sense to classify the data

Query analyzer

Show query execution plan

Show server trace

Display server-side statistics

Display client statistics

Database engine tuning consultant

Analysis bottleneck

Give suggested sql statements (indexes and statistics)

These are the SQL Server performance tuning methodology and common tools, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.