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 parameters of the instance have an important impact on the performance and stability of the system. The parameters at the system level may change according to the changes of the business, but prevent the setting of the system parameters from unreasonable or accidentally modified. System parameter inspection is also a necessary item in the inspection.

Overview of system parameters

1. Check whether there are any unreasonable parameters on the [check items]-[all] page. There will be a warning when the parameter settings fail the check.

Specific parameter setting

View the specific parameter configuration on the parameters page.

Note: reasonable and unreasonable parameters are derived from the calculation formula of the platform, and personal adjustments according to the characteristics of the system may be included in the unreasonable range (warnings can be ignored in this case).

Parameter description

The impact of unreasonable parameter settings:

Poor parameter settings may cause the system hardware to fail to maximize performance or cause performance problems. For example, if the setting of the maximum memory is too small, the system will not be able to use the memory and cause serious performance problems, otherwise, if it is not set or too large, it will force the release of SQL Server memory when the operating system is under memory pressure, and the SQL Server will not work.

1. Maximum parallelism (max degree of parallelism)

It is generally recommended that the system be set to 8 or 4 if there are more than 32 CPU. If all the statements in the system are very short and frequent, it is recommended to set it to 1 (cancel statement parallelism, be careful and really conform to your scenario).

Note: in many cases, the parallelism setting is related to your server CPU configuration, such as how many channels, cores, and whether it is hyper-threaded. Generally speaking, it is better not to cross the physical CPU.

The setting of parallelism is for the instance level (individual databases can be set in SQL2016)

Microsoft official suggestion: https://support.microsoft.com/zh-cn/kb/329204

two。 Parallel cost threshold (cost threshold for parallelism) SQL Server creates and runs a parallel plan for the same query only if the estimated cost of the serial plan running the same query is higher than the value set in the parallel cost threshold. Overhead refers to the estimated time (in seconds) it takes to run a serial plan in a specific hardware configuration. The parallel cost threshold option can be set to any value between 0 and 32767. The default value is 5.

In some cases, it is possible to choose a parallel plan even if the cost plan of the query is less than the current parallel cost threshold. This occurs because the use of parallel or serial planning is based on the cost estimates provided before completing the full optimization.

Note: it mainly controls when the SQL optimizer chooses the parallel plan. The default value is recommended. The smaller the value is, the easier it is for the optimizer to choose the parallel plan.

3. Server memory (min server memory and max server memory)

Max server memory (recommended setting): it is generally recommended that if the operating system reserves 3G-4G with less memory, if the memory is larger than 512, 5% of the memory is reserved for the operating system when there is no pressure on the database memory, and the rest is reserved for SQL SERVER. If the server has other applications, the memory occupied by the application should be subtracted from the SQL.

Min server memory (server has multiple instance recommended settings): this method is also useful when there are other memory-consuming processes on the computer because it ensures that SQL Server gets at least a reasonable amount of memory. The disadvantage of this approach is that when a new instance (or any other process) is started, the running instance may take some time to free memory, and if the instance must write the modified page back to the database to free memory, it may take longer.

The first instance with a workload usually allocates all memory. Idle instances or instances that start later may end up running with the least amount of available memory. SQL Server does not attempt to allocate memory usage evenly across instances. However, all instances will respond to Windows memory notification signals to resize their memory requirements. Windows does not use memory notification API to balance the memory used by individual applications. It only provides global feedback on the availability of system memory.

Note: maximum server memory controls SQL Server memory allocation, including buffer pools, compiled memory, all caches, QE memory grants, lock manager memory, and CLR memory (virtually any memory allocator found in sys.dm_os_memory_clerks). The memory of the thread stack, memory heap, linked server providers other than SQL Server, and any memory allocated by non-SQL Server DLL are not controlled by the maximum server memory.

Note: SQL Server does not immediately allocate the amount of memory specified in min server memory at startup. However, unless the value of min server memory is lowered, SQL Server cannot free memory when memory usage reaches that value due to client load.

4. Xp_cmdshell enables SQL SERVER to use the cmdshell function to apply

Xp_cmdshell is a peripheral application configuration in the default configuration of the newly installed SQL Server, and the feature is not enabled. To minimize the number of features that may be subject to malicious users.

Note: when some customers use scripts to back up the database, the xp_cmdshell function is enabled, and system procedures such as master.dbo.xp_delete_file can be used instead.

Summary

Poor parameter settings may cause the system hardware to fail to maximize performance or cause performance problems. For example, if the setting of the maximum memory is too small, the system will not be able to use the memory and cause serious performance problems, otherwise, if it is not set or too large, it will force the release of SQL Server memory when the operating system is under memory pressure, and the SQL Server will not work.

It is also an important inspection item to find out the change of system parameter configuration and whether it is reasonable or not in time.

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.