SQL Server Performance Tuning experience Summary _mssql

Trust a lot of friends, whether it is to do development, architecture, or DBA, are often heard that the word "tune". Speaking of "tune", may let a lot of technical staff heart surging, may also make a lot of people feel distressed. Of course, there are a lot of people are dismissive of this, because not everyone has access to the project is very large, and not everyone does the project is very high performance requirements.

In the mainstream enterprise-level development and Internet applications, the importance of the database is self-evident, and the performance of the database for the overall performance of the system is also critical, here Shire.

The performance tuning of SQL Server is actually a very broad topic. To be frank, it is possible to have at least a few books of content to be completely clear and thorough, from concept to practice. This article is only a concept-level summary, I hope that readers can have a new understanding of this, in the tuning on the road to help. If you are interested in a lot of friends, follow-up may share some actual combat experience.

First, make sure that the goal of performance tuning

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

Optimizing Response Time

What is "Optimizing response time"? The popular point is that after tuning, the execution of queries, updates, and other operations, the database reaction faster, the time spent less.

More common, the previous execution of a SQL query statement, it may take 3 seconds, indexed, 1 seconds before the finish. Indexed, this is also the most typical of the most "cheap" optimization means.

When you do "Optimizing response time," You need to know about user environments, programs, environments, users, and data.

Optimize throughput

When it comes to "throughput," you have to think about concurrency. is actually the ability to "process requests simultaneously". How to improve the database "anti-concurrency" ability? The first thing to understand is how SQL Server accesses data, how to control concurrent access (transaction isolation level, locks, etc.), how to interact with the underlying operating system, and understand multithreading, process, and more.

The more common approach is to reduce transaction isolation levels (to some extent sacrificing data consistency, etc.), and this "soft" method usually works well. Second, a single DB server to achieve a certain bottleneck, through the "cluster" and other means, to achieve the requested "load balancing" to achieve the goal of "anti-concurrency", the effect is immediate.

The methodology of performance tuning--Iteration

Baseline

Popular point, is used to calculate or compare the standard. It is usually based on current system performance or on the basis of matching system performance. refers to each component to play to the maximum.

Cost

The time, money, labor, etc. used to upgrade, change, etc. to improve the component performance.

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

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

How the baseline is represented, including the batch (job) completed per second, the number of transmissions per second, the amount of data per second, disk scan time, and so on

Analyze the factors that affect performance:

Database design (whether composite paradigm, reasonable filing, zoning, table, etc.)

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

Hardware infrastructure (equipment specifications, hardware performance, load balancing, disaster tolerance, etc.)

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

The order of performance tuning:

From left to right, from the technical difficulty, cost, effectiveness to consider

Detect method

Identify problems, explore causes, provide possible solutions, execute the most likely solution, confirm successful resolution (if not, repeat previous steps), and complete the rest of the work

These work in the detect methodology are subdivided, and there will be many that are not described here for the time being. Specific tuning steps, performance tuning tool use, the next article continues.