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 11:49 AM
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
I really appreciate your time, and I'll try and remember to come back and post after we implement our new storage solution (may be a couple months). I commented on Gail's articles, hopefully she will respond. I did notice someone else posted about Q4 in that Microsoft article you mentioned, and Gail responded that she's proven that MS article to no longer be true (or never was true?).

Thanks again for your time.
Post #1402012
Posted Wednesday, January 2, 2013 12:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
You're welcome.

Hope I didn't come across heavy-handed or anything. Just trying to say I don't have definitive answers for you. Someone probably does, but just not me. Wish I did.


- 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 #1402042
Posted Wednesday, January 2, 2013 1:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
kevin.kembel (12/21/2012)
GSquared (12/20/2012)
Table variables, temp tables, and worktables, only spill out of RAM onto drives, if they have to because of lack of available RAM, per MS.

I'd heard temporary tables, unlike variables, were always stored in TempDb's data files and not in RAM - do you have any links or articles that explain this that I could read? From what I can find it looks like we're both wrong, it looks like both table variables and temporary tables are always stored in TempDb and not in memory.
http://cybersql.blogspot.ca/2009/12/yes-table-variables-and-temp-tables.html

We've noticed in our live environment that the most active database for disk i/o in our system is TempDb, it actually noses out our OLTP. This makes sense if the article I linked to is true, and temporary tables and table variables are always written to the TempDb data files. We didn't think this was because of a lack of RAM (currently 32GB for our 35GB OLTP database), but I guess we'll know when we move to the 64GB system and monitor TempDb usage there.


I wrote a well-received article here on SSC a few years back that covers the differences: Comparing Table Variables to Temporary Tables

As far as memory or tempdb, check out this from http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k :
A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).


Summarize: if the data will fit in ram, it might not be written out to disk. But it always has to be able to be written out to disk, since the data might be greater than the available memory to hold the data. If it's written out to disk, it's written out to the tempdb database.

Don't forget that if your queries end up creating worktables, those are in tempdb. Your high IO from tempdb could just be from poorly performing queries or execution plans that were generated based upon poor statistics (don't forget that table variables don't have any statistics, so if you're using them you just might see some pretty remarkable IO improvements by changing to temp tables).


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1402056
Posted Wednesday, January 2, 2013 2:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,232, Visits: 2,554
I think Wayne's explanation goes pretty far to resolving the OP's confusion, but I think there's one other clarification about SQL Server's internal workings that might help.

SQL Server creates and stores data in "pages" of about 8K each. Rows of data from tables, indexes, etc. are stored in these pages. SQL Server reads pages from disk into the data cache (RAM) (or creates them there) when required by the T-SQL statements being executed. SQL Server performs operations on the rows in those pages in the data cache and writes information about those operations to the transaction log. As a result, a page in the data cache may differ from the version of that same page on disk - a so-called "dirty" page. Every so often, "dirty" pages are "flushed" to disk, i.e., the changed pages in the data cache are written to disk.

For both a temporary table and a table variable, SQL Server creates the object in tempdb, but this only requires the creation and allocation of the necessary pages in the data cache. As long as SQL Server has enough RAM available, SQL Server can hold the pages of the temp table/table variable in the data cache and does not have to (but apparently may) write them to disk. If the amount of data stored in the temp table/table variable exceeds the amount of RAM available to the data cache, the temp table/table variable "spills over" and SQL Server must write those pages to disk, where they will live until SQL Server reads them into the data cache again. Of course, local temporary tables and table variables are dropped when the creating process terminates, whether the pages exist on disk or in the data cache.

So temporary tables and table variables always "use" tempdb in the sense that they are created there, but their data pages may exist only in the data cache. While Gail Shaw's article cited above demonstrates that temp table/table variable pages MAY be written to disk in the absence of memory pressure (the point she was trying to prove in rebutting the "table variables are memory-only" myth), it doesn't prove that they MUST be written to disk (which is not what I think Gail was trying to prove). Without knowledge of the underlying code, though, I can't say when or how SQL Server decides to write temp table/table variable pages to disk when there is no memory pressure.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1402080
Posted Wednesday, January 2, 2013 2:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 8,844, Visits: 9,405
kevin.kembel (1/2/2013)
I've look at Google and your links, and it still appears to me that what you said before isn't entirely correct - which is why I was hoping for some explicit clarification. The articles you just linked to are a perfect example of what I mean. They seem to contradict each other, as well as what you said about table variables and temp tables (quoted above). So far I'm still assuming that they do always write to disk - both temp tables and table variables because that's the bulk of what I've read as well as what I've experienced when I monitor my TempDB's MDF read/write usage. If RAM is available, they cache as per usual, but they will still always write to disk even if sufficient RAM is available.

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.


Tom
Post #1402081
Posted Wednesday, January 2, 2013 2:21 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1402082
Posted Wednesday, January 2, 2013 2:45 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
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:
http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

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!
Post #1402090
Posted Wednesday, January 2, 2013 3:02 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: Monday, September 15, 2014 11:09 AM
Points: 869, Visits: 2,399
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.


Post #1402095
Posted Wednesday, January 2, 2013 3:08 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
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?
Post #1402097
Posted Wednesday, January 2, 2013 3:43 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 @ 3:43 PM
Points: 43,047, Visits: 36,206
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 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 #1402101
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse