Showing posts with label TempDB. Show all posts
Showing posts with label TempDB. Show all posts
Saturday, October 31, 2015
TempDB Database changes in SQL Server 2016
According to Books On Line, there are several changes made in SQL Server 2016 in regards to TempDB. Here is what Books On Line has on the topic
Here is what I saw in the output
namefileidfilenamefilegroup sizemaxsizegrowthusage
tempdev1C:\DATA\tempdb.mdfPRIMARY8192 KBUnlimited65536 KBdata only
templog2C:\DATA\templog.ldfNULL8192 KBUnlimited65536 KBlog only
temp23C:\DATA\tempdb_mssql_2.ndfPRIMARY8192 KBUnlimited65536 KBdata only
temp34C:\DATA\tempdb_mssql_3.ndfPRIMARY8192 KBUnlimited65536 KBdata only
temp45C:\DATA\tempdb_mssql_4.ndfPRIMARY8192 KBUnlimited65536 KBdata only
As you can see, there are 4 data files, when I looked at my SQL Server 2014 instance which was installed with defaults, there is only 1 data file. Another difference is that SQL Server 2014 uses 10% growth as default while SQL Server 2016 uses 65536 KB
This is a good change for all the casual installs by people who just want to experiment and learn, however most shops I would think already would have changed TempDB to have more than 1 file. But then again maybe Microsoft noticed that a lot of support calls that they are getting have only 1 data file for TempDB and thus they decided to implement this change.
There are several enhancements to TempDB:
- Trace Flags 1117 and 1118 are not required for tempdb anymore. If there are multiple tempdb database files all files will grow at the same time depending on growth settings. In addition, all allocations in tempdb will use uniform extents.
- By default, setup adds as many tempdb files as the CPU count or 8, whichever is lower.
- During setup, you can configure the number of tempdb database files, initial size, autogrowth and directory placement using the new UI input control on the Database Engine Configuration - TempDB section of SQL Server Installation Wizard.
- The default initial size is 8MB and the default autogrowth is 64MB.
- You can specify multiple volumes for tempdb database files. If multiple directories are specified tempdb data files will be spread across the directories in a round-robin fashion.
I decided to check this out, I downloaded and installed SQL Server 2016 Community Technology Preview 3.0 During the install I did not make any changes to TempDB
Once the install was done, I executed the following to see how many files TempDB had on my laptop
exec sp_helpdb 'tempdb'
Here is what I saw in the output
namefileidfilenamefilegroup sizemaxsizegrowthusage
tempdev1C:\DATA\tempdb.mdfPRIMARY8192 KBUnlimited65536 KBdata only
templog2C:\DATA\templog.ldfNULL8192 KBUnlimited65536 KBlog only
temp23C:\DATA\tempdb_mssql_2.ndfPRIMARY8192 KBUnlimited65536 KBdata only
temp34C:\DATA\tempdb_mssql_3.ndfPRIMARY8192 KBUnlimited65536 KBdata only
temp45C:\DATA\tempdb_mssql_4.ndfPRIMARY8192 KBUnlimited65536 KBdata only
As you can see, there are 4 data files, when I looked at my SQL Server 2014 instance which was installed with defaults, there is only 1 data file. Another difference is that SQL Server 2014 uses 10% growth as default while SQL Server 2016 uses 65536 KB
This is a good change for all the casual installs by people who just want to experiment and learn, however most shops I would think already would have changed TempDB to have more than 1 file. But then again maybe Microsoft noticed that a lot of support calls that they are getting have only 1 data file for TempDB and thus they decided to implement this change.
Subscribe to:
Posts (Atom)