Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to solve the stutter problem caused by improper memory allocation on the SQL Server server in the production environment. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Overview

Recently, a database was migrated, and after a period of time, the operation and maintenance staff found that the server was so stuck that it would take a long time to log in remotely. The solution process is recorded below.

1. Check the resources

You can find that the memory is full, and the top one is the sqlserver process.

2. View sqlserver memory allocation

SQL Server's strategy for using server memory is to use as much memory as it uses, and only when the server is out of memory will it release a little bit of memory, so SQL Server servers tend to take up a lot of memory.

You can see that the memory allocated before the migration is 10 GB, but the migrated server has only 8 GB of memory.

3. Adjust sqlserver memory

Here, you can change the maximum server memory to 1G, determine that the memory will be forced to release, and then change the maximum server memory to the appropriate value.

4. View memory allocation

Normally, only starting the sqlserver server consumes 2.34g of memory, opening the F5 client to 3.1g, that is, consuming 0.7g of memory, and finally opening the client that downloads the data, the downloaded data should be cached in the sqlserver memory, so the memory has been increasing. Currently stable at 5.17 gigabytes of memory, that is, the download consumes 2.2 gigabytes of memory.

5. View the current memory status:

DBCC MemoryStatus

This memory is typically used as a cache by the Sql Server runtime:

1) data cache: execute a query statement, and Sql Server will load the relevant data pages (the data of Sql Server operations are all in pages) into memory. Next time, if the data of this page is requested again, there is no need to read the disk, which greatly improves the speed.

2) execute command cache: when executing a stored procedure and a custom function, Sql Server needs to be compiled in binary and then run, and the compiled results will be cached, so there is no need to compile again when called again.

The above is how to solve the stutter problem caused by improper memory allocation of the SQL Server server in the production environment. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.