Shulou(Shulou.com)06/01 Report--

Preface

Doing a good job of daily inspection is an important step in database management and maintenance, and it is necessary to register the date and result of each inspection, and a patrol report may be needed at the same time.

This series aims to solve some common problems:

I don't know what to inspect.

I don't know how to get a convenient physical examination.

Too many machines are troublesome for physical examination.

It is difficult to generate a report and cannot present the results visually.

The first two articles have inspected the software and hardware configuration of the server and the general situation of the database. Next, we will check some structural design of the database. These checks are mainly aimed at some unoptimized structural design in the early stage of system development or structural changes caused by the continuous increase of new functions or changes in operation and maintenance of the system.

Overview of physical structure

View the information of each database in [check items]-[all]. When the database structure fails the regular check, the platform will prompt a warning.

Note: the check information mainly includes non-standard tables, missing indexes, unindexed foreign keys, unused indexes, duplicate indexes, and aging indexes.

An irregular watch

Check [Database]-[non-standard tables] to see if there is a non-standard design in the table in the system. These irregular designs mainly include no clustered index, using old data types, and the columns of the clustered index are randomly growing (GUID,uniqueidentifier type).

Note:

1. Microsoft recommends that clustered indexes are recommended in all tables. In addition to improving query performance, clustered indexes can also be rebuilt or reorganized as needed to control table fragmentation.

two。 The ntext, text, and p_w_picpath data types will be removed in future versions of Microsoft SQL Server. Avoid using these data types in new development work, and consider modifying applications that currently use these data types. Use nvarchar (max), varchar (max), and varbinary (max) instead.

3. The clustered index itself is sorted, and GUID (type uniqueidentifier) inserts data in this way resulting in too many page splits.

Missing index

(pictured)

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

Indexed foreign key

(pictured)

When operating on the data of the master table (such as deletion), you need to look for a check in the foreign key table. The lack of a foreign key index may lead to a full table scan and seriously affect performance.

When combining data from related tables in a query, foreign key columns are often used in join conditions by matching columns in one table's FOREIGN KEY constraint to primary or unique key columns in another table. Indexing enables the database engine to quickly find relevant data in foreign key tables and improve performance.

Unused index

(pictured)

With the progress of development and optimization, many people will create indexes on the database. In many cases, a better federated index or overlay index will be created, which will make the original single-column index lose its use. This part of the index will appear in the index that has not been used for a long time, and it is recommended to delete it.

Duplicate index

(pictured)

With the progress of development and optimization, many people will create indexes on the database, then the page will inevitably produce some indexes with similar functions or the same functions, and the index itself also has maintenance costs. There will be some overhead when updating, inserting and deleting, then repeated indexes will only increase this part of the maintenance overhead.

Aging index

(pictured)

Index maintenance is one of the routine maintenance tasks of configuration. with the continuous writing and change of data, it will produce a large number of index fragments, lack of index for maintenance tasks, and can not reorganize index data in time, resulting in index inefficiency or even failure.

Overview of programming structure

1. View the session information and execution plan in [check item]-[structural Design]. When the design structure fails the general check, the platform will prompt a warning.

Note: check the primary session isolation level of the information, whether there is a long session with transactions, and whether there is an implicit conversion in the execution plan.

Conversation information

1. View the session information in [session]-[idle session].

Note: the main concerns are sessions that have not been closed for a long time and sessions that have not been closed for a long time with transactions. Long-term sessions with transactions may be caused by program connection leakage, long-term transactions will block the normal progress of other sessions, resulting in serious performance problems such as system jam.

two。 View the session details on the "session"-"Overview" page, focusing on the transaction isolation level.

Brief description of transaction isolation level: transaction isolation level mainly controls queries (shared locks). The higher the isolation level, the worse the concurrency ability. (for more information, please see: platform technical materials, best configuration)

If you find a large number of REPEATABLE_READ (readable) or SERIALIZABLE (serializable) in your program, check whether it is necessary for the program to use a high level of isolation, resulting in increased blocking waits and reduced database concurrency.

Note: if there are a large number of high-level transaction levels in the system, make sure that a high isolation level is required at the expense of concurrency.

Implicit conversion

There is an execution plan for implicit conversions: implicit conversions in statements cause performance consumption, or indexes cannot be used.

Note: implicit conversion often occurs when the field type of the table design (varchar) has a priority lower than the parameter type passed by the program (nvarchar).

View the specific information on the [execution Plan]-[implicit conversion] page.

Summary

The stability and efficiency of the whole program and database are closely related to the quality of structural design, which is an important part of structural design in the process of inspection. In many cases, the communication between operation and maintenance personnel and designers and developers is not smooth, and there is a gap in professional skills, so structural design often has problems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.