March 14, 2014 at 9:57 am
Configuring SQL to put TEMPDB completely on RAM? .If RAM is dedicate to TempDB will improve performance?
Thanks
March 14, 2014 at 12:31 pm
Admingod (3/14/2014)
Configuring SQL to put TEMPDB completely on RAM? .If RAM is dedicate to TempDB will improve performance?Thanks
You can't put tempdb exclusively in ram. The size of tempdb is very dynamic. There will always be some level of disc access.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 12:58 pm
Admingod (3/14/2014)
Configuring SQL to put TEMPDB completely on RAM? .If RAM is dedicate to TempDB will improve performance?Thanks
To be honest, this is a bit like putting a bandaid on a stabwound. If you're having performance problems, the best thing to do is to find out what those things are and fix them.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2014 at 4:00 pm
Actually putting tempdb totally in memory is a great idea in general.
10 years ago, we never imagine MS will have in-memory OLTP technology, so I bet in future, we will see TempDB-In-Memory, and Log-In-Memory technology coming out.:-D
March 14, 2014 at 4:15 pm
Thanks to everyone!
March 14, 2014 at 8:00 pm
jeffrey yao (3/14/2014)
Actually putting tempdb totally in memory is a great idea in general.10 years ago, we never imagine MS will have in-memory OLTP technology, so I bet in future, we will see TempDB-In-Memory, and Log-In-Memory technology coming out.:-D
Heh... 15 years ago, I would never have imagined that any IT manager manager would have allowed "eventually correct" data or optimistic locking where substantial amounts of data could be lost without a trace. I'm a real speed freak when it comes to wanting fast code but, as close as speed comes to accuracy in importance for me, speed will never be more important than accuracy or ACID properties in general for the types of things that I have to work with.
As for putting TempDB totally in memory, I agree, it would be good for the system but, considering that many developers don't know how to take advantage of the fact that TempDB currently starts out in memory and only spills to disk if the task is too big and that data must live in memory before it can actually be consumed, I think it would be just another justification for many folks to continue to write poor quality, inefficient code and it would continue to get worse where even having TempDB in large amounts of RAM would eventually run into the same slowdowns we currently experience with such code. It has happened with virtually every technological breakthrough we've seen so far.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2014 at 12:22 am
@jeff Moden, the wish that all developers will be able to write high-quality code was, is and will still be a wish. But just because of the fact that better "infrastructure" (hardware/new technology etc) can hide/compromise the "poor quality" of the code, so we should not strive for such "infrastructure" is not the right logic. Just using this "tempdb in memory" assumption scenario, if MS had this technology today, it would make lots of existing t-sql applications fly high. Down the road, hardware is always cheaper than human labor, we may save tens of thousands man-hour to tune tons of application codes if we have better "infrastructure". This reminds me of the old days when I struggled to reduce a few bytes here and a few bytes there to ensure my assembly code would be squeezed into the memory (the whole computer memory is 128K). At that time, the productivity was too low to generate sufficient and meaningful works.
March 15, 2014 at 9:00 pm
jeffrey yao (3/15/2014)
@Jeff Moden, the wish that all developers will be able to write high-quality code was, is and will still be a wish. But just because of the fact that better "infrastructure" (hardware/new technology etc) can hide/compromise the "poor quality" of the code, so we should not strive for such "infrastructure" is not the right logic. Just using this "tempdb in memory" assumption scenario, if MS had this technology today, it would make lots of existing t-sql applications fly high. Down the road, hardware is always cheaper than human labor, we may save tens of thousands man-hour to tune tons of application codes if we have better "infrastructure". This reminds me of the old days when I struggled to reduce a few bytes here and a few bytes there to ensure my assembly code would be squeezed into the memory (the whole computer memory is 128K). At that time, the productivity was too low to generate sufficient and meaningful works.
I think that you seriously over estimate how much TempDB is actually used in queries... even bad queries. I don't have access to a box with a RAMDISK in it but I'll get one. I've needed a new computer for about 6 years, anyway. It may take a week or two.
As to saving "tens of thousands of man-hour {sic}), there's only roughly 2,080 hours in a man-year. If we took the middle of the road on "tens of thousands" at 50,000 man-hours, you're talking about 24 man-years with no vacations, holidays, or potty time. If you think that "tuning" code would ever take that long for even a very large project, then you need to hire some good developers to write good code to start with because you don't actually need to tune code to have high performance code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2014 at 8:59 am
Admingod (3/14/2014)
Configuring SQL to put TEMPDB completely on RAM? .If RAM is dedicate to TempDB will improve performance?Thanks
You can get similar effects by putting tempdb on a Fusion-IO card.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 16, 2014 at 10:48 am
Jeff Moden (3/15/2014)
jeffrey yao (3/15/2014)
@Jeff Moden, the wish that all developers will be able to write high-quality code was, is and will still be a wish. But just because of the fact that better "infrastructure" (hardware/new technology etc) can hide/compromise the "poor quality" of the code, so we should not strive for such "infrastructure" is not the right logic. Just using this "tempdb in memory" assumption scenario, if MS had this technology today, it would make lots of existing t-sql applications fly high. Down the road, hardware is always cheaper than human labor, we may save tens of thousands man-hour to tune tons of application codes if we have better "infrastructure". This reminds me of the old days when I struggled to reduce a few bytes here and a few bytes there to ensure my assembly code would be squeezed into the memory (the whole computer memory is 128K). At that time, the productivity was too low to generate sufficient and meaningful works.I think that you seriously over estimate how much TempDB is actually used in queries... even bad queries. I don't have access to a box with a RAMDISK in it but I'll get one. I've needed a new computer for about 6 years, anyway. It may take a week or two.
As to saving "tens of thousands of man-hour {sic}), there's only roughly 2,080 hours in a man-year. If we took the middle of the road on "tens of thousands" at 50,000 man-hours, you're talking about 24 man-years with no vacations, holidays, or potty time. If you think that "tuning" code would ever take that long for even a very large project, then you need to hire some good developers to write good code to start with because you don't actually need to tune code to have high performance code.
There's nothing in the world like "instant gratification". I started to look for a new computer and a physical ramdisk card and came across a free download that would allow me to dedicate a portion of memory as a drive. My laptop has 6GB so I left 2 for the system, let SQL Server have 2, and dedicated 2 to a ramdrive.
I setup some data in my test DB (2 tables of 5 million rows each) and started doing some tests with and without indexes, etc, etc, with TempDB on my regular hard drive and with it in the ramdisk. I'm not done testing yet but when it comes to normal and non-SARGable queries that use hash joins and the like, there's such a small change that it seems to be not worth it.
What was worth it was changing a non-Sargable query to a Sargable query. Even without indexes, there was a +3x improvement in performance in just doing that (another article forthcoming). Adding the correct indexes made it an +83x improvement and an 800x decrease in I/O that has nothing to do with TempDB and so TempDB on a ramdisk didn't help at all there. It actually took me longer to write the non-SARGable query than it did to do it right because I had to think about how to do it wrong :-P. Adding the correct indexes, of course, only took a minute to type.
When it comes to SELECT/INTO into a temp table, it works about 5 times faster on the ramdrive and that would be really good for loading data via BULK INSERT/BCP but you'd need a lot of memory dedicated to TempDB for the types of files I load. Might be worth it, might not. Gotta think about that.
It also helped a fair amount when building a clustered index with SORT_IN_TEMPDB = ON but it didn't do much and doubled the amount of CPU time when building some NCI's. I haven't tested any rebuilds or reorgs, yet.
So far as query testing goes, TempDB on ramdisk did little to nothing because normal SARGable and non-SARGable queries just don't use much TempDB in the resulting hash join. I have to try some really bad queries where many people would use DISTINCT to overcome the duplication caused by accidental cross joins (many-to-many joins), etc.
I think you can get a bigger bang for the buck if you gather up all the developers once a week for a 20 minute Lunch'n'Learn to show them proper technique (which I do at work) than a ramdisk can offer.
I'll include all of my test setup, test harnesses, and results in a forthcoming article but, like I said, I see some utility in putting TemDB on a ramdisk but it doesn't appear to be the panacea of performance that a lot of people would think it is. Certainly, it's not going to save "tens of thousands of man-hour{sic}).
As a bit of a sidebar, there is no question that having good hardware helps. There's also no question that just having decent code helps an order of magnitude or more than just throwing hardware at a problem. It doesn't take hours of "tuning" to write decent code either. It just takes a very little bit of knowledge. It only takes 20 minutes to teach a roomful of developers how to write a SARGable date-based query and to demonstrate the extreme advantages of doing so. So far as I'm concerned, it's a crime to spend money on something like a ramdisk for TempDB in false hope for a performance panacea to overcome bad code instead of spending it on the occasional bit of internal training so that you can easily get 3x to 83x performance gains with virtually no effort.
Like I said, "Putting TempDB on a ramdisk is like putting a Band-Aid on stab wound". Fix the stab wound. Train your people. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2014 at 12:12 pm
jeffrey yao (3/15/2014)
@Jeff Moden, the wish that all developers will be able to write high-quality code was, is and will still be a wish. But just because of the fact that better "infrastructure" (hardware/new technology etc) can hide/compromise the "poor quality" of the code, so we should not strive for such "infrastructure" is not the right logic. Just using this "tempdb in memory" assumption scenario, if MS had this technology today, it would make lots of existing t-sql applications fly high. Down the road, hardware is always cheaper than human labor, we may save tens of thousands man-hour to tune tons of application codes if we have better "infrastructure". This reminds me of the old days when I struggled to reduce a few bytes here and a few bytes there to ensure my assembly code would be squeezed into the memory (the whole computer memory is 128K). At that time, the productivity was too low to generate sufficient and meaningful works.
It is nevertheless a good idea to do nothing which will encoyrage poor developers to believe that performance is not their problem while doing nothing to alleviate perormance problems and often actually increasing them.
If you have enough RAM to make a RAMDISK big enough to hold your tempdb, then almost always it will also be true that you have enough RAM to ensure that tempdb always remains in RAM and is never written to disc tyhis is trivial in 64-bit machines, but 32-bit machines may need to be told to use extended store - but of course you have to tell them that anyway to make them able to use the extra store as RAMDISK). In fact the only effects putting tempdb on RAMDISK instead of assigning all that store for SQLServer to use as it chooses are (i) that RAMDISK takes up whatever its storesize is whether or not tempdb is using it, so isn't usable for anything else even when tempdb doesn't need it and (ii) as a consequence of (i) the optimiser is no longer able to allocate the RAM now dedicated to tempdb to hold the data (whether in tempdb or elsewhere) whose presence in RAM will deliver the best performance, so will produce less efficient query plans than if it were given the RAM to play with.
In other words, it's nearly always all performance loss and no performance gain.
Tom
March 16, 2014 at 3:45 pm
Admingod (3/14/2014)
Configuring SQL to put TEMPDB completely on RAM? .If RAM is dedicate to TempDB will improve performance?Thanks
To answer this question, probably not nearly as much as people would have you think. It would help much more to educate the developers in some simple best practices for writing T-SQL. In those rare cases where putting TempDB in RAM would actually help, the best you'll normally get is 5 or 6X performance gains. Teaching the developers how to write good T-SQL could give you 3x performance gains across the board instead of just in rare instances and, if you work with the DBAs to properly index the tables, you can easily see 60, 80, or even 100x performance gains as well as dropping memory requirements and dropping memory IO by 800x.
Also contrary to popular belief, it doesn't take much training.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2014 at 9:23 am
Jeff, did you find IMDisk[/url] or one of the many other RAMDisk products? I'd really be interested to see the difference between a RAMDisk and a really good SSD for TempDB.
Very interesting points on BULK INSERT/BCP, though, Jeff - I presume you meant having BULK INSERT/BCP load directly into a table on tempdb for followup processing? For that particular case, I'd be more inclined to create a "Staging" database on a RAMDISK, and autogrow would get very interesting, since RAMDisks are typically of very limited size.
In response to the original question:
If your system has so much RAM, and your database is so small that the entire database can fit in buffers with room to spare, then I can see very little downside in allocating this "spare room" to tempdb if it'll be large enough; for instance, a machine with 256GB of RAM running a 50GB database with a 20GB TempDB; pulling 32GB out of RAM to a RAMdisk isn't going to affect SQL Server much. On the other hand, if you have this situation, the accountants really should start auditing your hardware purchases!
Otherwise, look into SSD's first - more space per $, and still very good performance compared to low numbers of spinning disks.
March 17, 2014 at 10:47 am
Guess no-one remembers sp_configure 'tempdb in ram' and DBCC PINTABLE... Gezzzz I'm getting old :crying:
But, hey, what goes around, comes around... feeling better now 😛
March 17, 2014 at 12:30 pm
We ran some tests just at the storage level and the RamDisk was 13x faster. Here are the CrystalDiskMark results
8x15k SAS Drives configured as RAID 1+0 and 100 Write Caching yielded:
Random Write 4KB (QD=32) : 29.483 MB/s [ 7197.9 IOPS]
RamDisk
Random Write 4KB (QD=32) : 389.149 MB/s [ 95007.2 IOPS]
Jeff - I am definitely looking forward to your future article on the subject. I agree that that putting tempDb in RAM may not be a cure for performance issues.
I think it all depends on the specific situation and how much someone has tested the solution. I kind of think that if you can get more performance out of a tuned system, (especially using parts on hand), even a 1% increase is forward progress.
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply