Dynamics NAV Performance – TempDB

SQL Server uses a shared workspace database called tempdb to handle a lot of system tasks (as well as user-created temp tables.)

TempDB by default is made of 1 data file and 1 log file (One log file is just fine.)

Microsoft advise that you create multiple TempDB data files, each with a fixed minimum size of 1 GB and a fixed auto-grow if 1 GB instead of the default 10% setting. The number of files should be kept equal to the number of logical processors up to 8 logical CPUs maximum.

On a busy SQL Server with only one TempDB you can end up waiting a long time to get your ‘latch’ on a system page in tempdb. This can make your queries run very slowly in some cases.

tempdb

The files for TempDB should be on a drive separate from the SQL Data and Log files.

(NOTE: Each TempDB file DOES NOT need to be on its own drive they just need to be separated from all SQL Data and Log files)

 

 

Here’s a sample script but sure to customize it to reflect the proper size and file path for your environment:

  1. /* Re-sizing TempDB to 8 GB */
  2. USE [master];
  3. GO
  4. alter database tempdb modify file (NAME=’tempdb‘, SIZE = 1GB, FILEGROWTH = 1GB);
  5. GO
  6. /* Adding three additional files */
  7. USE [master];
  8. GO
  9. ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev2′, FILENAME = N’T:\MSSQL\DATA\tempdb2.ndf’ , SIZE = 1GB , FILEGROWTH = 1GB);
  10. ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev3′, FILENAME = N’T:\MSSQL\DATA\tempdb3.ndf’ , SIZE = 1GB , FILEGROWTH = 1GB);
  11. ALTER DATABASE [tempdb] ADD FILE (NAME = N’tempdev4′, FILENAME = N’T:\MSSQL\DATA\tempdb4.ndf’ , SIZE = 1GB , FILEGROWTH = 1GB);
  12. GO

 

Published by Tristan

Technical infrastructure manager/consultant with over 12 years of experience in planning, deploying and managing complex IT solutions across a range of technology sectors.

Join the Conversation

1 Comment

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: