SQL Server performance tuning through memory and CPU

The memory of SQL Server also affects performance. If too much memory is in the SQL server system, it is a waste of money. If the memory is too small, it is very harmful to performance. Unfortunately, it is very flexible to decide when you need more memory in the system. When memory is faulty, you will find that disk I/O increases, and disk queues also increase. You will also find that the buffer cache hit ratio is reduced and the page life is extended. As the memory demand increases, you will begin to find the error information in the log file.

An important part of SQL Server Memory has been separated, resulting in performance degradation. Duration: % n seconds, Working Group (Kb): % W, committed (Kb): % C, memory utilization: % u.

SQL Server encountered a % O Io request event. It took more than 15 seconds to complete the event in the [% F] file in the database [% d] (% I. OS file processing is % H. The latest Io length of the Offset is % L ..

But this is not the only time these errors are reported, so you need to use it with the performance monitor metric to determine whether the memory is really too low.

There are also some solutions to solve the SQL server memory problem. The simplest solution is to increase the server memory and increase the number of available buffer caches. This increases the amount of memory data and reduces your disk I/O. Other solutions include migrating cluster indexes for large-space tables and using only non-cluster indexes for these tables, including the primary key.

The cluster index is used for searching and the Cluster Index seeks is used. If another index is used, it cannot reduce any memory pressure, because the clustered index is not in the memory. If cluster-based index scans is used, a new non-cluster index may be useful without migrating the index.

　　How to monitor CPU pair columns (CPU queuing)

CPU is another part of hardware, which can lead to potential performance problems. Most people only view the speed or number of CPUs. However, like a disk, CPU can become a bottleneck. If a CPU bottleneck occurs, you may not check the CPU performance by 100%. The CPU has the same command-to-column method as the I/O-to-column method. The command is downloaded to the CPU queue, and the previous operating program is executed to wait for the CPU to become available. As the CPU speed becomes faster, the speed of doing anything in the CPU is also faster, but we can only do the same thing at once. Now we can use dual-core, three-core, quad-core, and multi-core CPUs. In this way, we can execute more commands at a time.

You can use the SQL server performance monitor to monitor your CPU. You will find perfmon under the system target, which has a calculator named "processor queue length ". Almost any other queue length greater than zero indicates that you need to add some operation programs, which can be executed by SQL server at the same time. However, this does not indicate that a faster CPU is needed, but a more multi-core CPU is needed. Today, the latest server supports 32 cores for each server, and some advanced servers support 64 cores (when Chase supports 64 cores in a proportional range) can also be created (only some vendors ).

In part 1 and part 2, I have pointed out some parts of the hardware. These skills are not the most comprehensive and final solutions to performance problems. Table Design and index adjustment are often and are very important for a long time. Today's SQL Server is expected to do more in a longer period of time, so that hardware adjustment is more important to the database platform. Use some tools in Arsenal to solve performance problems, so that you can upgrade each of the existing hardware performance to the minimum extent you have not yet done. But when you do need to buy, these skills help you make the right decisions and make things worthwhile.