Tempdb settings in SQL Server 2016

A nice little error log feature that I noticed in SQL Server 2016 regarding tempdb.

Tempdb, a system database in SQL Server, some call it the “public toilet” but honestly it is where temporary user objects are created, internal objects are created by the database engine and row versioning relies upon it.

What I like about SQL Server 2016 is that it watches out for a bad configuration, if you are new to the SQL Server world then a bad tempdb configuration is quite possible but you will be told quite clearly about it within the error log.

I setup a bad configuration just to prove the point.

tempdb

The image shows all sorts of mess here. First I have 3 data files with different initial sizes and a mixture of percentage based auto growth and X_MB increments.  This totally goes against common advice of using correct initial /growth sizes and the number of files is just random for a 4 core system.

With that being said, check the error log:

The tempdb database has 3 data file(s). The tempdb database data files are not configured with the same initial size and autogrowth settings. To reduce potential allocation contention, the initial size and autogrowth of the files should be same.

What happens if I set a more sensible setting? There will be no warning element to the message.

tempdb1

Starting up database ‘tempdb’. The tempdb database has 2 data file(s).

Slightly more respectable I guess. Thanks Microsoft for watching my back.

One thought on “Tempdb settings in SQL Server 2016

  1. Pingback: Tempdb settings in SQL Server 2016 - SQL Server Blog - SQL Server - Toad World

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s