Database Design and performance optimization

Turn from: http://blog.51cto.com/jimshu/1250066 I. Database design and performance optimization--Overview Jimshu concerns2People review5892 people reading 2013-07-16 08:02:14

Objective

The first time I contacted SQL Server 6.5 for Windows NT 4.0 in 1998, I felt that SQL Server was just a powerful Excel file. Now in retrospect, when I was holding such an attitude, the applications I developed should be very naïve, and its performance is conceivable. It is commonplace to remember that it would take more than 10 seconds to search for a record casually. As a programmer ("Code Farmer") and DBA, I just seemed to complain about the "low", "poor quality" of the database system, or to propose upgrading the hardware of the server to the boss. Since everything depends on self-study, I did not reflect on their own database technology.

During "Sars" in 2003, I risked my life to attend the MCDBA2000 training course. The XF Kong teacher explained the system so that my knowledge and mastery of SQL Server 2000 no longer stayed on the superficial surface. With the gradual deepening of SQL Server underlying technology, my application access to the database and the daily operations are finally out of the low-level phase. After that, the various performance problems of the table and countermeasures are clear up.

In recent years, the Microsoft two-bit database "Danale" Howard Yin, Max shen articles, with the help of two experts, and refer to a number of technical books, borrow 51CTO this piece of feng Shui Treasure, I will continue to the SQL Server Performance optimization of a series of experience and experience to organize and publish it.

I. Overview

"One, database design and performance optimization--Overview", that is, this article. http://jimshu.blog.51cto.com/3171847/1250066

According to my understanding, the performance optimization of OLTP mainly lies in 2 aspects:

(1) System Architecture and design optimization

To achieve optimal performance in SQL Server scenarios, it is critical to have a good database design. In practice, many SQL Server scenarios often result in poor performance due to poorly designed databases.

System architecture Design has a direct impact on the performance of the system, if the design is not appropriate, and even face the serious situation of demolition. Some large software companies or formal software development teams typically have full-time database architect experts involved in designing the system architecture, and this process can be very lengthy. While the "Code farmers" part-time in the design often neglect some important issues, or because the development cycle is too short and the fundamental time does not consider these factors, for example, did not take into account the next 3-5 years of data growth, not taking into account the access pressure, and so on.

When designing objects such as databases, tables, indexes, views, and so on, you should not only familiarize yourself with the logical workings of these objects, but also understand the possible bottlenecks of these objects and strive for optimization in the design phase.

During the maintenance phase of the database system, you need to monitor performance through a number of tools.

(2) Query optimization

SQL Server database queries are slow for many reasons, in addition to the system architecture design problems, query efficiency is also worthy of attention.

I first focus on the system architecture and design optimization, the following from the software life cycle perspective, from the perspective of understanding bottlenecks in detail.

Second, from the perspective of software life cycle

According to the Software engineering waterfall model (Waterfall models), the life cycle of an application is composed of several parts. Database optimization should begin at the design stage and throughout the software life cycle.

1. Design

It is recommended that a full-time database architecture expert participate in the design.

Demand analysis should be forward-looking, at least for the next 3-5 years to plan the amount of data growth.

"Two, server Optimization (1) To achieve load balancing" http://jimshu.blog.51cto.com/3171847/1250070

Second, server optimization (2) Performance evaluation and load forecast http://jimshu.blog.51cto.com/3171847/1251142

Second, server optimization (3) server-side optimization measures http://jimshu.blog.51cto.com/3171847/1251545

Second, server optimization (4) Resource Governor management workload http://jimshu.blog.51cto.com/blog/3171847/1251546

"Second, server Optimization (5) Database version selection" http://jimshu.blog.51cto.com/3171847/1251547

Second, server optimization (6) WSRM management of multiple instances http://jimshu.blog.51cto.com/3171847/1252417

2. Development

Query optimization, index optimization

Third, index optimization (1) nonclustered index on the heap http://jimshu.blog.51cto.com/3171847/1252419

"Three, index optimization (2) clustered index" http://jimshu.blog.51cto.com/3171847/1252420

"Three, index optimization (3) nonclustered index on clustered index" http://jimshu.blog.51cto.com/3171847/1252421

"Three, index optimization (4) Index fragment" http://jimshu.blog.51cto.com/3171847/1254954

"Index optimization (5) Index Design Guide" http://jimshu.blog.51cto.com/3171847/1254956

