SQL Server Performance Tuning series (1)-Overview

I. Preface

 

I plan to write some ideas about SQL server performance tuning in the near future, that is, it is too complicated to give a clue. when we write SQL statements, design tables, create DBAs, or create projects, there will be many performance considerations. Many forums will also frequently ask: why is the SQL running so slow or inefficient?

 

If the database to be managed is relatively large (maybe> 50 GB), performance considerations are very important. in terms of business logic, we certainly hope that the background data can be processed quickly and a request can be submitted to quickly respond. if the background data processing is too slow, the front-end page will be suspended, which may easily lead to the illusion of the user, and the program is dead.

 

SQL Server is currently one of the most widely used large database systems. After a large database system runs for a period of time, problems such as slow running, performance degradation, and increased failures may occur, to keep the system running properly, you must constantly adjust the system ". In this way, our front-end page or back-end data processing can be efficiently executed. performance Tuning is not a simple task. Generally, it requires extensive experience and knowledge, not just database experience, we also need to have a basic understanding of business logic, system architecture design, writing applications, operating systems, setting up network environments, using various detection and monitoring tools and programs, security and anti-virus, etc, in order to find the crux of the problem in a complex system.

 

I am not an expert in Performance Tuning. I can only share some comments and ideas of myself. I would like to invite you to consult with experts and scholars!

 

Ii. Performance Tuning Theory

 

With the 80/20 principle, we can find 20% of the most influential efficiency to achieve 80% or more results.

1. five basic principles of Adjustment

(1). Global consideration, partial suspension.

Only when the problem is found correctly and the interference to the database is minimized can the problem be effectively optimized. Global factors must be taken into account during some local adjustments.

(2). Division breaks the bottleneck.

In most cases, the system runs slowly because a module in the system occupies a large amount of resources, blocks access from other processes, further limits the system performance, and causes bottlenecks.

(3). High startup cost and low operation cost.

The startup overhead is lower than the running overhead. Use as few startup times as possible to get the best performance.

(4). Integration Between the server and the client

/SCRIPT>

And load balancing.

Load ing balancing. If multiple servers exist, load ing can be evenly divided to improve overall performance.

(5). Price-performance ratio.

The high speed of SQL server requires support from the configured hardware.

,

Therefore, a trade-off between performance and price is required.

 

2. tuning is to identify the bottleneck of the system, optimize it to eliminate the bottleneck, and improve the system operation efficiency. At the beginning, you can consider the following aspects.

(1). server hardware (CPU, Io, Rom, if there is a bottleneck, consider hardware upgrade)

(2). Raid

(3). SQL Server Configuration

(4). Table Structure Design (including effective use of indexes)

(5). Regular maintenance plans (including backup history data, rebuild index, reorganize index, shrink database, etc)

(6). T-SQL programming (find out the SQL statements with low running efficiency, optimize)

 

Iii. Performance Tuning methods and Optimization Techniques

 

1. Use server performance monitor for monitoring (Performance Monitor and powershell get-counter tools are introduced)

 

2. Profile: trace SQL Execution to find the SQL statement or SP that consumes a lot of resources.

 

3. SQL Server Configuration

 

4. Index and T-SQL Program

 

.

.

.

......

 

 

SQL Server Performance Tuning series:

SQL Server Performance Tuning series (1)-Overview

SQL Server Performance Tuning series (2) -- server Performance Monitor (perfmon)

SQL Server Performance Tuning series (3) -- powershell get-counter (Planning ...)

SQL Server Performance Tuning series (4) -- profiler (I)

SQL Server Performance Tuning series (4) -- profiler (II)

SQL Server Performance Tuning series (5) -- SQL Server Configuration

SQL Server Performance Tuning series (6) -- Index Structure and Tuning

SQL Server Performance Tuning series (7)-raid

SQL Server Performance Tuning series (8)-execution plan (in plan ...)

...

To be continued ....