Support local disk location for TempDB in failover cluster installation

Support local disk location for TempDB in failover cluster installation by qcjamess

Closed

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	532759
Opened:	2/11/2010 12:42:59 AM
Access Restriction:	Public

Workaround(s)

view

In busy SQL Server environments the TempDB database can be the database with the highest IO load. If row versioning is enabled, the IO load can be much higher than any other database - especially since TempDB is a shared resource used by all databases in a given instance.

Failover clustering has the requirement that all SQL Server databases be contained on the shared storage - including the TempDB. This doesn't make much sense to me, especially given the fact that TempDB does not persist across restarts of the SQL Engine. Also, the IOPS generated by a busy system's TempDB utilization can overburden a system's HBA and storage arrays, leading to slower disk performance for log and databasedatafile IO.

This problem is made even worse for a stretch/geo dispersed cluster where the TempDB IO is mirrored to the remote storage unnecessarily.

Details (expand)

English

SQL Engine

Support placing the TempDB database on local storage. TempDB on local SSD storage would be preferred because of the huge performance gains over standard storage.

Since TempDb does not persist across restarts and is actually dropped/recreated each time SQL starts up this should really not pose any problem whatsoever.

Improved Performance

0 attachments

Posted by millertime78 on 3/28/2012 at 9:00 PM

I'm glad to see this now in SQL2012

Posted by Johann Weskandt on 2/16/2012 at 7:31 AM

Note: This is possible now/soon in SQL Server 2012! See http://msdn.microsoft.com/en-us/library/bb500459%28v=SQL.110%29.aspx and related links for details!

Johann Weskandt
Escalation Engineer for SQL Server
Microsoft GMBH, Germany

Posted by Microsoft on 4/5/2010 at 11:19 PM

Enabling tempdb over local disk for a clustered instance need to change the current SQL Server Engine behaviors. Considering the potential impact of the Engine changes, we will not add this new feature in SQL Server 2008 service packs.

On the other hand, we are actively investigating the possiable solutions to support this in a future release.

Thanks again for your valuable feedbacks.

Regards,
Min He
Program Manager, SQL CRD Engine

Posted by Microsoft on 2/20/2010 at 3:08 AM

Creating tempdb on local disk is not supported by current SQL Server releases. We will investigate for a future release to support it.

Thanks,
Min He
Program Manager, SQL CRD Engine

Posted by justin.6 on 3/11/2010 at 11:49 AM

thank you for all the instrucktions you have given me I'm hoping to make the momt of it thanls again, jr

Posted by justin.6 on 3/11/2010 at 11:54 AM

justinraymond2010@googlemail.com

07526748369