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 Thursday, December 20, 2012 10:19 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
We're setting up a new database server for our OLTP and smaller website databases, and disk performance is a big area of concern. We have 3 disk arrays available to us, and 64GB of RAM.

The 3 storage systems are:

Inline SAS HDD (2-disks 15K)
DAS SAS HDD (4-disks 15K)
DAS SAS SSD (8-disks)

On these 3 storage arrays, we have 6 main types of storage.

System Data Files (master, msdb, model, distribution)
TempDb Data Files (8 data files for 8 cores)
OLTP Data Files
OLTP Data Indexes (separate file)
Website Data Files (much lower use than OLTP)
All The Log Files (System, OLTP, Web, etc)

We planned on distributing our files on the storage systems like this:

Inline HDD (OS) – Don’t want much here because it houses each of the OS’s
- System Data Files
- TempDb Data File (1 of 8 data files)
MD HDD (4-disk)
- All The Log Files
- TempDb Data Files (3 of 8 data files)
MD SDD (8-disk)
- OLTP Data Files
- OLTP Data Indexes
- Website Data Files
- TempDb Data Files (4 of 8 data files)

Analyzing our current system, the largest I/O requirements are for TempDb, followed by OLTP Data files - so those are our main focus. We are looking into the option of allocating 8GB more RAM to the server to create a RAMDisk (not taking away any of the 64GB already allocated to SQL). The RAMDisk would be used entirely for 4 of the 8 TempDb data files, isolating most of the TempDb work in RAM. Then rearrange the rest a bit to better isolate TempDb and OLTP data files:

Inline HDD (OS) – Don’t want much here because it houses each of the OS’s
- System Data Files
RAMDisk (8GB)
- TempDb Data Files (4 of 8 data files - 2GB each)
MD HDD (4-disk)
- All The Log Files
- Website Data Files
MD SSD (8-disk)
- OLTP Data Files
- OLTP Data Indexes
- TempDb Data Files (4 of 8 data files - 2GB each)

To me, this solution looks much better, and from what I read we should get some major performance boosts as a result. There are lots of guides out there showing how to use RAMDisks for TempDb performance improvements, but other experts say to stick to the SQL Recommended Best Practices which doesn't include RAMDisks.

My question is, has anyone here used RAMDisks for TempDb, and what are their thoughts and experience?
Post #1399050
Posted Thursday, December 20, 2012 10:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I wouldn't bother using a RAM disk for tempdb.

Most of what accesses tempdb will already be perfectly happy to live in RAM till it runs out and has to use the disk. So the gains are likely to be minor, if they exist at all. 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.

So, test it, and if you find it really gives you the boost you need, go for it. But definitely test thoroughly and don't be surprised if the benefit is hard to isolate or nonexistent.


- 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 #1399068
Posted Friday, December 21, 2012 9:08 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
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.
Post #1399472
Posted Friday, December 21, 2012 3:14 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
GSquared (12/20/2012)
I wouldn't bother using a RAM disk for tempdb.

Most of what accesses tempdb will already be perfectly happy to live in RAM till it runs out and has to use the disk. So the gains are likely to be minor, if they exist at all. 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 do agree that in most cases (this one in particular), putting tempdb on a RAMDisk is a waste of RAM. In a few cases (i.e. tempdb maximum size is smaller than the amount of RAM doing nothing), then it could help tremendously, since some worktables spill to RAM based on SQL incorrectly estimating data size, and in particular the tempdb log file does get a fair number of writes in many of our cases.

I am, however, extremely puzzled by your tempdb data files mixed between different storage speeds. That should lead to either very inconsistent performance, or consistent performance at the very slowest speed, neither of which is beneficial. Why split tempdb between different types of storage? What tradeoffs are you expecting?
Post #1399604
Posted Friday, December 21, 2012 4:26 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
My plan with splitting tempdb across three different storage systems was because I didn't have any fast storage to isolate the files on their own. My understanding is that SQL would use the tempdb files in round robin, and while I assume you're correct that some tempdb files would perform faster than others, several concurrent processes would be able to access tempdb data using the three storage systems simultaneously. Since tempdb would be in contention with other I/O on each storage system.

