Shulou(Shulou.com)06/01 Report--

Basic process of memory problem location:

Performance counters mainly used

Page life expectancy (database counter: the number of seconds that mainly show unused pages that will stay in the cache)

Lazy writes/sec (database counter: lazy writers trigger when there is pressure in memory and new memory requirements, flushing "aging buffers" in batches)

Page Reads/sec,Page Writes/sec (database-level counters are used here: count when pages that need to be read or written are not in memory and need to be read to disk)

Target Server Memory (KB) (total amount of memory that SQL server can use)

Total Server Memory (KB) (the total amount of memory used by SQL SERVER, in this case, the size of BUFFER POOL)

Available MBytes (system seriator: mainly shows how much memory is available in the system)

Note: Target Server Memory (KB)-Total Server Memory (KB) is approximately equal to the amount of memory that SQL SERVER can still use.

Step 1. Exclude applications from affecting memory

Total Server Memory (KB) (the total amount of memory used by SQL SERVER, here refers to the size of BUFFER POOL) can check the total amount of memory used by SQL Server. If the total amount of memory used is very small and the server still has a large amount of memory, please check whether the memory usage of SQL Server is limited.

Available MBytes mainly shows how much free memory is left in the system (if this value is large and Target Server Memory (KB)-Total Server Memory (KB) is 0 or less, you can appropriately increase max server memory (maximum memory, described later))

If there is a steep drop in the Total Server Memory (KB) counter, it can generally indicate the memory used by the database occupied by external programs.

Step 2. Memory problem location

Continuous memory pressure

Lazy writes/sec

Page life expectancy

Memory fluctuation pressure

Page Reads/sec

Lazy writes/sec

Page life expectancy

Step 3. Analysis and resolution of memory problems (general steps)

System sets maximum memory max server memory

Q: why do I have to set a usage limit when there is not enough memory in my system? I use this server for the database or set it up?

A: database is an application running on windows, and it is essentially no different from notepad for the operating system, so this is like the relationship between the monarch (operating system) and the minister (database).

And SQL SERVER is an application that likes memory very much, so it is possible to eat a lot of memory so that the windows system does not have enough memory to use, then the monarch-minister relationship is reflected incisively and vividly at this time, and the windows (SQL SERVER) dies (frees memory) the minister has to die. To some extent, this release is not just enough for windows, it is likely to cause a sharp drop in SQL memory, resulting in SQL's short-term false death (no response to the operation). So for the stability of your database, this maximum limit must be set.

Memory settings are recommended:

Generally speaking, I recommend that if the operating system with less memory reserves 3G-4G, if the memory is larger than 512, 5% is reserved for the operating system when there is no pressure on the database memory, and the rest is reserved for SQL SERVER. If the server has other applications, the memory occupied by the application should be reduced in SQL.

If the memory is relatively small and the memory pressure of the database is high, you can reserve an appropriate amount of memory for the system through the judgment of Available MBytes described earlier.

Note: the maximum memory is set in MB.

The optimization of the statement makes the statement consume less memory!

Please follow the following articles in the statement optimization series, which is only aimed at reducing memory

Reducing memory for statement optimization mainly focuses on several aspects:

Is the index missing?

Whether memory-consuming operations can be eliminated (such as sorting)

Reduce statement complexity so that the optimizer can choose the best plan

Statement memory consumption is mainly reflected in a large number of reads, or sorting and other operations.

The so-called read is simply the number of data pages needed to execute the statement. The more you need, the more memory you need to cache these data pages. If the required page is not in memory, it needs to be read from disk (disk reading is why Page Reads/sec is high)

A simple example of adding an index to reduce logical reading ~

Statement used an entire table scan plan, executed for 19 seconds, logical read 143800 times, pre-read 137236 (read on disk), consumed 40KB memory, and clearly indicated the lack of index!

So we add the missing index and execute it again.

With the addition of indexed statements, the number of logical reads in less than 1 second is reduced to 13, and memory consumption has been negligible. This is the importance of indexes to statements! In the case of a single statement, how many such statements are there in your system?

Let's take a look at an example of writing modification:

Just simply changed the writing time of the following sentence from 7 seconds to 1 second, and the memory consumption changed from 300+MB to 1MB.

These two examples tell us that maybe by simply making some adjustments in the system, the pressure on memory will be significantly reduced or become very sufficient, so before you make a decision to buy memory, have you tuned the statements for the system?

Step 4. Analysis and resolution of memory problems (special troubleshooting steps)

Memory fluctuation

If you are a system maintainer, see such in-memory data indicators, if you do not have some ideas, please familiarize yourself with your system.

This picture clearly reflects the memory pressure of the system every few hours, so don't rush to find statements at the corresponding point in time, we should at least think about what operations are executed regularly in the system. SQL JOB? Planning a mission? Regular processing at the front desk? Etc.

Is there any exception in this regular timing processing? Have there been any changes recently? Is the result of the implementation the same as you thought?

Maybe the problem is so clearly defined.

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.