SQL Server performance Tuning First step

I believe a lot of friends, whether it is to do development, architecture, or DBA, are often heard of the word "tuning". Talk about "tuning", may make a lot of technical staff heart passion, also may make a lot of people feel distressed, do not know how to start. Of course, there are many people who are dismissive of this, because not everyone does the project is very high performance requirements.

In the mainstream of enterprise-level development and Internet applications, the importance of database is self-evident, and the performance of the database is also critical to the performance of the whole system, here needless.

SQL Server performance tuning, in fact, is a very broad topic. To be honest, it may take at least some of the content of the book to be completely clear and thorough from concept to practice. This article is only a basic summary of the concept, I hope readers can have a new understanding of this, on the tuning road to help. If you are interested in a lot of friends, follow-up may share some practical experience and skills.

Here to remind everyone: do not easily believe that the online spread of those "SQL optimization gold sentence", "SQL Optimization prescriptions", their practice several times, is the hard truth.

First figure out the goal of performance tuning

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

Optimize response time

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

More commonly, a previously executed SQL query statement, may take 3 seconds, after the index, 1 seconds less than to be done. The most typical and "inexpensive" optimization means.

When it comes to "optimizing response time", you need to understand: User environment, program, Environment, user and data and other aspects of knowledge.

Optimize throughput

Speaking of "throughput", it is necessary to think of "concurrency". is actually the ability to "handle requests at the same time." How to improve the database "anti-concurrency" ability? The first step is to understand how SQL Server accesses data, how to control concurrent access (transaction isolation levels, locks, etc.), how to interact with the underlying operating system, and knowledge of multithreading, processes, and more.

This "soft" approach usually has a very good effect by reducing the level of transaction isolation (to some extent sacrificing data consistency, etc.). Second, a single DB server to achieve a certain bottleneck, through the "cluster" and other means to achieve read and write requests "load balancing" to achieve "anti-concurrency" purpose (reduce the single-machine QPS and TPS), the effect is immediate.

The methodology of performance tuning--Iteration

Baseline

Popular point, is used to calculate or compare the standard. Typically based on current system performance, or as a benchmark for matching system performance. means that each component is maximized.

Cost

The time, money, labor, etc. used to upgrade, replace, and improve the performance of the components.

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 baselines are expressed, including: Batches completed per second (jobs), transfers per second, amount of data per second, disk scan time, etc.

Analyze the factors that affect performance:

Database design (whether it is a complex paradigm, whether it is reasonable to archive, partition, sub-table, etc.)

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

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

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

The order of performance tuning:

DETECT method

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

These work in the detect methodology is broken down, there will be many, and there is not much to describe here for the time being. Specific tuning steps, the use of performance tuning tools, the opportunity to share.

This article is actually the author 3 years ago written, inevitably wrong, please understand!

SQL Server performance Tuning First step