December 5, 2011 at 2:58 am
I've spotted that on one of our Sharepoint (2007) servers the buffer cache which hovers around ~16GB most of the time jumped up to ~22GB when an index maintenance job ran. I can understand this so that's fine, what i don't get it why an hour or so after the maintenance finished the buffer cache dropped back down to ~14GB. I always thought that once data was in memory it'd only ever be pushed out if a dropcleanbuffers was ran or a new page needed to be read in, pushing the current one out when the RAM is maxed. There is 26GB of buffer cache available so plenty of RAM and didn't see any errors relating to data being cached to disk (page file) - plus we have LPIM set.
There are log backup jobs happening every hour but no restores during this time.
We did experience some site slowness during a 45min period whilst this was happening but SQL Sentry doesn't really show anything out of the ordinary with regards to the cache drop. The drop wasn't as sudden as it would be if dropcleanbuffers had been ran, more like over a ~5min period it dropped steadily.
Could it be a SP timer job? I'm sure i'm missing something really simple here but can't see it.......
SQL Server 2005 (9.0.4285) on Windows Server 2003
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
December 5, 2011 at 3:08 am
Is that server is dedicated to a single instance of SQL Server or is it used to by other instances of SQL Server or other servers/services/applications that need memory? If it is used for other apps that need memory, then it could be that the OS took some memory from SQL Server in order to let a different application use that memory. If this is the case and you want to prevent it from happening again, you can modify the “min memory for server” option using sp_configure, but take into consideration that this will impact the other applications that use the same machine.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 5, 2011 at 3:23 am
Single dedicated instance for sharepoint databases only (config and content). Web servers are hosted elsewhere.
Min memory = 22528MB and max = 26624MB.
I'd thought about windows/other apps taking it back but can't see what it could be.....
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
December 5, 2011 at 4:11 am
Probably what happened is that the reindex pulled lots and lots of data into the cache (as it will) and those pages weren't used again, so they got aged out of cache so that there would be free space for new requests.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2011 at 4:18 am
GilaMonster (12/5/2011)
Probably what happened is that the reindex pulled lots and lots of data into the cache (as it will) and those pages weren't used again, so they got aged out of cache so that there would be free space for new requests.
Interesting......agreed on the index job pulling them in but how long does the data stay unused until its "aged out of cache"? I always thought that they'd just stay there till they were forced out or a flush.....?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
December 5, 2011 at 4:25 am
ChrisTaylor (12/5/2011)
but how long does the data stay unused until its "aged out of cache"?
Depends on how often they're used and if there's 'memory pressure' (which there probably would be if the data cache was huge after a reindex).
More than I can summarise here, I don't have any references offhand, but a read up on cache clock hands and cache internals would probably explain. It's complex stuff though, very complex.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply