tempdb on local or san disks
-
Hi
We have a server with 300Gb of local storage that is also going to be SAN attached. Rather than waste the 300Gb, would anyone recommend putting tempdb on these local disks?
e.g.
Local C: SQL Install
Local D: Tempdb datafiles
Local E: Page file
SAN G: All Other datafiles
SAN F: All transaction Logs (including tempdb)
SAN H: Backups
Can't bear to waste these disks!!
Thanks in advance
Andy
-
At my last job, we had a SAN and put the TEMPDB on it. It worked just fine.
Main thing to remember....SQL Server will now have a dependancy on the SAN. So when you reboot, the SAN MUST connect before SQL Server services start.
-SQLBill
-
Thanks SQLBill. All our other clusters are configured with Tempdb on the SAN. I was concerned about making full use of that 300Gb.
Andy
-
Ah, as this is a cluster, does this mean that all my datafiles need to go on the SAN?
-
We had a cluster until the vendor's upgrade required the cluster to be broken. Our system databases were on the server drives and the user database files were on the SAN.
It wasn't until we quit clustering that we put TEMPDB on the SAN.
I think you can put everything on the SAN if needed. Just remember the services can't start until the SAN is up or you'll have errors.
-SQLBill
-
We are not clustered and have a similar disk situation to Andy. I decided to put tempdb data files on the local disk. The local disk also holds the paging file and the TEMP folder. Performance is OK, and we have saved some I-O bandwidth on the SAN.
Everything else, including the OS and and tempdb log, is on the SAN. Mind you we have a HDS 9900 series SAN so we expect at least 99.999 availability from it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Thanks Ed
I have a feeling that as we are clustered, tempdb will need to go on the SAN
-
Hi Andy,
You mention your system is clustered, so you will not be able to put anything on a local 'node' drive that the SQL Server virtual instance relies on, these must go on the shared drive array(s) that service the cluster group.
Putting tempdb on SAN is not a problem as long as you tell the SAN Admins how you need the spindles allocating. By this, I mean, if you know that tempdb in your environment is heavily used and a potential bottleneck, then you may want it on its own LUN dedicated to the spindles that serve it (ie no other apps/services share that physical set of spindles) - this of course also applies to other parts of your SQL Server setup too, as I have found from experience that SAN Admins will utilise the spindles how they see fit to the detriment of Database Server performance.
HTH
Rgds iwg
-
Thanks iwg
I kind of came to the same conclusion in the conversation I was having with myself above!!
Its good to have it confirmed though!
Andy
-
Also remember that when the cluster fails over to the passive node that if your tempdb was set to auto-grow it will then have to finish growing out the new larger tempdb before SQL is back to full "on-line" performance. So I'd take a look at how your tempdb is setup before you switch to local disks.
-
Have you tried asking your san vendor? Typically the vendor will say which option is better for the particular san and environment that you have.
-
Sorry Brian, but in my humble experience (:satisfied
I have not found the SAN vendors ot their 3rd Parties to be the best option in how one should set up the databases. I also know that this is an issue a number of other DBAs and DB specialists have experienced too. I have worked on a number of SAN systems where one of my tasks was sort out poor performing DBs, only to prove it was the way the SAN was set up and utilised that was causing the performance issues. And again, I know of other well-respected DBAs who have had the same. Of course I cannot say this for every SAN Admin/Architect, but I've yet to meet one who knows the issues facing DBs (with their IOs) and how best a SAN can should be set up to minimise such potential issues. 
-
Thanks humbleDBA, that is great advise. Just what I needed!
-
1) You mentioned 300GB of local storage you might want to put tempdb on. It is CRITICALLY IMPORTANT to know what constitutes that 300GB "disk". It could be a partition on a single 2TB 7200RPM SATA drive in which case your tempdb performance is gonna be horrible.
2) You MUST also get to know the underlying IO subsystem with respect to all those other "drives" you mentioned.
3) You can put tempdb on local storage in a sql server cluster. There is a known "workaround" (unsupported to my knowledge) that allows this and MANY people are doing this now and putting tempdb on local SSDs such as FusionIO on sql clusters.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Please note this thread is 5 years old.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply