Fourth chapter--SQLSERVER2008-2012 Resources and performance monitoring (3)

Original: Fourth chapter--SQLSERVER2008-2012 Resources and performance monitoring (3)

This article is the last chapter in this series to monitor memory usage. Monitoring the memory of the server is a very important thing, and there are many situations that can cause memory consumption. So check them regularly.

This article uses reliability and Performance Monitor to obtain memory-related statistics.

Preparatory work:

Before you begin, let's look at the counters that will be used:

? Memory:available Mbytes: Provides the amount of available memory on the system.

? Memory:pages/sec: Shows how many pages are used to read or write to the hard disk, which are based on hard page faults.

? Paging file:%usage: Displays the percentage of total suspended.

? SQL server:buffer Manager:buffer Cache Hit Ratio: Returns the percentage of data that SQL Server returns from the cache but not from the hard disk.

? SQL Server:buffer manager:page Life expectancy: shows the average number of seconds that data resides in memory

? SQL server:buffer manager:memory Grants Pending: Number of processes to wait for the memory workspace to be granted .

Steps:

1. turn on reliability and Performance Monitor and enter Perfmon.exe in Run

2. Select Performance Monitor.

3. removes all existing counters.

4. add a new counter.

5. Select the server that you want to monitor.

6. Select the following counters:

memory:available Mbytes
memory:pages/sec
paging File:%usage
sql server:buffer Manager:buffer Cache Hit
sql Server:buffer manager:page Life expectancy
sql server:memory manager:memory Grants Pending

7. then click OK.

The above steps are already described in the previous chapter. It's not a liability here.

Analysis:

in this article, the tool for reliability and Performance Monitor is used again. In order to obtain memory-related performance counters, these counters need to be observed in a graphical interface.

First check the memory:available Mbytes, which means the system's available memory. If you find that this value is often low, it may indicate that the server is out of memory, and in the production database, this value can be used in gigabytes.

Then check the memory:pages/sec to think that this is due to a hard page error caused by the read or write page from disk. If this value is longer than 20, it means that the application uses virtual memory, resulting in a hang.

Next is memory:pages/sec, and also check paging file:%usage to estimate memory hangs. If this value often exceeds 20%, it may mean that there is not enough memory.

SQL server:buffer Manager:buffer Cache Hit ratio: means the number of times the data is read from the cache, with a reasonable value of more than 90%. If the value is low, you may be out of memory or need to check indexes and queries. If you need to get a lot of data, this step can consume a lot of memory and cause SQL Server to read data from disk instead of memory. Check the index to make sure that you can scan as many strokes as possible in the large table. and limit the result rows returned by the query whenever possible.

Check the SQL Server:buffer manager:page life expectancy, which represents the number of seconds the data page resides in memory. Microsoft recommends a minimum of 300 seconds. If it is often less than 300 seconds in an instance, it means that the data is retained for less than 5 minutes to be moved out of memory.

If SQL Server:memory Manager:memory Grants pending often recommends waiting for a process, you may need to increase the server's memory.

For whatever reason, if you find that memory is out of date and hangs more frequently, you should first check if there are other applications or services that are not SQL Server that consume more memory than SQL Server. If you find these apps or services, try moving to another server. If you do not, you need to add more memory for SQL Server to use.

If the server is only working with SQL Server and does not have the above mentioned situation, analyze your queries and indexes to ensure that they are optimized. If you have optimized, there are still these problems, then you need to consider increasing memory.

In addition to reliability and Performance Monitor, you can also use SQL Server Profiler to monitor performance, create a user-defined collector to coexist as a file, and when you get performance data from Performance Monitor, SQL Server Profiler runs synchronously. Once you have completed the collection, you can import performance data into SQL Server Profiler for analysis at any time.

Fourth chapter--SQLSERVER2008-2012 Resources and performance monitoring (3)