![]() ![]() This has three benefits: the local SSD is often cheaper, it’s often faster, and it keeps your SAN path traffic freed up for your valuable user data file & log file reads and writes.Ĭreate 8 equally sized data files. Since SQL Server 2012, Microsoft has fully supported using local solid state drives even in a cluster. If you’re using a SAN-backed cluster, use local SSDs for TempDB. However, if TempDB is on its own volume that runs out of space, no big deal – just restart the instance and you’re back in business. If TempDB lives on the same volume as your user data and log files, and the drive runs out of space, you can have a tough time restarting SQL Server. Any user can dump a ton of stuff into TempDB without your control, and they can even run TempDB out of space. Get on this level – and by this level, I mean the latest updates shown on .Ĭreate a volume/drive/array for TempDB. You want this to be a separate volume from your user database & log files – not just for performance reasons, but also for logistical management reasons. Because you were so good this year, they even backported it to SQL Server 2012, too. Microsoft made performance improvements to SQL Server 2014 so that it writes to TempDB less frequently. If you’re on SQL Server 2012, get on SP1 Cumulative Update 10 or newer. ![]() The long version is a little more complicated. Presto, the drive is full and your TempDB is configured for easy performance. Create 8 equally sized data files and one log file, each that size. Divide the total space by 9, and that’s your size number. The short version: configure one volume/drive for TempDB. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |