SQL Server Performance Tuning experience

I believe many of my friends, whether engaged in development, architecture, or DBA, often hear the word "tuning. Speaking of "tuning", it may make many technical staff feel excited, or it may make many people feel distressed. Of course, there are also many people who despise this because not everyone is exposed to a large number of projects, and not everyone does have high performance requirements.

In mainstream enterprise-level development and Internet applications, the importance of databases is self-evident, and the performance of databases is also crucial for the performance of the entire system.

The performance tuning of sqlserver is actually a broad topic. To be honest, to fully understand the concept and practice, you may need at least a few books. This article is just a conceptual summary. I hope that you can have a new understanding of this and it will be helpful in tuning the path. If you are interested in many friends, you may share some practical experience in the future.

First, clarify the performance tuning objectives.

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

Optimized Response Time

What is "optimized Response Time? In other words, after optimization, when performing queries, updates, and other operations, the database responds faster and takes less time.

It is quite common that it may take three seconds to execute an SQL query statement. After the index is added, it will take less than one second. Add indexes, which is also the most typical and cheapest optimization method.

When optimizing the response time, you need to know about the user environment, programs, environments, users, and data.

Optimized Throughput

Speaking of "throughput", we need to think of "concurrency. It is actually the ability to "process requests at the same time. How can we improve the database's "anti-concurrency" capability? First, you must understand how sqlserver accesses data, how to control concurrent access (transaction isolation level, lock, etc.), and how to interact with the underlying operating system, we also need to learn more about multithreading and processes.

This "soft means" is usually very effective by reducing the transaction isolation level (sacrificing data consistency to a certain extent. Second, after a single DB Server reaches a certain bottleneck, it can achieve "Load Balancing" of requests through "cluster" and other methods to achieve the goal of "anti-concurrency, the results are also immediate.

Performance Tuning methodology-Iteration

Baseline

In layman's terms, it is the standard used for calculation or comparison. It is usually based on the current system or matching system. The maximum number of components.

Cost

Time, money, labor, and so on for upgrading and replacing components to improve component performance.

The baseline definition is based on user expectations and may involve the following factors:

Previous experiences, application benchmarks, industry standards, and previous versions

Baseline representation, including: Batch Processing (job) completed per second, transmission volume per second, data volume per second, disk scan time, and so on

Analyze factors affecting performance:

Database Design (whether it is a compound paradigm, whether it is reasonable to archive, partition, table sharding, etc)

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

Hardware infrastructure (Device specifications, hardware performance, Server Load balancer, Disaster Tolerance, etc)

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

Performance Tuning sequence:

From left to right, considering technical difficulty, cost, and effectiveness

DETECT Method

Discover the problem, explore the cause, provide possible solutions, execute the most likely solution, confirm whether the solution is successfully solved (if not, repeat the previous steps), and complete the remaining work

DETECT MethodThere will be a lot of work in detail in this article. I will not describe it too much here. For detailed optimization steps and use of performance tuning tools, continue in the next article.