I think with the RAMDisk solution, it would probably be best to put all of the tempdb files on the RAMDisk together - instead of split with the SSD like I originally posted. What are your thoughts, am I misunderstanding something?
Post #1399630
Posted Thursday, December 27, 2012 7:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
One good article on temp and variable: http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

Answer from Microsoft on where (RAM vs disk) temp tables and table variables are created: http://support.microsoft.com/kb/305977

Relevant quote (from Q4):
If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).


Hence my point that leaving the RAM available for the cache is probably a better use than a RAM-disk.

Puting the log files for tempdb on a RAM disk might have some performance improvements, but it is again unlikely because of how caching works. It would definitely render the data non-ACID, but tempdb pretty much already does that anyway, so no loss there.

If you decide to go ahead and do that, I'd like to see before and after performance numbers for the server. Total wait time would be the one to look at.


- 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 #1400617
Posted Wednesday, January 2, 2013 9:03 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'm going to try with and without a RAMDisk for TempDb, as you suggested, compare the two to be sure.

But I'm more confused now than before about disk usage with temporary tables and table variables. Your MS link is for SQL2000, which I have read handled temp tables in RAM differently, so I'm not sure that I believe the answer to Q4 is still true. The other article you linked to suggests that SQL Server caching temporary tables in RAM (when available) has led to the 'legend' that they're stored in RAM instead of disk. What I can gather from various sources is that it seems like both temp tables and variables are always written to tempdb's data files, but are often cached in RAM as well when available, especially when smaller and more frequently accessed (which would still mean that fast storage for TempDb would be required, even when RAM is available). Which doesn't sound all that different from user tables to me.

So this thread has taken a turn, but does anyone have anything recent and definitive that explains exactly where temp tables and table variables are stored (RAM or Disk), and in what scenarios does that depend? If it is stored in RAM when RAM is available, why would Dave Pinal's article I linked to earlier show TempDb pages written to disk?
Post #1401894
Posted Wednesday, January 2, 2013 9:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
SQL 2000 did handle table variables differently than prior versions of SQL Server. They didn't have them, it was a new feature. It hasn't changed, mechanically, since then.

Here's some data on how table variables work: http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

Here's some more data on both: http://sqlinthewild.co.za/index.php/2007/12/19/temp-tables-and-table-variables/

Bing/Google the subject, you'll find the data you need pretty readily.


- 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 #1401932
Posted Wednesday, January 2, 2013 10:56 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
GSquared (12/20/2012)
Most of what accesses tempdb will already be perfectly happy to live in RAM till it runs out and has to use the disk. So the gains are likely to be minor, if they exist at all. 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'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.

From the first link, "A Trio of Table Variables" http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

This myth can be broken down into two parts:
1. ...
2. That table variables are not written to disk

To answer myth #2, it looks like he creates a basic table variable example that is definitely not under memory pressure...

20 Megabytes. The SQL instance I’m running this on is allowed to use up to 2 GB of memory. No way on earth is this table variable going to cause any form of memory pressure (and I promise there is nothing else running)

He then kills SQL, and uses a hex editor to view the tempdb.mdf data file on the disk. He shows the data in the disk to prove that his table variable was written to disk even when there was plenty of RAM available. Then says this about myth #2...
That pretty much speaks for itself. This myth, clearly false.


What he seems to be showing here directly contradicts what is said in the second article that you linked to from the same site:
From the second link, "Temp Table and Table Variables" http://sqlinthewild.co.za/index.php/2007/12/19/temp-tables-and-table-variables/

... Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache


So the first article shows that table variables are written to disk even when there is no memory pressure, while the second article claims in a summary of table variables that they are not persisted to disk unless there is memory pressure. I really must be misunderstanding something fundamental, like you said there's lots of information on Google/Bing, but most of what I find seems to be obviously contradictory.

The only obvious difference in context between them that I see is that the second link claiming table variables "are not persisted to disk unless there is memory pressure" is over 5 years old, where the newer article shows that they are persisted to disk even when RAM is available. Does the newer article debunk the myth that they perpetuated in the older article?

