Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Memory Poke Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 5:37 AM
Points: 9, Visits: 196
I have a supplier who insists on having two jobs that every couple of hours change the Max Memory setting of the SQL Server instance. Basically one job sets the memory to 45Mb and the next back up to 46Mb each one running in turn every two hours. The supplier insists that without this "Memory Poke" some or all writes to the database start to take longer.

I can see that every time the maximum memory setting is changed, the procedure cache is flushed out of memory, but I can't find anything that would suggest why regular changing of the max memory setting could influence database writes.

Our monitoring software shows a Procedure Cache Hit Ratio of around 85% on average. A recent disabling of the jobs that change the memory setting didn't seem to push this higher.

Anybody seen this elsewhere or got any suggestions as to where to look for an explanation; or even it can't have that effect? The supplier just wants it running "in case".
Post #1449184
Posted Friday, May 3, 2013 12:44 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 839, Visits: 1,198
This is the strangest thing I have heard. I never have heard anything like that before. But I have 1 question is it 45mb or GB?
Post #1449326
Posted Friday, May 3, 2013 1:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
Never heard of such a thing. Sounds like someone was under pressure to put something, anything, out there as a solution to a problem they could not solve, but what an odd thing to have decided to do. I would do some observational-based testing and look to remove those jobs.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1449335
Posted Friday, May 3, 2013 1:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 12,953, Visits: 32,482
i was thinking the same as opc.three;
it's a false cause-effect i bet;

i'm thinking that procedures started sucking-performance-wise because of stale statistics, and the clearing of the proc cache, which occurs when you change the memory, solves the problem indirectly by forcing some new plans to be created.

That's my first guess.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449347
Posted Friday, May 3, 2013 1:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
I'll call BS on that. Changing the max memory setting is not going to affect SQL writes.

Could be that you have bad plans getting into cache (maybe problematic plan caching), but there are way better ways of fixing that than changing a setting that clears the cache as a side effect.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1449352
Posted Tuesday, May 7, 2013 2:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 5:37 AM
Points: 9, Visits: 196
Sorry, my mistake GB of memory not Mb.
Post #1450023
Posted Wednesday, May 8, 2013 11:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 1,792, Visits: 5,041
Identify a specific INSERT or UPDATE statement is believed to benefit from this cache flushing. It's possible that the actual I/O isn't slow, but rather the execution plan is stale. Perhaps there is a complex SELECT / JOIN associated with the INSERT or UPDATE. You can specify RECOMPILE hint on stored procedure or individual statements to force recompile and creating fresh plan each time it is executed. That's a common thing to do for long running procedures.

Post #1450721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse