Expert Diagnostic Optimization Series------------------not enough memory?

Many users are now plagued by slow database problems and are struggling to pay for a professional DBA that is too expensive. Software maintenance personnel on the database is not so deep understanding, so that the problem can not be resolved, or can only be temporarily resolved can not be cured. Developers to solve data problems are basically searching Baidu various methods to try again, may miss the best time to diagnose problems and may try a bunch of methods at last helpless give up.

How to let the trivial of the program maintenance personnel, the fastest way to solve the problem of the database? How to minimize the pain of our programmers ... Drink tea every day to see the news safe through the day? This series of important through the expert for SQL Server tools to explain the various problems encountered in the database and the root causes of such problems, so that the location of the problem more accurate, the problem-solving ideas clearer!!

The performance of the database is good or bad, for the end-user performance is the click of the operation can respond quickly, then the response to the database is the statement execution time is short enough!

The performance of the database using operation and maintenance personnel, the simple may be seen as CPU, memory, disk three major indicators are normal, the previous article on the basic CPU diagnosis

Expert Diagnostic Optimization Series------------------is your CPU high?

In this article we'll start with memory and see what the memory can see!

No more nonsense, just open the whole-----------------------------------------------------------------------------------------.

First of all to explain a misunderstanding, you are shocked by such a picture?

　　　　

　　　　My server memory is full, this is the cause of my database slow!　　My program error is also because of this, everything because the memory is full!! Hurry up and add the memory.

　　　　The answer is uppercase "not necessarily ", SQL Server is a very fond of memory of the guy, he will cache your data, execution plan, connection information and so on, so this phenomenon is very normal, not easy to jump to conclusions, unless you after careful research and analysis!

So how to analyze whether it is not enough memory problems caused? Let's talk about it!

The main use of performance counters (do not know what is the performance counter, please self-Baidu)

1. Page life expectancy (Database counter: The primary display of pages that are not used, the number of seconds to stay in the cache)
2. Lazy Writes/sec (Database counter: Lazy writer fires when memory is stressed and has new memory requirements, batch flushes "aged buffers")
3. Page reads/sec,page writes/sec (use database-level counters here: When a page that needs to be read or written is not in memory, it needs to be read to disk count)
4. Target Server Memory (KB) (total amount of RAM that SQL Server can use)
5. Total Server Memory (KB) (the amount of RAM used by SQL Server, which is the size of the buffer pool)
6. Available MBytes (System factor: Main display system how much memory is available)

Note: Target Server Memory (KB)-Total Server Memory (KB) is approximately equal to the number of memories that SQL Server can use .

Available MBytes mainly shows how much free memory is in the system (if this value is large, and SQL Server can use 0 or smaller memory, you can adjust the max server memory appropriately (Max Ram, described later))

　　　　

Here no longer detail these three counters, we mainly through the first three counts of linkage to determine whether the system's memory is really pressure!!!

First of all, how are these three counters linked?

Concept departure: Page life expectancy the number of seconds that pages in the cache are not being used, if low indicates memory pressure

Page reads/sec The data to be read does not need to be physically read in memory

Lazy writes/sec memory pressure when batch flushes the aging buffer

When an operation requires a large amount of data to be read, and the data page is not in the cache--"a lot of reading cold data from disk into the cache (page reads/sec)--" When the cache has significant pressure, the lazy writes/sec will trigger (lazy writes/ SEC up), a large number of flash caches of aging data or cache plans-the data is cleared out of cache, and the page life cycle drops

　　　　

Page reads/sec

　　　　

Lazy writes/sec

　　　　

Page Life expectancy

　　　　

High-energy WARNING: When you see your counter is like this, you give the conclusion should not be just, I have a memory pressure!

This example is not only to show that the three counters are linkage, but also can see the law, that is, every three hours a significant memory pressure. As the first CPU article introduction, this regularity of appearance, as a system maintenance personnel, must carefully think about what operation caused the problem? Don't slow down the whole system because of a simple configuration problem!

I through the problem point-in-time statement analysis found that the system every three hours log backup, normal log backup does not cause such a phenomenon, but if the log backup with CHECKDB?

This is what is said don't affect the whole system because of a small mistake!

--------------------------------------------------------------------------------------------

The appearance of low system memory

The following shows a memory pressure server with the appearance of these three counters:

　　　　Page reads/sec

　　　　

　　　　Lazy writes/sec

　　　　

　　　　Page Life expectancy lifecycle

　　　　

　　　　　

These counters reflect the problem is definitely a serious lack of system memory, counter double high low. So what should we do when we know that the system is running out of memory? Add memory?