I thank you for posting those two links, these are perfect examples to work with, can you explain them to me and how they are both correct?
Post #1401975
Posted Wednesday, January 2, 2013 11:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Honestly, if you want commentary on the two links, you might ask Gail directly. She (not he) is better positioned to explain her posts than I am.

I'm not a Microsoft engineer, so I don't have "insider access" to the code that handles disk-spillover. (Neither is Gail, for that matter.) So what I have (and what she has) is data from what we can research online, plus our own experimentation.

That's why I said I'd be interested in seeing your numbers comparing what you get from using a RAM disk vs the same load on a regular (HDD or SSD) mechanism. After all, direct observation is generally superior to reading about something anyway.

I'm giving you the data I've got. That's all I can do in this kind of situation.

Personally, in 12 years as a DBA, I've never yet found that optimization at that level has mattered to me. I've found that 99% or more of performance issues on the database servers I've dealt with, are in the way the data was architected (normalization, et al), and in the way the DAL is built (procs, inline code, et al).

But that's my limited, annecdotal experience. I'd be willing to bet the guys who do EVE Online (last I heard, it runs on SQL Server) are MUCH more concerned about getting every microsecond of performance out of their databases, than I ever will be. They probably have to work at those levels of optimization.

But I'm usually dealing with a few thousand transactions per second, mostly just feeding dynamic content to websites. Not that those don't need tuning, but not at the kind of level you're looking at with RAM disk vs SSD for tempdb.

The kind of performance issues I have to deal with are things like:

When we got a new engine for our websites, it was built by a third party company. In preparing to go live, we found out that the home page was going to take an average of 6 minutes to load, because of really, really, really poor database design. I spent a day re-architecting their database, a couple of days working with the web devs on regression testing to make sure I hadn't broken anything, and got the average load time to a few milliseconds. Query time went from 6 minutes to about 8 milliseconds, for the exact same data. That page gets a few hundred hits per second, from all over the world. 8 milliseconds is good enough for what we need. Could it be 7 instead? Probably, with the right amount of work done at the server level.

We had an ETL process that, besides losing data, was also taking up to 12 hours to run through a few thousand records per day. I inherited this from my predecessor (who left this job to manage databases for a bank - which is a scary thing from my perspective). I fixed it, refactored it, and took the average time for the export process down to about 10 minutes. Again, I'm sure it could be refined down to 8 minutes if I spent the necessary hours to fine-tune the server environment.

Prior employer, had a daily process that I was asked to find out why it was "failing" so often. Day one on the job, I found the "daily" process was taking anywhere up to 70 hours to run. Part of the problem is it was using a VB.NET construct to read XML files line by line, then using string functions to parse the data out of the XML, then staging each value into a Name-Value-Pair construct, then reconstituting the data into properly normalized tables. (Yes, that was "part of the problem". It had other issues on top of that.) I converted that part to a bulk import of the XML files, then XQuery to parse it directly to the normalized tables. Run time went down to something like 20 minutes just from that one refactor. That also got rid of some data-loss issues. Took me about 2 or 3 hours to find the problem and fix it. No server-level work needed at all. Could I have taken it down to 15 minutes instead of 20 by spending a few days optimizing the I/O channels for the text files? Probably.

In all of these cases, and thousands more just like that, I've sped up data processes by simply huge margins, without having to worry about details at the level you're playing with. I'm interested in what you find, because I'm curious about that kind of thing, but I'm dubious about its value except possibly in some extreme edge cases (like, possibly, EVE).

The extra time, probably measured in days or weeks, it would take to fine-tune each server in the manner you're experimenting with, just isn't worth the potential ROI for someone like me. That same time can be spent re-architecting, re-coding, etc., much more profitably.

So, all I can do is give you what I've found on the subject. I think you'll need to experiment with it on your own to really find out what, if any, benefit you get from it. I am interested in what results you get, but mainly out of curiosity, not need.


- 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 #1402000
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse