January 2, 2013 at 2:21 pm
Another belated thought on this: If you're seeing a LOT of tempdb use, are connections using shapshot isolation (either explicit, or "read committed snapshot isolation" enabled on the databases)?
That will pick up the load on tempdb pretty heavily in many cases, since that's where the row versions are stored.
If so, tempdb's heavy I/O may have nothing to do with temp tables or table variables, and data on memory vs disk on those two may not apply in the slightest.
- 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
January 2, 2013 at 2:45 pm
Gus, RE the isolation level, it's the default read committed, with snapshot off.
L' Eomot Inversé (1/2/2013)
I think that the source of confusion here is teh difference between allocating space for some data on the disc and actually writing the data to the disc.Every page of table data, whether permanent table, temp table, or table variable, has to have space allocated for it on disc; however, it's possible that the data is never written to the space allocated for it. Most of the time, data is never written to the space allocated for table variables or for temp tables - that's what Gus is telling you; but all the time, space is allocated on disc so that the data could be written to disc if that became neccessary - that's what some of the references are telling you, but apparently it is so badly phrased as to appear to contradict what Gus told you.
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. But just to recap for anyone who doesn't want to read this whole thread: I still haven't seen any recent articles or examples of this happening. Here are two examples that show the contrary, and claim that RAM-only table variables/temp tables are a myth:
The first link shows disk space being allocated to tempdb when both temp tables and table variables are used:
One posted said that although that shows space being allocated, data is never written to that space until it becomes necessary. Yet Gus's article that he linked to shows that that isn't necessarily true either - this article steps through in detail and shows the data being written to the disk (not just space being allocated) when a table variable is used and there is more than enough RAM.
http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
I'll admit, I'm the rookie who's not yet believing what several experts tell me - based on the responses I've received so far, if this is a myth, it's a very persistent one! Maybe it takes a rookie to question what's always been told? The only examples I've seen show that table variables and temp tables are written (not just allocated space) to disk - even when RAM is available. That doesn't mean that they aren't also cached in memory to be read quickly, so far I believe that both are true. I'm really only curious now about writing data to temp tables/variables, and whether tempdb storage would be the bottleneck concern for writing to temp tables even when the system has an abundance of RAM.
Can anyone explain Gail's article that shows table variables being written to disk even when RAM is available (other than just linking to another article that simply states otherwise, without any proof or evidence)? And that's not meant to be confrontational, I'm really confused and looking for this answer because everyone's opinion seems to be contrary to the only actual examples I can find.
One of the previous comments suggested that Gail's example shows one table variable writing to disk, but doesn't mean that all table variables write to disk. Logically that's true, but impossible to test against all possible cases. So I would question if a 20MB table is immediately persisted to disk when there's 2GB of free RAM (as her example shows), then it sounds like writing temp data to disk may at least be so common that you can build a system to assume it will be the norm?
Sorry for the ramble, hard to respond to several posts in one!
January 2, 2013 at 3:02 pm
While I haven't done any experiments, I would caution against the thought patterns of discrete possibilities (if enough RAM then only in RAM). For anyone investigating, I would suggest working in a more open fashion:
What SQL Server feels is "Enough" may vary, and may be 99% or 1% accurate, etc. (and may undershoot or overshoot).
For each element (temp table, table variable, work table, SNAPSHOT ISOLATION data, etc. etc.):
Some percentage of the structure (0% to 100%) is stored in RAM.
Some percentage of the data (0% to 100%) is stored in RAM.
Some percentage of other elements (0% to 100%) is stored in RAM.
Some percentage of the structure (0% to 100%) is stored on disk.
Some percentage of the data (0% to 100%) is stored on disk.
Some percentage of other elements (0% to 100%) is stored on disk.
Note that for each of these, the percentages may add up to more than 100% - i.e. all in RAM as well as all in disk.
This, I suspect, is part of the cause of confusion - reality is likely much more complex than many of the "rules of thumb". Gail clearly showed that at least some of the data is written to disk at least some of the time, which is extremely valuable, and does completely debunk the "All in RAM" idea. That said, it is not sufficient to show how much is written to disk on what types of objects, but it is a good template for doing said testing.
Many cases may also be different, depending on SQL Server's estimates vs. actuals, bugs, coding artifacts, and so on and so forth.
January 2, 2013 at 3:08 pm
I know that at this point, for me the answer will still be "test various tempdb storage solutions, benchmark, and pick the best one for my environment". But I'm still hoping that some guru will be able to jump in and lay it all out, definitively and without question. 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?
January 2, 2013 at 3:43 pm
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 Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2013 at 3:47 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 2, 2013 at 3:49 pm
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
January 2, 2013 at 4:04 pm
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?
January 2, 2013 at 4:38 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2013 at 4:44 pm
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.
January 2, 2013 at 8:05 pm
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
January 3, 2013 at 8:00 am
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
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply