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 «««123

Storage Subsystems and RAMDisks Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 3:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
kevin.kembel (1/2/2013)
Or maybe someone in SSC can turn it into an article, showing specific examples and proof of temp tables/variables that exist only in RAM and are never written to disk?


I believe, unfortunately, the only way to actually get a definitive answer would be to directly inquire of the SQL Server team at Microsoft and ask them to provide a white paper. I do believe that everyone hanging around here would read it thoroughly, too. It's one of those 'quasi-answered' topics that the experts here (like Gail and Wayne) have done their best to determine.

It'd be harder to go deeper into the rabbit hole without significant access to the developers themselves, and it's quite possible the algorithm itself is being kept as a trade secret. I've never seen anything quite as defined as you're looking for in this regards that's official.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1402105
Posted Wednesday, January 2, 2013 3:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 1,053, Visits: 2,560
GilaMonster (1/2/2013)
RAM-only table-variables are indeed a myth.

Table variables (like temp tables) will be in memory if possible and written to disk if not. They are always allocated space in TempDB, whether they're actually written down and removed from memory depends on what's happening (memory pressure, size of tables, etc)

They may well be written to disk even if there isn't memory pressure (though in that case the pages likely will remain in memory as well). That's, as far as I know, the lazy writer being proactive (the checkpoint doesn't write out dirty pages for TempDB)


I wondered if that might be the case - SQL Server basically saying, "Hey, I don't have anything else to do - might as well write these temp table/table variable pages to disk in case I need them there later."


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1402109
Posted Wednesday, January 2, 2013 4:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 2:22 PM
Points: 39, Visits: 190
GilaMonster (1/2/2013)
RAM-only table-variables are indeed a myth.

Table variables (like temp tables) will be in memory if possible and written to disk if not. They are always allocated space in TempDB, whether they're actually written down and removed from memory depends on what's happening (memory pressure, size of tables, etc)

They may well be written to disk even if there isn't memory pressure (though in that case the pages likely will remain in memory as well). That's, as far as I know, the lazy writer being proactive (the checkpoint doesn't write out dirty pages for TempDB)


Gail! Thanks for jumping in, that sounds like what the last few posts have said as well. So in your article where you showed the data persisting to disk when there was no memory pressure, that was because it was convenient for SQL at the time to be proactive? If the test was more intensive, and you were able to take a snapshot of the tempdb.mdf in the middle of it, are you suggesting that the data may not have been persisted to disk at that time, and existed only in memory?

It sounds like the truth lies somewhere in the middle, that you won't want to discount the value of super-fast tempdb when your system has an abundance of RAM because table variables don't just spill out of RAM when it can no longer be contained? But that there are more cases than just a lack of RAM that lead to tempdb writing variables to disk? If so, do you have any thoughts then on my original questions of using RAMDisk for tempdb storage, assuming that I'm not starving SQL Server of any RAM to do it?
Post #1402115
Posted Wednesday, January 2, 2013 4:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 39,953, Visits: 36,309
kevin.kembel (1/2/2013)
So in your article where you showed the data persisting to disk when there was no memory pressure, that was because it was convenient for SQL at the time to be proactive?


Probably. All I was proving there was that table variables are not memory only, nothing more.

If the test was more intensive, and you were able to take a snapshot of the tempdb.mdf in the middle of it, are you suggesting that the data may not have been persisted to disk at that time, and existed only in memory?


Maybe. Maybe not. Could be that the very first page changed got written immediately to disk. Why don't you test and tell us?

If so, do you have any thoughts then on my original questions of using RAMDisk for tempdb storage, assuming that I'm not starving SQL Server of any RAM to do it?


Personally I wouldn't use a RAMDisk. I like my memory to be memory, for SQL to use for user database data cache, tempDB data cache, plan cache, log buffer, lock memory, object cache, memory grants, thread stacks, backup buffers and all of the other things in SQL that will use memory.

TempDB should not be the majority user of memory. If it is, I'd argue that there needs to be some serious tuning to get the TempDB usage under control.



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 #1402119
Posted Wednesday, January 2, 2013 4:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 2:22 PM
Points: 39, Visits: 190
Awesome, thanks Gail, Gus, and everyone else for your input. It only took a few dozen experts to convince me (and Gail explaining her article to me), but I hope this thread helps others struggling with the same questions. I hope I have time to do some testing and benchmarking when we get the new storage to see what will benefit us most in our case.
Post #1402120
Posted Wednesday, January 2, 2013 8:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 7,732, Visits: 9,476
kevin.kembel (1/2/2013)
The consensus by everyone so far in this thread definitely seems to be that table variables and temp tables work in RAM when available, and only spill to disk when RAM is not available.

No, that's not so. The concensus is that that's what usually happens, not that it's what always happens.
One posted said that although that shows space being allocated, data is never written to that space until it becomes necessary.

I suspect that there you are referring to my earlier post - but that isn't what I said; what I said was that most often they won't be written to disc. However, even "most often" will be wrong too (unless there is most often adequate disc performance), because it depends on two things: (i)the pressure on the cache and (ii) the pressure on the discs. Usually if the pressure on the cache is low then the pressure on the discs will be low too so the sql system will sometimes decide to write a page now, while it costs effectively nothing, rather than later, when it may cost something. But optimising these writes that cost nothing by putting the data on SSD is a waste of resource - optimise a nothing cost and you still have a nothing cost, so no gain - and I think that's all you would be optimising, since you have your data and logs separated and presumably don't have non-db activity on the same drives as tempdb data.


Tom
Post #1402152
Posted Thursday, January 3, 2013 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
You're welcome. Glad we could help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1402398
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse