Sql-server – Best practice for tempdb log file

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed.

As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Now also make sure that the tempdb files are created such that you don't need auto growth and that they are all the same size with the same growth parameters not in percentiles.

Your tempdb is now ~140 GB so that is about the size you need to provision for. Create 8x15-20 GB datafiles and a single log file (as transaction logs are used sequentially) set all the files with exactly the same growth parameters, something that makes sense on your storage 512mb is fine if you have instant file allocation. As your transaction log is just about 5GB create a single 6-8GB file for the transaction log and set autogrowth to be in MB. Log growth is not affected by instant file allocations so make that parameter smaller than the one on the database files.

If you still see contention then go for 16x 8-10 GB files and dont change the transaction log parameters

I think you've overfragmented your tempdb and there is a mismatch between the server CPU and disk setup, but let's collect some more information:

Questions / Further information required

• Please confirm processor name and type (I'm basically trying to establish if it's 2 x hex-core with HT ). Use System information (eg Control Panel > System and Security > System on Windows Server 2012 R2) and / or sysinternals tool CoreInfo to confirm.
• Please confirm server maxdop ( eg EXEC sp_configure 'max degree of parallelism' ). If the CPUs are hex-core, the server maxdop should be at most 6 (as per here), or arguably lower on an OLTP system. I normally keep my tempdb files in line with my server DOP to a max of 8 but we'll come onto that.
• Please confirm server total memory on the box and the SQL Server memory cap ( eg EXEC sp_configure 'max server memory (MB)' ).
• Please confirm if any other services are running on the box ( eg SSIS, SSAS, SSRS, the application, iTunes etc )
• Please confirm Instant File Initialisation is enabled for the SQL Server service account. (Ways to test it here).
• Why is there such a massive discrepancy between the CPU (beefy 2 node NUMA setup) verus the one disk (home PC)? Consider adding disks, striping, SSD for tempdb (although avoid over-reacting : ).
• Please add an actual execution plan for one of the problem queries. Anonymise with SQL Sentry Plan Explorer if you wish.

• Hash joins with table variables in an OLTP system? This suggests a lack of indexing on the table variable, main table or both. Are you declaring your table variables like this (with no indexes)?

  DECLARE @t TABLE ( x INT )
  

• Don't skimp on the table variable definition even though it's holding small resultsets. It's always best to give the optimizer as much information as possible so be explicit with nullability, uniqueness, whether or not the index is clustered / non-clustered, eg

  DECLARE @t TABLE ( x INT PRIMARY KEY )
  DECLARE @u TABLE ( x INT PRIMARY KEY NONCLUSTERED, u INT NOT NULL UNIQUE CLUSTERED, z INT NOT NULL UNIQUE, a CHAR(1) NULL ) -- not sure why you would do this but you can
  DECLARE @v TABLE ( x INT NOT NULL, y INT NOT NULL, PRIMARY KEY ( x, y ) )   -- multi-column primary key
  

• Posting the execution plan will help diagnose this.

• Check for code preventing table variable caching as per here, here. I think dynamic SQL and proc executed WITH RECOMPILE are the only ones that affect table variables.

  DECLARE @u TABLE ( x INT )
  
  INSERT @u
  EXEC('DECLARE @t TABLE ( x INT ); INSERT INTO @t VALUES ( 1 ); SELECT x FROM @t;' )
  
  SELECT *
  FROM @u
  

• Check SQL Server Log ( Object Explorer > Management > SQL Server Logs ) for messages, eg IO warnings.

• Check Windows Event Viewer
• There have been a number of builds released since SP1. Review the CU fixes put in since SP1. It's possible there are bugs in SP1 fixed in subsequent CUs, eg FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct https://support.microsoft.com/en-us/kb/3088480
• Establish this is your cause before applying any hotfixes, although it is more important to keep up-to-date with CUs with SQL Server 2014, due the number of new features (in-memory OLTP, clustered columnstore).
• Finally, the need for one tempdb file per core is a myth and looking at your disk setup my guess is tempdb is overly fragmented. I have a nagging feeling you have one disk-head, tempdb has one filegroup, many files.

However forget what we think we know; create a test rig which reproduces your problem, and experiment with reducing the number of temp files ... start at 1, 2, 4, 6 etc gather the information, to make an evidence-based decision. Now this is the harder bit as your problem seems intermittent and you may not be able to mess with your tempdb setup, but that's how I would approach this.

Good luck. Let us know how you get on.