How To Set Up Automatic Buffer Cache Clean?
-
Does anyone know how to clean the buffer cache earlier than it reaches the max size? In theory the cache holds inactive pages while buffer cache reaches its max size (or min free buffer pages limit ) then lazywriter thread cleans all inactive pages. In order to improve sql server performance I need to clear these inactive(not all) pages periodically
Thanks for your help
-
The lazywriter should normally be able to keep free space in the cache. As activity increases and the amount of free space gets lower the lazywriter will work more often. In a busy system it will probably be running more or less all the time, i.e. never going back to sleeping.
You cannot clear out specific pages from the buffer (afaik). What you can do is clear all non-dirty pages with DBCC DROPCLEANBUFFERS. But note that this will flush all pages that have not been modified. If that matches your definition of inactive or not, I can't say.
-
Thanks Chris. I know this DBCC statment, but as you said, it flushes all pages even those which are not aged.
On one of the our production servers the buffer cache fills up till 2GB and then cleans it automatically. After that server performance become notably better. So we would like to have this cache recleaning more often than it does and leaving usable pages in cache.
On SQL 6.5 manual I've found that there is such possibility to change parameters value like max free buffer size, and something like buffer cache scaning period interval (not sure this parameter is caled in that way) but what I can do on sql 2000?
-
Yep, as you noted there used to be some config options in 6.5, but to my recollection they were mostly used to throttle the lazywriter (and log writer), not make it run more often. In any case they do not seem to be there anymore, and none of my sources contains any info on how to make the lazywriter run more often (once every second by default, or more often when there is not enough free pages). Perhaps there is some trace flag that can be used in some way, but none that I know of. I would not be surprised if there is a flag to output information each time the lazywriter runs, for instance.
It does sound a little strange that performance becomes much better when the cache is cleaned. Btw, what exactly do you mean by that?
Have you monitored what happens at this point? Look at the counters for Buffer Manager, such as Page Life Expectancy, Lazy Writes/sec and Free list stalls/sec.
-
Well it becomes better because when our buffer cache becomes full, the server uses physical disk I/O instead of logical I/O. It reads data from disk not from cache.
But sometimes it automatically clears the cache and performance becomes better. Wanted to know what is going on and how to make the lazywriter to scan cache more often
-
using raid on server?
-
The disks are mirrored... Have you any idea?
-
what type of mirror configuration are you running? duplex-mode where in the two physical disks are connected to two different controllers or are you using standard mirroring when two physical hard-drive are connected on one controller?
-
Which cache are we talking about here? Is it the cache of the hardware raidsystem, or SQL Server data cache??
Pages are always fetched from cache in SQL Server, if they do not exist in the cache they will first be placed there and then read.
-
Standart. But does it have any relations with the buffer cache?
-
i just want to understand your environment.
btw: have you run perfmon.exe and include the db counters. this is as far as i know about this topic and as far as i will go on it, its really hard to say since everyone really encounters performance in sql
mate - only if you were born lucky. so good luck and hope everything goes well.here are some reference you can use
i have cut and pasted certains portions of the articles into this thread. the reference are worth looking at).http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarsqlsg/html/sqlperftune.asp
http://support.microsoft.com/kb/112539/EN-US/
http://support.microsoft.com/kb/q171034/
NOTE: This should not be done unless you have an expert level of system and SQL knowledge.
1. sp_configure "allow", 1 2. reconfigure with override 3. update spt_values set high=255 where name='max async IO' 4. sp_configure "max async io", 70 (where 70 is for example the new value) 5. reconfigure with override restart SQL Server
If LazyWriter is having problems keeping the free buffer level at the configured value (or at least regularly above 50 percent of the configured free buffer level), it could mean that the disk subsystem is not able to provide the disk I/O performance that LazyWriter needs (compare drops in free buffer level to any disk queuing to see if this is true). Or, it might be that 'max lazywriter io' needs to be set higher in order to refresh free buffers faster.
In disk subsystem terms, 'max lazywriter io' controls the number of simultaneous I/O requests that LazyWriter can have submitted to Windows NT (and in turn, to the disk I/O subsystem) at the same time. It is possible to set 'max lazywriter io' higher so that more dirty pages flush out of the SQL Server data cache at the same time onto disk. This helps keep the free buffer level up to the configured level, but it is important to be careful that the hard drives are not queuing up disk I/O requests.
Disk I/O Transfer Rates vs. RAID Controller Transfer Rates vs. PCI Bus Bandwidth
A typical hard disk provides a maximum transfer rate of around 10 MB per second or 75 nonsequential/150 sequential disk transfers per second. Typical RAID controllers have an advertised transfer rate of about 40 MB per second or (very approximately) 2,000 disk transfers per second. Peripheral Component Interconnect (PCI) buses have an advertised transfer rate of about 133 MB per second. The actual transfer rates achievable for a device will differ from the advertised rate, but that is not too important for our discussion here. What is important to understand is how to use these transfer rates as a rough starting point for determining the number of hard drives to associate with each RAID controller and, in turn, how many drives and RAID controllers can be attached to a PCI bus without I/O bottleneck problems.
In the previous section, How Advertised Disk Transfer Rates Relate to SQL Server, it was calculated that the maximum amount of SQL Server data that can be read from or written to a hard drive in a second is 2.4 MB. Assuming a RAID controller can handle 40 MB per second, it is possible to roughly calculate the number of hard drives that should be associated with one RAID controller by dividing 40 by 2.4 to get about 16. This means that at most 16 drives should be associated with that one controller when SQL Server is doing nothing but sequential I/O of 16 KB. Similarly, it was previously calculated that with all nonsequential I/O of 16 KB, the maximum data sent up from the hard drive to the controller would be 1.2 MB per second. Dividing 40 MB per second by 1.2 MB per second gives us the result of 33. This means that at most 33 hard drives should be associated with the single controller in the nonsequential 16-KB scenario.
Another way to figure out how many drives should be associated with a RAID controller is to look at disk transfers per second instead of looking at the megabytes per second. If a hard drive is capable of 75 nonsequential (random) I/Os per second, it follows that about 26 hard drives working together could theoretically produce 2,000 nonsequential I/Os per second, enough to hit the maximum I/O handling capacity of a single RAID controller. On the other hand, it would only take about 13 hard drives working together to produce 2,000 sequential I/Os per second and keep the RAID controller running at maximum throughput, because a single hard drive can sustain 150 sequential I/Os per second.
"Know RAID a Little and It Will Be Your Friend"
When scaling databases past a few gigabytes it is important to have at least a basic understanding of Redundant Array of Inexpensive Disks (RAID) technology and how it relates to database performance.
-
Configuration options for SQL Server version 6.5 and 4.21 is not of much help here unfortunately.
Vidas, I have still not completely understood which cache you are referring to.
The buffer cache is part of the SQL Server cache and is used to store pages in memory. All reads are made from the buffer cache, so if a page does not already exist there it is first read from disk, placed into the buffer cache and then fetched to the client.
-
Yes Chris, you are absolutelly right about what do I mean. I think that RAID there isn't that factor to which we should pay attention.
After running some tools like DBCC MEMUSAGE, which shows free buffer number, I begin to think that all work normal on this server. Yes, the buffer cache is allways fiiled up about 99percent, but we cann't to forget that 3GB of RAM is allocated to SQL server and it dynamically takes about 1.6GB. So buffer cache has about 1.5GB and is 99percent full. This 1 percent of free cache memory is about 15MB and maybe it's enough for the server to hold such amount of free memory. The DBCC statement shows that free buffer size varies from 1200-12, therefore I think that there is no such dramatical situation as it looked like before. What do you think?
-
Afaik, DBCC MEMUSAGE shows the 20 objects that have the most pages cached in memory. So I am not sure how you found "The DBCC statement shows that free buffer size varies from 1200-12". And where does 99% full come from? Are you referring to buffer cache hit ratio?
> What do you think?
As I said, I think the cache is being used correctly and the lazywriter is doing it's job. If you are having performance problems I assume they are caused by something else, possibly disk I/O (which might be caused by the raid-system).
-
Ups, sorry. Not DBCC MEMUSAGE but DBCC MEMORYSTATUS. 99% comes from one monitoring tool called Spotlight. It shows many general SQL Server parameters and couple of them are SQL server memory size, buffer cache size and proc cache.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply