SQL Server Database optimization Combat (i)

Preface: Always want to write some about the SQL Server database optimization article, but between my ability limited, has not dared to swim.

Now, want to put a few chapters have been organized on the blog and share with you, and June mutual encouragement.

Analyze the problem:

for optimization, finding the problem accurately is the most important point. the next few chapters will focus on how to pinpoint problems and quickly propose the most effective solutions.

There are a lot of ways to get the key points of the problem, although you have plenty of hands, but the most direct is to listen to the needs of customers or advocates, and detailed inquiry requirements.

For example, one query is slow, one operation is slow, and, of course, the higher end is telling you directly which statement is slow (in general, we can make sure that the statement is our colleague). the most immediate is the customer site colleagues in the maintenance of software or development software found in the statement problems, for the after-sales colleagues, to find the key to the problem is a very time-consuming and laborious thing. Therefore, this chapter introduces some quick ways to find the key points of the problem.

Hardware configuration:

Insufficient hardware configuration, is one of the main problems of some old customers; many customers may have used the software as early as 4 or 5 years ago, and with the gradual development of enterprises, the business has been expanding. But the server is still 4, 5 years ago configuration, resulting in the phenomenon of small horse cart. So, looking at the hardware configuration is a very simple thing. Here's how it works:

Right-click My Computer---Properties

When opened, we can see:

1, view the system, generally speaking, the server will be Windows Server 2008, the earlier year will be Windows Server 2003.

2. View Processor (CPU)

In general, the server-specific CPU will be used, (more than 100 stores) the higher configuration is 8 cores of CPU installation 2-4, the general will be 6 core CPU installed 2-4. Of course, these configurations are also a rough value, if more accurate calculation, will consider the number of customer stores, store daily sales, the number of headquarters use, and many other factors.

3, view memory, Microsoft recommended 1core corresponding to 2-4g memory.

4, the system type, in general, the server will choose 64-bit operating system. Because the 32-bit operating system supports a maximum memory of only 4G, usually less than 4G, memory is known to be 3.5-3.75g or so.

Cpu:

Check the CPU usage.

Start Task Manager,

CPU usage is more than 80%, and the server is very dangerous if it does not decrease continuously.

Review the process, sort by CPU, and see which apps take up the CPU.

There are generally 3 scenarios:

1. Non-SQL Server application consumes CPU

View the app, whether it's a system app, or not, you can end the process and see if the process is enabled or turned on in the startup item.

Run Msconfig:

2. SQL Server consumes high CPU

If SQL Server occupies a higher CPU, it typically occurs when a database job is running, or there is an unreasonable SQL statement.

3. No application is found to occupy a higher CPU

It is generally poisoned ... Recommended to Avira virus.

Memory

View memory usage.

In general, memory usage is more than 90%, which affects the operation of the system.

The primary memory-hogging typically is SQL Server.

The solution is to set the maximum memory for SQL Server.

Right-click Database Service-Properties

Sets the maximum server memory.

For memory already occupied, the system will not be automatically reduced, in order to reduce the memory usage, need to manually set up a small memory, (in order to reduce the memory usage in the Task Manager) and other memory utilization down, then set the server's maximum memory. The general principle of setting is to leave 4G of memory for the system, and the rest to be allocated to SQL Server. (The server has no other application)

SQL Server Database optimization Combat (i)