Easy to master the way of database management--the third of operation and maintenance inspection (structural design)

Objective

To do daily inspection is an important step of database management and maintenance, and it is necessary to register each inspection date and result, and may need to make a inspection report.

This series is designed to address some common problems:

• I don't know what to patrol.
• Don't know how to get a quick checkup
• Machine Too much physical examination trouble
• Difficult to generate reports, no visual rendering of results

The first two articles on the server hardware and software configuration, database overview of the inspection, the following we have to check some of the database structure design, these checks are mainly aimed at the initial stage of the system development of some non-optimized structural design or in the system is constantly new functions or changes in the operation of the structure changes.

Physical Structure Overview

View information for each database in check-all, and the platform prompts for a warning when the database structure does not pass a regular check.

Note: The check information mainly contains the nonstandard table, missing index, no index foreign key, unused index, duplicate index, aging index.

　　

　　

Non-canonical table

Check the existence of an irregular design for the tables in the system in database-non-canonical tables. These nonstandard designs primarily contain nonclustered indexes, using the old data type, and the columns of the clustered index are randomly growing (guid,uniqueidentifier type)

Note:

1. Microsoft recommends that you have a clustered index in the table. In addition to improving query performance, clustered indexes can be rebuilt or re-organized on demand to control table fragmentation.

2.In Microsoft SQLwill be removed in future versions of the Serverntext, text  and  image  data type.   Avoid using these data types in new development work, and consider modifying applications that currently use these data types.  nvarchar (Max) , varchar (Max) , and varbinary (Max) instead. " data-source-localized= "0" data-original-title= "" > use  nvarchar (max), varchar (max)  , and  varbinary (max) instead.

3. The clustered index itself is to be sorted, and the GUID (uniqueidentifier type) causes too many page splits when the data is inserted.

　　

Missing index

(figure)

Index in database design is a key to performance, when the database is missing a large number of indexes, it will inevitably lead to poor performance of the database.

No index FOREIGN key

(figure)

• For primary table data operations, such as deletions, you need to find a check in the Foreign key table, which can cause a full table scan if a foreign key index is missing, severely impacting performance.

• When you combine data from related tables in a query, you often use foreign key columns in the join condition by matching the columns in the FOREIGN key constraint of one table with the primary key or unique key columns in the other table. Indexes enable the database engine to quickly find related data in the foreign key table to improve performance.

Index not in use

(figure)

With development and optimization, many people create an index of the database, many times create a better federated index or overwrite the index, will let the original single-column index lost the use of the scene, this part of the index will appear in the long-unused index, it is recommended to delete.

Duplicate index

(figure)

With the development and optimization, many people will create the index of the database, then the page will inevitably produce some similar or identical indexes, the index itself is a maintenance cost, in the update, insert, delete a certain cost, then the duplicate index will only increase this part of the maintenance cost.

Index of Aging

(figure)

Index maintenance is one of the configuration of regular maintenance tasks, as data is constantly written and changed, resulting in a lot of index fragmentation, the lack of index of maintenance tasks, the inability to reorganize index data in a timely manner, resulting in inefficient indexing, or even failure.

Overview of programming structures

1. View the session information and execution plan in the "check"-"Structure design" and the platform prompts for a warning when the design structure does not pass a regular check.

Note: Check the information for the primary session isolation level, whether there is a long session with a transaction, and whether there is an implicit conversion in the execution plan.

　　

Session Information

1. View session information in sessions-idle sessions.

Note: The main concern is that the session is not closed for a long time and a session with a transaction is not closed for a long time. A session with a transaction over a long period of time may be caused by a leaking program connection, which can cause the system card to death serious performance problems due to a lengthy transaction that blocks other sessions from functioning properly.

2. View the session details in the Sessions-Overview page, focusing primarily on the transaction isolation level.

Transaction ISOLATION LEVEL Description: The transaction isolation level primarily controls queries (shared locks), and the higher the isolation level, the worse the concurrency capability. (For more information, see: Platform technical data, optimal configuration)

If a large number of repeatable_read (repeatable reads) or serializable (serializable) are found in the program, check to see if the program needs to use a high level of isolation, which causes blocking waits to increase and the database concurrency capability to degrade. Note: If there are a large number of high level transaction levels in the system, make sure that you need a high isolation level to sacrifice the concurrency capability.

　　

Implicit conversions

Execution plan with implicit conversions: the presence of implicit conversions in a statement results in performance consumption or the use of an index.

Note: Implicit conversions often occur in table design where the field type (varchar) priority level is lower than the type of parameter passed by the program (nvarchar)

View specific information in the execution plan-implicit conversions page.

Summarize

The whole program and database is stable, efficient and structural design is inseparable, in the process of inspection, structural design is a more important part. Most of the time, operators and designers, developers, communication is not smooth, professional skills there is a gap, so structural design often problems.

Easy to master the way of database management--the third of operation and maintenance inspection (structural design)