Don't worry, let's talk about how to make your system save memory, perhaps done this round of optimization, your system memory is enough! You heard wrong, is-----optimization!

Optimize-----Make your memory stress-free

　　　　You're going to set the maximum memory for your system max server memories　　

　　　　

　　　　Q: My system memory is not enough why do I have to set the usage limit? I have this server to the database with the use of settings?

　　　A: The database is the application running on Windows, he and Notepad for the operating system essentially no difference, then this is like June (operating system) and Minister (database) relationship.

and SQL Server is a very like the memory of the application, so it is likely to eat a lot of memory cause Windows system does not have enough memory to use, then the monarch relationship is the most vividly, June (Windows) (SQL SERVER) to die (free memory) the minister had to die ... This release to a certain extent is not enough for Windows alone, it is likely to cause a steep drop in SQL memory, so that SQL for a short period of animation (Operation unresponsive). So for your database stability, this maximum limit must be set.

Recommended Memory settings:

　　　　Generally I recommend operating system reserved 3G-4G, left to SQL Server, if the server has other applications also to reduce the memory of the application in SQL.

If the memory is small and the memory pressure of the database is large, you can reserve memory for the system with the result of the available MBytes as described earlier.

　　　　

　　　　Note: The maximum memory setting is in MB

Statement optimization, so that the statement consumes less memory!

Statement Optimization Series please follow the next article, here only for reduced memory

Reducing memory pair statement optimization is mainly focused on several aspects:

1. Is the index missing?
2. Whether operations that consume memory can be eliminated (such as sorting)
3. Reduce the complexity of the statement so that the optimizer can choose the best plan

Statement consumption of memory is mainly reflected in a large number of reads, or a sort of operations. Confined to the space here only to do simple examples, detailed statement optimization please follow the article.

The so-called read, write simple understanding is the number of data pages needed to execute the statement, the more you need, the greater the memory to cache the data pages. If you need a page that is not in memory, you also need to read from disk ( disk read is why page reads/sec is high )

　　　　

Simple one index reduction example of logical reading ~

　　　　

　　　　

　　　　

The statement uses an entire table scan schedule, executes for 19 seconds, reads logic 143,800 times, reads 137236 (on disk), consumes 40KB of memory, and explicitly prompts for a missing index!

Then we add a hint to the missing index and execute it again

　　　　

　　　　

The indexed statements are reduced to 13 times less than 1 seconds of logical reads, and memory consumption is negligible. This is the importance of the index to the statement! A single statement so, how many of these statements are there in your system?

　　　　

Let's look at an example of how to modify the wording:

　　　　

　　　　

　　　　

Just a simple change of the wording of the sentence time has 7 seconds into 1 seconds, memory consumption from 300+MB to 1MB

　　　　

These two examples tell us that there may be some simple adjustments in the system, the memory pressure will be significantly reduced or become very sufficient, so before you make a decision to buy memory, whether the system statement is tuned?

　　　　　　

-----------------------------------------------------------------------------------------------------

A brief description of the thresholds for memory performance counters

Page life expectancy counter how much is this time higher than normal?

A: Many of the data on this value are misunderstood, said that 300s,300s is a reference value more than 10 years ago, is based on the memory of the server is affected by the 4GB memory constraints,

At present, the server memory is more than 100GB, with the same standard, it is clearly not accurate, the value of the calculation is related to the specific server memory configuration, a reference to the standard algorithm is Max Buffer Pool (GB)/4*300 (S)

Why is there a Buffer Cache hit ratio counter missing?

Many of the materials are introduced that the threshold is 90%,95% and other reference values, in fact, are wrong,

In fact, those who have actually observed, have long been found, from the ple and buffer hit ratio come to a fundamentally inconsistent conclusion.

For more information, see: wy123 's Blog SQL Server memory-related counters and memory pressure diagnostics buffer cache Hit ratio performance counters can really be used as a memory bottleneck to determine the indicator?

-----------------------------------------------------------------------------------------------------

Summary: Memory is one of the most important dependencies for a database, and memory problem diagnosis and optimization are critical to the system.

Optimization statements can make your system memory pressure significantly lower.

The effect of statement optimization is much more effective than adding hardware!

As a technician for the system problem positioning, analysis, tuning is the most important, if the memory problems are solved by adding memory, what is our value?

----------------------------------------------------------------------------------------------------

Note: This article is original, welcome reprint, please in the article page obvious location give this article link!
If you think this article is not bad please click on the lower right corner of the recommendation , thank you very much!

Quote Gaodashia in a sentence: "Deny SQL Server back pot, start with me!"

Expert Diagnostic Optimization Series------------------not enough memory?