MSSQL tempdb - your location
-
If it's a VM which is typically the case in modern set ups, I use a separate .VHDX attached to the VM for different parts of the server... one of them being for the tempdb.
-
I have seen bad queries fill the temp db, and the system I inherited was on the system drive. That was a prod and a fun 30 minutes.
DBA couldnt believe it and we carved a special drive for tempdb, as he said the same thing. Not on system drive or db drive. -
@Tim_G said in MSSQL tempdb - your location:
If it's a VM which is typically the case in modern set ups, I use a separate .VHDX attached to the VM for different parts of the server... one of them being for the tempdb.
In this instance, it is a VM.
-
Is this the same person who said they needed separate drives just for the database on a 4 user accounting setup?
**Edit: Separate partition from the system drive = yes. Separate drive doesn't make sense when virtualized.
-
I've seen the same recommendation for one tempdb file per core in Epicor's Performance and Diagnostics Tool (config check shows an error state if your tempdb is not configured that way). I don't think it checks for tempdb being on a different logical drive from everything else (OS, logs, other database files), however. And in addition to that, I was thinking that later versions of SQL Server auto-created multiple files for you based on cores allocated to the VM (maybe 2014 or 2016), but I could be wrong.
-
Why is your critical SQL Server on a SAN? That's one of the last workloads you'd want on a SAN. And SAN isn't valuable, it's your cheapest storage - that's what SAN is for, lower cost bulk storage.
All that said, even when you have a SAN, you want your tempdb on a local SSD. Can be very small.
-
tempdb can also be moved to RAM disk, and I believe that this is a recommendation, if you have the RAM to properly handle it.
-
We run MSSQL on VMs under XenServer. We have two pretty hefty MSSQL VMs, and I have them configured so that TempDB, User DBs, Logs, and backups are all on their own virtual disks. These virtual disks all reside within the same SR, which is a virtual volume on our 3PAR SAN. Each MSSQL server is given it's own virtual volume.
I grow the respective virtual disks as needed, as well as the virtual volume. It has worked out well in our instance. If we didn't have the flexibility the 3PAR "virtual volume" model, I would probably be approaching this differently.
As for the "number of TempDB files per logical processor", I have this implemented on our two large production MSSQL servers. Whether or not it improved performance I cannot say for fact. I can tell you that it did not cause any harm for us. I have 8 TempDB files that are of equal size (since the rule of thumb is to not exceed 8 if you have more than 8 logical processors), and I grow them as needed. I believe there is a trace flag that you can set (in 2012 anyway) that will make auto-growth grow all files evenly, but I'm paranoid and handle it manually.
Of course, every MSSQL environment is different. So your resulst may vary.
-
Regarding tempdb on a local SSD vs. ramdisk, has anyone tried using a ramdisk for it? I was thinking about this today on the drive in to work. If your SQL Server is a VM and you have the host memory to spare, could you set a memory reservation for the SQL VM to be the same as the size of the ramdisk you want to use for tempdb plus the SQL Server application memory limit (which you set inside SSMS - set to unlimited unless you cap it) and thus ensure that both SQL Server and the ramdisk would be guaranteed host RAM? I guess you could include the extra RAM that Windows needs in your memory reservation too. And I assume this would still be faster than local SSD because you're running from host RAM, right?
-
@NetworkNerd said in MSSQL tempdb - your location:
Regarding tempdb on a local SSD vs. ramdisk, has anyone tried using a ramdisk for it? I was thinking about this today on the drive in to work.
I've known people to have done it, but have not done it that I can remember.
-
@NetworkNerd said in MSSQL tempdb - your location:
And I assume this would still be faster than local SSD because you're running from host RAM, right?
Yes, RAM is millions and millions of IOPS, rather than tens of thousands.
-
From what I've been reading regarding TempDB in a RAM disk is that it's not recommended these days. The way the MSSQL engine works (if configured properly) is it uses all the RAM of the server (gets complicated after 64 GB RAM if you're using the Standard edition, but even then it can use more). So, in theory, TempDB should be in RAM as much as the server allows. It will only "spill" to disk if there is not enough RAM to complete whatever TempDB operation is happening at the time.
From what I'm reading, the recommendation these days is to put TempDB on a local SSD and/or beef up the amount of RAM the MSSQL server has.
https://www.brentozar.com/archive/2014/12/sql-server-2012-standard-edition-max-server-memory-mb/
-
@anthonyh said in MSSQL tempdb - your location:
From what I've been reading regarding TempDB in a RAM disk is that it's not recommended these days. The way the MSSQL engine works (if configured properly) is it uses all the RAM of the server (gets complicated after 64 GB RAM if you're using the Standard edition, but even then it can use more). So, in theory, TempDB should be in RAM as much as the server allows. It will only "spill" to disk if there is not enough RAM to complete whatever TempDB operation is happening at the time.
From what I'm reading, the recommendation these days is to put TempDB on a local SSD and/or beef up the amount of RAM the MSSQL server has.
https://www.brentozar.com/archive/2014/12/sql-server-2012-standard-edition-max-server-memory-mb/
Good to know. Basically.... RAM disk but managed by SQL Server, not by you.
-
@scottalanmiller said in MSSQL tempdb - your location:
@anthonyh said in MSSQL tempdb - your location:
From what I've been reading regarding TempDB in a RAM disk is that it's not recommended these days. The way the MSSQL engine works (if configured properly) is it uses all the RAM of the server (gets complicated after 64 GB RAM if you're using the Standard edition, but even then it can use more). So, in theory, TempDB should be in RAM as much as the server allows. It will only "spill" to disk if there is not enough RAM to complete whatever TempDB operation is happening at the time.
From what I'm reading, the recommendation these days is to put TempDB on a local SSD and/or beef up the amount of RAM the MSSQL server has.
https://www.brentozar.com/archive/2014/12/sql-server-2012-standard-edition-max-server-memory-mb/
Good to know. Basically.... RAM disk but managed by SQL Server, not by you.
That's how I understand it. The key is configuring the MSSQL service to consume the appropriate amount of RAM. I'm trying to dig up the "rule of thumb" I used when configuring our production servers. I believe it was Total RAM - 10 GB. However this was based on our prod VMs having 96 GB RAM assigned to them.
I also enabled "lock pages in memory" which helped performance wise as well:
An official MS article: https://support.microsoft.com/en-us/help/2659143/how-to-enable-the-locked-pages-feature-in-sql-server-2012
A better article: https://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server/
This should prevent the OS from paging out SQL data, so less swapping will occur. You don't want to do this if you have not configured the memory limit of the MSSQL service as by default it's set to consume everything.
I should disclaim that I do not consider myself a SQL expert in any way and all my experience is with MSSQL 2012 under Server 2012 R2. Soo your experience may vary. I'd like to think I've got decent Google-Fu and an ability to eventually figure out which buttons to push in which order.
-
Some good stuff in this thread, thanks!
