March 17, 2016 at 5:36 am
I've the possibility to splits tempdb into multiple files, currently there's 1 big files, size of 480GB (actually 99% of free space)
which is the best way to accomplish the goal?
what do you think about this way?
-- resizing 1 files
ALTER DATABASE tempdb MODIFY FILE (name='tempdev', size = 70GB, FILEGROWTH = 0);
-- add other files
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'X:\tempdev2.mdf' , SIZE = 70GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'X:\tempdev3.mdf' , SIZE = 70GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev4', FILENAME = N'X:\tempdev4.mdf' , SIZE = 70GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev5', FILENAME = N'X:\tempdev5.mdf' , SIZE = 70GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev6', FILENAME = N'X:\tempdev6.mdf' , SIZE = 70GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev7', FILENAME = N'X:\tempdev7.mdf' , SIZE = 70GB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev8', FILENAME = N'X:\tempdev8.mdf' , SIZE = 70GB , FILEGROWTH = 0);
Restart mssql service
March 17, 2016 at 7:43 am
How big is that drive (X:\) in total? The total size of tempdb according to your code will be 560gb. It is probably good to always leave 10% - 20% for free space on the dedicated drive where you are creating the primary and secondary data files for tempdb to avoid filling the drive completely and space alarms. I noticed you are naming the secondary files (.mdfs). For naming consistency purposes, we always name secondary files (.ndf) - this is up to you though as the file extension can be anything. Where is the tempdb log located, on another dedicated drive for logs? If so, that is a good place for it.
You are creating 8 total data files. The most popular, latest recommendation for the number of data files needed for tempdb:
–Start With 1 data file for every 2 or 4 logical CPU cores, up to a maximum of 8
–If tempdb contention is detected, increase the number of data files by 4
–Repeat this process until contention is resolved, up to a maximum of 1 data file per logical CPU core
March 17, 2016 at 7:58 am
You haven't given us the most important factor here: what EXACTLY constitutes your X drive, and what else is on it? If it is a single or few disks, 8 files can actually make IO SLOWER. SOOO many people don't get that.
Also, do you have demonstrated PFS/SGAM latching problems in tempdb now? If not then the second main issue for using multiple files is of no concern.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 17, 2016 at 9:21 am
server has 48 cpu and
x: is 1TB, "semi dedicated" for tempdb, raid 10
(the big and heavy loaded database files are on another nas)
I sized the datafiles based on current size (480), this is actually my big doubt
found some pagelatch on tempdb during peak load ( use sp_whoisactive)
April 7, 2016 at 4:30 am
sorry to back in topic, but the DB shutdown will be next week (it happens 1-2 times per years).
in these days i've checked for gam/sgam/pfs on tempdb and found some,maybe i reduce to 4 instead 8 datafile?
during maintenance windows i'll apply the last patch, we have a cluster with 3 nodes with 2 sql groups running, any advice? I think to start with passive node, failover on the patched node and so on...
last thing, transaction log,
the biggest is 900GB+ with 1700+ VLF, should i shrink it and regrow with the same size ? db is in simple mode
there's any cons about this task?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply