May 10, 2016 at 12:31 am
Hello,
Unfortunately, we only use SQL Server 2014 Standard Edition, we can't use the memory optimized table feature.
Can I move just one table (it is a staging table) to a Windows 2012 R2 RAM disk?
If yes, may I have the steps? Thanks.
May 10, 2016 at 12:49 am
onlo (5/10/2016)
Hello,Unfortunately, we only use SQL Server 2014 Standard Edition, we can't use the memory optimized table feature.
Can I move just one table (it is a staging table) to a Windows 2012 R2 RAM disk?
If yes, may I have the steps? Thanks.
Haven't done this but first thought is to create a new filegroup with one file on the RAM disk and then move the table to that filegroup by rebuilding the clustered index in the new filegroup. Obviously other indices must be rebuild afterwards.
😎
May 10, 2016 at 8:34 am
onlo (5/10/2016)
Hello,Unfortunately, we only use SQL Server 2014 Standard Edition, we can't use the memory optimized table feature.
Can I move just one table (it is a staging table) to a Windows 2012 R2 RAM disk?
If yes, may I have the steps? Thanks.
Since this is a throw-away table, why not create a database for it and specify the data/log files exist on your spiffy RAM disk?
I will assume your server has sufficient RAM above the 128GB that SQL 2014 Standard Edition can have allocated to it. Otherwise I REALLY question if this is the best thing to do with your memory!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 10, 2016 at 9:04 am
onlo (5/10/2016)
Hello,Unfortunately, we only use SQL Server 2014 Standard Edition, we can't use the memory optimized table feature.
Can I move just one table (it is a staging table) to a Windows 2012 R2 RAM disk?
If yes, may I have the steps? Thanks.
You'll probably see no performance improvement by placing the table on a RAM disk. SQL Server already retains data pages in buffer cache, so the database engine will go to the cache first anyhow. Consider if it's really table scans that you're strugging with and if better indexing will solve your performance issues.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2016 at 9:56 am
Did a quick test on my 2nd generation i5 laptop which has 3rd gen. SSD, the ramdisk dbs and tables in a ramdisk filegroup do not yield much of a benefit except in file growth events. The test was obviously not exhausting but I think it was thorough enough to cover this hardware scenario.
Quick question, what is the hardware you have and how is it set up?
😎
May 10, 2016 at 12:01 pm
If you have a database with heavy write activity, something like a high volume DW or ETL staging, then placing the transaction log on a RAM drive would probably yield significant improvement, because that's where the I/O bottleneck is. However, it would also make the database less fault tollerant, and I doubt that SQL Server will allow transaction log files to be placed on a RAM drive, unless the device driver can fake the OS into thinking the drive is fixed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2016 at 9:49 pm
I didn't get any performance gain from using RAM disk.
My SQL 2014 is running on VM. So, all of its disk drives, actually, are using the same disk system.
It seems there is a disk IO boundary on my 2014.
I haven't turned on the Resource Governor.
Are there any steps I missed when I installed SQL 2014 and Visual Studio?
Any script for SQL 2014 disk IO stress test?
May 10, 2016 at 9:59 pm
onlo (5/10/2016)
I didn't get any performance gain from using RAM disk.My SQL 2014 is running on VM. So, all of its disk drives, actually, are using the same disk system.
It seems there is a disk IO boundary on my 2014.
I haven't turned on the Resource Governor.
Are there any steps I missed when I installed SQL 2014 and Visual Studio?
Any script for SQL 2014 disk IO stress test?
Don't think you missed anything. As I posted earlier my tests didn't show any substantial gain over using ramdisk over the local SSD except for the file growth and file initialization. You can probably put that extra RAM in better use as Kevin suggested especially if you haven't reached SQL 2014 Standard Edition's limit of 128Gb.
😎
May 11, 2016 at 3:55 am
Eric M Russell (5/10/2016)
You'll probably see no performance improvement by placing the table on a RAM disk.
I would agree with this.
The performance improvements from Hekaton don't come from the table being in memory. They come from the new row structures, the lock-less, latch-less concurrency, the optimised access paths for hekaton tables.
Just forcing a table to be in memory via RAM Disk isn't going to get you any of that, and it may have adverse impacts elsewhere as you're now spending memory to 'store' the table, as well as memory in the buffer pool for the same table.
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
May 12, 2016 at 12:27 am
Thanks for all of your replies.
May 12, 2016 at 12:58 pm
I strongly disagree with the statements that having a DATA FILE on a RAM DISK with a table in it won't speed up work on that table. For reads, when the data isn't in the buffer pool and physical reads have to be done those will now be done at microsecond-scale RAM speed. Even if you have Fusion IO on your PCI bus RAM access WILL be faster (although driver implementations could gum things up a bit obviously). For writes the same occurs, although RAM writes will often be relatively faster for a variety of reasons that slow down physical media writing (RAID, all the stuff between buffer pool and physical media, rotating rust, etc).
Note this does hold my original caveat that it is still important for RAM to be large enough to hold both SQL Server stuff and this RAM disk.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply