Archive
SQL DBA – Configure multiple TempDB Database Files while installing SQL Server 2016 (new feature)
SQL Server 2016 allows you to scale up your database with Enhanced Database Caching, using support for multiple TempDB files per instance for multi-core environments. This reduces metadata- and allocation contention for TempDB workloads, improving performance and scalability.
Now you can configure the number of TempDB Database Files during the installation of a new Instance. While installation process in the Database Engine Configuration page you will see an extra option to set number of TempDB files.
Or, you can specify the number of files by using the new command line parameter: /SQLTEMPDBFILECOUNT
setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS /FEATURES="SqlEngine" /INSTANCENAME="SQL15" .. /SQLTEMPDBDIR="D:\tempdb" /SQLTEMPDBFILECOUNT="4"
While installing via UI the label besides the Input Control below mentions: “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.
Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.
So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:
Table variables are not stored in memory, but in tempdb
Table-Variables just like Temp-Tables also gets stored in tempdb.
This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory.
–> To prove this I executed following code:
-- Batch #1 SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #2 CREATE TABLE #tempTab (j INT) INSERT INTO #tempTab VALUES (1) SELECT * FROM #tempTab SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #3 DECLARE @tabVar TABLE (i INT) INSERT INTO @tabVar VALUES (1) SELECT * FROM @tabVar SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO DROP TABLE #tempTab GO
Batch #1: Excuting first batch gives me 0 rows, means no tables exists in tempdb system database.
Batch #2: The second batch of stmt gives me 1 row with following table name:
#tempTab_____________________________________________________________000000019D39
Batch #3: and the third batch of stmt gives me 2 rows with following table name:
#tempTab_____________________________________________________________000000019D39 #0C4F413A
This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.
–> Check the full demo here:
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 