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

This article focuses on "ways to tune the performance of sqlserver". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to tune the performance of sqlserver.

First of all, figure out the goal of performance tuning.

From the most intuitive and common point of view, it mainly includes the following two points:

Optimize response time

What is "optimized response time"? The popular point is that after tuning, when performing queries, updates and other operations, the database responds faster and takes less time.

It is more common to execute a sql query statement in the past, which may take 3 seconds. After adding the index, it will be done in less than 1 second. Indexing, which is also the most typical and "cheap" optimization method.

When doing "optimize response time", you need to know: user environment, program, environment, user and data knowledge.

Optimize throughput

When it comes to "throughput", you have to think of "concurrency". In fact, it is the ability to process requests at the same time. How to improve the ability of database "anti-concurrency"? First of all, we need to understand how sqlserver accesses data, how to control concurrent access (transaction isolation level, locks, etc.), how to interact with the underlying operating system, and how to understand "multithreading, processes" and so on.

The more common means, by lowering the transaction isolation level (sacrificing data consistency to a certain extent, etc.), this "soft means" usually works well. Secondly, after a single DB Server reaches a certain bottleneck, it can achieve "load balancing" through "cluster" and other ways to achieve the purpose of "anti-concurrency", and the effect is also immediate.

Methodology of performance tuning-- iteration

Base line

In popular terms, it is a standard used for calculation or comparison. It is usually based on current system performance, or on matching system performance. Refers to the maximum performance of each component.

Cost

Time, money, labor, etc., used to upgrade, replace, etc., to improve the performance of components.

The definition of a baseline, based on user expectations, may involve the following factors

Past experience, application benchmarks, industry standards, previous versions of the situation

The representation of the baseline, including batch processing (jobs) completed per second, transmission per second, data per second, disk scan time, etc.

Analyze the factors that affect performance:

Database design (whether compound paradigm, reasonable archiving, partitioning, sub-table, etc.)

Software system (operating system optimization, database system configuration, resource planning and monitoring, etc.)

Hardware infrastructure (device specifications, hardware performance, load balancing, disaster recovery, etc.)

Sql statement writing, index and statistics, transactions and locks, application access code (too many connections, frequent switches, etc.)

Order of performance tuning:

From left to right, consider the technical difficulty, cost and effectiveness.

DETECT method

Identify the problem, explore the cause, provide a possible solution, implement the most likely solution, confirm that the solution is successful (if not, repeat the previous steps), and complete the rest of the work

At this point, I believe you have a deeper understanding of "the way to tune sqlserver performance". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.