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

Preface

The purpose of this article is to help users of SQL Server databases understand common problems and solve them quickly. These problems are routine database management problems, and provide a general framework of frequently asked questions for many friends who do not have an in-depth understanding of the database.

Some of the following problems are routine problems found during the diagnosis of nearly a thousand database users. This article is divided into [FAQ diagnosis process]-[FAQ]-[suggestions for rapid resolution of FAQ]

FAQ diagnosis process overview module-[summary] understand the system

Understand system performance (statement execution time, session wait)

Statement execution time: the Abscissa is the time range, and the ordinate is the number of statements distributed within the range of execution time.

(this example: there are 1103 statements with execution time of 3-5 seconds in collection time, and a large number of slow sentences in 5-10 seconds and 10-20 seconds, indicating that the execution of system statements is slow and the system has performance problems.)

Session-wait type: the Abscissa is the wait type and the ordinate is the number of waits.

(this example: a large number of waits occur in the system, indicating that there is a performance problem in the system)

Understand system performance metrics (CPU, memory, disk counters)

Three main counters are used to diagnose whether the system has bottlenecks and the resources that cause bottlenecks.

Understand system request pressure (batch requests / per second, connections)

Understand the number of statements requested per second in the system and the number of connections opened by the system, and master the level of system pressure.

(note: it can be collected and analyzed many times to master the pressure fluctuation and handling capacity of the system)

Understand the severity of system problems (check items, SQL error log types)

Understand the potential risks and defects in the system

(red: high severity, it is recommended that all problems be solved)

[check item] module-[all] found system problems

This paper presents the problems and hidden dangers of the system from the aspects of system, parameters, session, performance counter, query statement, job, log, execution plan, tempdb and business database.

(green: configuration standard, blue: low severity, orange: medium severity, red: high severity, may cause risk)

common problem

Performance problem

Unreasonable configuration of performance parameters: hardware cannot maximize performance

The system waits heavily: causes the statement to run slowly

Large number of statements executed for more than 3 seconds: poor customer experience

Missing index condition: causes the statement to run slowly and consumes too many system resources

Environmental problems

Disk space planning problem: insufficient disk space or unable to meet future business

The patch is not up-to-date: some of the functions are abnormal due to defects in the Microsoft version.

Backup issu

Missing backup: failure can result in loss of all data

Unreasonable backup schedule: leading to performance problems or risk of prolonged data loss

Backup file and data file on the same disk: disk failure, resulting in data loss or corruption along with backup

Availability issu

Database single point risk: failure will lead to business interruption, failure leads to data loss

Safety problem

Lack of data consistency check (checkdb): failure to detect data corruption in time, resulting in database failure and data loss

A large number of login failures: check whether the system has been affected by * or if program modifications have caused a large number of login failures

Structural design problem

More than 10-minute session with transactions: uncommitted transactions for a long time will cause the program to block and check if the application has connection leaks

Implicit conversion exists: causing the index not to be used (for example: table field definition varchar, program parameter passing type nvarchar)

Table missing clustered index: causing performance problems

Missing index of foreign keys on the table: causing performance problems

Quick problem solving and general recommendations to quickly solve performance problems configure system parameters

View unreasonable parameters-Click the Action button

View the current running values and suggested values on the pop-up page.

Pop-up optimization configuration script-if you need to modify the replication text-you can modify it when running on the server

Add missing index

In the missing Index of the Database module, create a missing index for the database.

Note: not all prompted indexes need to be created. According to the comprehensive evaluation of [average percentage of impact] [percentage of average user overhead] [number of user searches], important indexes missing in the system are established. And synthesize [equal column] [unequal column] [include column] to create an optimal index containing multiple cases.

Analyze key statements according to execution frequency and cost

In the "query statement"-"Summary View" of the [query statement] module, focus on optimizing key statements such as [execution times] or [cpu time] [read times] [number of writes] [number of rows affected], etc.

Click on the statement to enter the "Classification View", the detailed execution information page of the statement.

On the "Category View" page, you can click the statement to enter the "related items" to view the specific information of the execution of each statement and the execution plan, waiting and other information.

Recommended disk planning for environmental problems

Divided by file type: data files, log files, tempDB files, backup files, each on one physical disk (4 physical disks)

Divided by database: different business databases (stressful ones) are placed on a physical disk, tempDB and backup files each have a physical disk. (number of large business libraries + 2)

Operating system and version

64-bit operating system and SQL SERVER software are recommended

It is recommended that the SQL SERVER patch be the latest patch

Suggestions for backup issu

Backup scheme for small and medium-sized databases: full backup every day and log backup once an hour.

Large database backup scheme: weekly full backup, daily differential backup, hourly log backup.

Backup files and data files are placed on different physical disks

Copy backup files in different places (disaster recovery)

Availability recommendation

In order to ensure business continuity and high availability, as well as the security of data, any database must adopt a high availability scheme to avoid the risk of a single point.

Mainstream high availability technologies for databases: Always On availability groups, SQL failover clusters, log transfer technology, mirroring technology, and moebius load balancing clusters

Security issues suggest account security and authority management

Usually, we set the password through sa, and clearly write our account number and password in the config file. There are great security risks in this design. Whether it is caused by * * or mistakenly deleted, it will bring serious consequences to the system. It is recommended to take security measures such as user permission planning and account division.

Maintenance task CHECKDB to ensure data security

Contact with hundreds of customers due to not timely detection of data page damage caused by database unavailable or data loss, it is recommended to run every week to find database damage in time.

Suggestions for structural Design clustered Index and Foreign key Index

In the table structure design, it is recommended that all tables have clustered indexes, and foreign keys add indexes to improve performance.

Implicit conversion

During the physical design of the database and the call design of the program, the field types do not match (the type conversion priority of the program is higher than that of the field types in the database, such as varchar in the table and nvarchar in the program), implicit conversion will occur, which will increase the performance consumption of the database and make the index unusable, which will lead to serious performance problems.

The corresponding implicit conversion is found in the execution plan of the tool statement, and the input of the analysis program and the design of the database table are solved comprehensively.

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.