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 03, 2013 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:04 AM
Points: 9, Visits: 189
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 03, 2013 12:44 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 6:09 AM
Points: 742, Visits: 1,064
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 03, 2013 1:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1449335
Posted Friday, May 03, 2013 1:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:29 PM
Points: 12,741, Visits: 31,053
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 03, 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 @ 4:36 AM
Points: 41,521, Visits: 34,438
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 07, 2013 2:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:04 AM
Points: 9, Visits: 189
Sorry, my mistake GB of memory not Mb.
Post #1450023
Posted Wednesday, May 08, 2013 11:45 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 1,468, Visits: 4,267
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.




"Winter Is Coming" - April 6, 2014
Post #1450721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse