"Competency Enhancement" SQL Server FAQs and high-speed solution recommendations

Objective

This article is intended to help users of SQL Server databases understand common problems. and high-speed solutions to these problems. These issues are a general management problem for the database and provide an approximate FAQ framework for many friends who do not have a deep understanding of the database.

Some of the following issues are common problems that are found in nearly thousands of database user diagnoses. This article is divided into "common problems diagnosis process"-"Frequently asked questions"-"frequently asked questions high resolution recommendations"

FAQ Diagnostic Process Overview Module [summary] Understanding the system

1. Understanding System Performance (statement run time, session wait)

Statement run time: The horizontal axis is the time range, and the ordinate is the number of statements that are distributed within the run time.

(This example: there are 1103 statements in the collection time that have a statement running at 3-5 seconds.) 5-10 seconds, 10-20 seconds the number of slow statements is very large. Indicates that the system statement is running slowly and the system has a performance problem)

Session-Wait type: The horizontal axis is the wait type, and the ordinate is the waiting quantity.

(This example: there is a lot of waiting in the system, indicating that the system has a performance problem)

1. Understanding System Execution Metrics (CPU, memory, disk counters)

Diagnose the system for bottlenecks and resource bottlenecks with 3 main counters.

1. Understanding System Request pressure (batch request/second, number of connections)

Understand the number of requests per second in the system and the number of connections opened by the system, and master the system pressure levels.

(Note: Can collect the analysis multiple times, master the system pressure fluctuation and processing ability)

1. Understanding System problem Severity (check items, SQL error log types)

Understand potential risks and defects in the system

(Red: High severity, recommended problem all resolved)

[Check] module-[all] found system problem

From the system, parameters, sessions, performance counters, query statements, jobs, logs, run plans, tempdb, business database in many aspects of the system present problems and hidden dangers.

(Green: Configuration Criteria, Blue: low severity.) Orange: Medium severity, Red: high degree of severity, may pose a risk)

Problems

Performance issues

1. The performance parameters are not configured properly: the hardware does not perform the maximum performance
2. System wait Critical: Causes statement execution to be slow
3. More statements executed than 3 seconds: poor Customer experience
4. A missing index condition exists: Causes the statement to execute slowly. Consume too much system resources

Environmental issues

1. Disk space Planning issues: insufficient disk space or inability to meet future business
2. The patch is not up-to-date: Due to Microsoft version number defects, some features are abnormal.

Backup issues

1. Backup missing: Failure to occur causes all data loss
2. Unreasonable backup schedule: cause performance issues or risk of long-time data loss
3. Backup file with data file: disk has problems that cause data to be lost or corrupted with backup

Usability issues

1. Database single point risk: A problem can lead to business disruption. Data loss due to failure

Security issues

1. Missing data consistency check (CHECKDB): failed to detect data corruption in time and finally caused database failure and data loss
2. Large number of logon failures: Check for system attacks or program changes resulting in a large number of logon failures

Structural Design Issues

1. More than 10 minutes of sessions with transactions: a long time exists without committing the transaction. Causes the program to become clogged. Check if the application has a connection leak
2. There is an implicit conversion: causes the index not to be used (example: table field definition varchar, program pass type nvarchar)
3. Table missing clustered index: causing performance issues
4. Foreign key missing index on table: causing performance issues

High-speed resolution of this problem with general recommendations for high-speed resolution of performance issues configuring system parameters

View an unreasonable number of references-click the action button

On the pop-up page, view the current execution value, with the suggested value.

Popup Optimization Configuration Script-if you want to change the copy text-you can change it in server execution

Add Missing Index

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

Note: Indexes that are not all hints need to be created. Based on the "average percentage of impact", "average user cost percentage", "User lookup times" situation comprehensive evaluation, to establish the missing important index in the system. and synthesize equal columns, unequal columns, including columns, to create an optimal index that includes multiple scenarios.

Analysis of key statements based on operating frequency and overhead

In query statement-Summary view of the query statement module. Focus on the key statements, such as "number of runs" or "CPU Time", "number of reads", "Number of rows affected".

Click the statement to enter the Category view. Statement-specific Run Information page

On the Category View page. Be able to click on the statement to enter "related items" to see the details of each statement run and run the plan, wait for information.

Recommended disk planning for environmental issues

1. Divided by file type: Data files, log files, tempdb files, backup files, respectively, on one physical disk (4 physical disks)
2. According to the database: different business databases (high pressure) are placed on one physical disk, tempdb and the backup file each one physical disk. (Number of large business libraries +2)

Operating system and version number

1. 64-bit operating system and SQL Server software recommended
2. Recommended SQL Server patches for the latest patches

Recommendations for backup issues

1. Small and Medium database backup scheme: Daily full backup, one hour log backup.

2. Large database backup scenario: Every comprehensive backup, daily differential backup, one-hour log backup.

3. Backup files and data files placed on different physical disks
4. Backup file copy offsite (Disaster recovery)

Usability recommendations

Regardless of the database in order to ensure business continuity and high availability, as well as the security of data, it is necessary to adopt high-availability scenarios to avoid the single point of risk.

High-availability technology for database mainstream: Always on availability groups. SQL failover clustering, log shipping technology, mirroring technology. and Moebius load Balancing cluster

Security issues recommended account security and Rights management

In general, we are all set through SA password, and in the config file in plain text written on our account and password, this design has a very large security implications. Whether the attack or accidental deletion will bring serious consequences for the system, it is recommended to the user for permission planning, account division and other security measures.

maintenance tasks checkdb ensure data security

Contact hundreds of customers due to the failure of timely detection of data page corruption caused by the database is not available or data loss, we recommend the weekly execution of timely detection of database corruption.

Recommended clustered index and foreign key index for structural design

In the table structure design, there are clustered indexes in the proposed table. The foreign key is added to the index. To improve performance.

Implicit conversions

The field type does not match when the database physical design and the program call design (the type conversion priority passed by the program is higher than the field type in the database.) Implicit conversions occur when a table is varchar and the program passes in nvarchar, and implicit conversions Add database performance consumption, which can cause serious performance problems if the index is not available.

By finding the corresponding implicit conversion in the "Run Plan" of the tool statement, the analysis program is integrated with the database table design.

　　

"Competency Enhancement" SQL Server FAQs and high-speed solution recommendations