"Three, index optimization (6) filter Index" http://jimshu.blog.51cto.com/3171847/1254965

Query optimization (1) ... "," Query optimization (n) ... "will be included in the next topic.

3. Testing, deployment

Database physical optimization, for example, the design of the database storage location. Optimization of system resources.

"Four, physical optimization (1) Normalization" http://jimshu.blog.51cto.com/3171847/1257298

Four, physical optimization (2) indexed view http://jimshu.blog.51cto.com/3171847/1257306

Iv. Physical Optimization (3) computed columns and their indexes http://jimshu.blog.51cto.com/3171847/1257310

"Four, physical optimization (4) partitioned View" http://jimshu.blog.51cto.com/3171847/1258453

Four, physical optimization (5) Table and index partition http://jimshu.blog.51cto.com/3171847/1258459

Iv. Physical Optimization (6) Database Engine Tuning Advisor http://jimshu.blog.51cto.com/3171847/1258469

Iv. Physical Optimization (7) View index usage http://jimshu.blog.51cto.com/blog/3171847/1258817

4. Daily operation and Maintenance

Database performance monitoring, resource optimization.

V. Performance monitoring (1) Profiler http://jimshu.blog.51cto.com/3171847/1259089

V. Performance monitoring (2) Windows performance Log http://jimshu.blog.51cto.com/3171847/1259091

V. Performance monitoring (3) SQL trace http://jimshu.blog.51cto.com/3171847/1259093

V. Performance monitoring (4) Extended Events http://jimshu.blog.51cto.com/blog/3171847/1259321

V. Performance monitoring (5) Management Data Warehouse http://jimshu.blog.51cto.com/3171847/1259322

"V. Performance monitoring (6) database Audit" http://jimshu.blog.51cto.com/3171847/1259323

Five, performance monitoring (7) SQLDIAG "http://jimshu.blog.51cto.com/3171847/1262406

Thirdly, from the angle of understanding the bottleneck

From the database principles and practical experience, there are 3 main bottlenecks that affect SQL Server:

1. CPU

"Six, CPU Optimization (1) CPU technology Classification" http://jimshu.blog.51cto.com/3171847/1265166

"Six, CPU Optimization (2) Hyper-Threading" http://jimshu.blog.51cto.com/3171847/1265237

"Six, CPU Optimization (3) processor Group" http://jimshu.blog.51cto.com/3171847/1265438

"Six, CPU Optimization (4) NUMA architecture" http://jimshu.blog.51cto.com/3171847/1266977

"Six, CPU Optimization (5) max degree of parallelism" http://jimshu.blog.51cto.com/3171847/1266978

"Six, CPU Optimization (6) DMV and Counter" http://jimshu.blog.51cto.com/3171847/1269174

2. Memory

"Seven, Memory optimization (1) Enable AWE" http://jimshu.blog.51cto.com/3171847/1269942

"Seven, Memory optimization (2) Dynamic memory allocation" http://jimshu.blog.51cto.com/3171847/1270005

"Seven, Memory Optimization (3) using DMV" http://jimshu.blog.51cto.com/3171847/1270018

Seven, memory optimization (4) Memory counter http://jimshu.blog.51cto.com/3171847/1271576

Seven, memory optimization (5) Memory pressure analysis

Seven, memory optimization (6) SQL Server 2012 Memory Management Innovation

3. Disk I/O

"Eight, IO Optimization (1) disk cluster" http://jimshu.blog.51cto.com/3171847/1298514

"Eight, IO Optimization (2) Sqlio tool" http://jimshu.blog.51cto.com/3171847/1298515

"Eight, IO Optimization (3) Sparse column" http://jimshu.blog.51cto.com/3171847/1298558

"Eight, IO Optimization (4) data compression" http://jimshu.blog.51cto.com/3171847/1299047

"Eight, IO Optimization (5) Filegroup" http://jimshu.blog.51cto.com/3171847/1299170

"Eight, IO optimization (6) tempdb" http://jimshu.blog.51cto.com/3171847/1299175

"Eight, IO Optimization (7) Reduced IO competition" http://jimshu.blog.51cto.com/3171847/1299191

Iv. Conclusion

"Nine, OLTP performance tuning and optimization--conclusion" http://jimshu.blog.51cto.com/3171847/1300717

Database Design and performance optimization