SQL Server memory encounters operating system process crush case

From Click on Open link

Scene:

A recent DB server occasional CPU alarm, my email alarm threshold (Please read yù) value set is 15%, the beginning is not the case, thinking is what the statistical category of inquiries, and then more frequent.

Explore:

I decided to check to see what was going on, and the Order of my troubleshooting was as follows:

1, first turn on cacti monitoring, found that the most recent CPU average after a day suddenly rise, and can see Systemprocessor Queue Length and Sqlservr%processortime also in significant changes.

2, starting from the most easy to start inefficient SQL, consider what is the latest business changes? Connect to the SQL instance, open the Activity Monitor, expand the "most recent resource-intensive query," and reverse CPU time, where no immediate resource-intensive queries are found. According to personal experience, here, if the value is 4 digits, 3 digits in minutes, the general server CPU is probably more than 10%, if the CPU time there is 5 digits, and the number of minutes in the execution is also very high, hundreds of times, the CPU will generally not calm. Pictures for demo only

3, no resource-consuming SQL, this is the most reluctant DBA to see the results, because perhaps SQL Server from internal or external pressure, so that they spend too much time to deal with the operating system communication. The performance problems of SQL Server's common query-inefficient classes, most of which come from memory or hard disks, sometimes require a parallel baseline to determine who is the cause and who is the fruit. Here, we first look at SQL Server memory usage, and when I turn on the performance counters, my friends and I are stunned ... With a database with 64G of memory installed, SQL Server has a targetmemory of more than 500 megabytes! This stolenpage also occupies more than 200 megabytes, the database DataPage only has 200 megabytes of memory to use, oh,shit! although I do not want to use " Where did you go? "These three words, but where did my memory go?" We also note that the Pagelifeexpectancy value is only 26 (a memory-rich server, which should at least be on the W), and very early on we relish the "Cache Hit ration" But still maintain a relatively high level of 98! This case tells us that the cache hit rate performance counter is often not a problem.

4, OK, in this case, who occupies the memory that belongs to my dear SQL Server? We continue, open wiindows task management, select the Process tab, click to show all the user processes, found that svchost.exe occupy the vast majority of 60G memory!

5. What is that svchost.exe? We use the Processmonitor tool below, and automatically load all the wiindows processes after it is turned on, sorted by memory, the mouse moves to the Svchost.exe process and displays as remote Registry Service.

6, find here, things have been a certain way, this is mostly Windows memory leak bug, hence Google keywords: Windows Server 2008 R2 Remote Registry memory leak

Sure enough: Assume that your query performance counters on a sqlremote computer by using a application on a computer this is running W indows 7 or Windows Server 2008 R2. In this situation, the memory usage to the Remote Registry service on the local computer increases the until Memory is exhausted.

Workaround:

1, reboot the server, install hotfix

2, because the restart of the server will affect the business, so I am thinking of restarting the RemoteRegistry service, should also be able to temporarily solve the problem, this bug should be in a certain fixed situation occurred.

Then, at the right time, I restarted the service, the SQL Server targetmemory back to more than 60 g,cpu also normal, so far the problem has not occurred.

Follow-up follow up:

The work of a DBA is hard to say, it's easy, it's easy to find a problem, it's not enough to solve the problem, we have to be aware of our shortcomings, in this case, I didn't have the SQL Server memory monitored, so I didn't find the severity of the illness in the first place, but the server didn't take on the important business, Otherwise disastrous consequences, perhaps early collapse, fear is that, if the crash, nature to restart the server, to that time, we even the first scene did not, when leader asked, I should be hard scratching my head.

After this event, I set up the SQL Server memory monitoring, 1 days later, I new monitoring data, and found a server has the same problem! I am glad that the server is not down, but thankfully I did the right thing.

With a memory monitor, you can see that after the service restart, the total pages of SQL Server has been rising, and gradually stabilized, Page life expectancy also become more and more large, the CPU can also indicate that the disease has been eliminated, I am pleased.

Summarize:

The server before performance problems, most of the early signs, especially memory leaks, because the memory is a little bit squeezed, and finally reached a limit, SQL Server will suddenly crash off, and then only left you a dump, Microsoft smiled. Experienced doctors should see some clues from the daily backache, then further analysis, predict the occurrence of major diseases in advance, this is the value of DBA. This case, tell me, pay attention to the details of the server abnormal changes in order to do in the bud.