SQL Server – Number of TempDB Files If Parallelism Can’t Be Used

maxdopsql servertempdb

Currently we have 4 cores and 4 tempdb files. I just came on as the DBA about 2 months ago and, for some reason, 3 of the tempDB files are 2GB and 1 is 1GB. Obviously, that is not best practice.

Being the OCD and proactive person that I am, I want to resize that file to match up with the other 3.

But wait, there's more. It's used on a system that has an application that gains performance by NOT using any parallelism, so we have it disabled. MAXDOP = 1, cost threshold = 0. It's the only application on the system and the SQL isntance is basically designed for this one application.

What are everyone's thoughts on this? Will it help with any slowdowns that were seeing, given that our MAXDOP = 1? Also, what implications/issues should I be on the lookout for when I change the file size? I'll be doing it at night, off production hours.

Thanks for the help guys.

Best Answer

Individual queries don't use parallelism? So what? If you have two completely different users running different queries at the same time, they too can make use of different tempdb files.

Of course it doesn't help all that much if you have four tempdb files all on the same volume - the real gain is when you can spread it out into multiple I/O paths.

Hard to speculate on "any slowdowns you're seeing" without more details.

Shouldn't be any issues with changing the file size. Just make sure they are all exactly the same size, have the exact same autogrow settings, and check to see if you should run trace flags 1117 and 1118.

Related Solutions

SQL Server 2012 – Troubleshooting Slow Local SSD TempDB

We cracked this a while back by replacing the RAID controller on the server. The disks and server configuration were fine but it appears that the RAID controller couldn't deal with the IO.

We are now in the good place of reads ~ 2ms and Writes at <= 5ms

SQL Server – SSMS 2008/2014 Showing Different Values for TempDB File Sizes

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb. NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at:

https://sqlblog.org/2009/10/14/bad-habits-to-kick-using-the-visual-designers

Best Answer

Related Solutions

SQL Server 2012 – Troubleshooting Slow Local SSD TempDB

SQL Server – SSMS 2008/2014 Showing Different Values for TempDB File Sizes

Related Question