Once in a while I come across something that sounds fun or interesting and decide to dive a little deeper into it. That happened to me recently and caused me to preempt my scheduled post and work on writing up something entirely different. Why? Because this seemed like fun and useful.

So what is it I am yammering on about that was fun?

I think we can probably concede that there are some best practices flying around in regards to the configuration of tempdb. One of those best practices is in regards to two trace flags within SQL Server. These trace flags are 1117 and 1118. Here is a little bit of background on the trace flags and what they do.

A caveat I have now for the use of trace flags is that I err on the same side as Kendra (author of the article just mentioned). I don’t generally like to enable trace flags unless it is very warranted for a very specific condition. As Kendra mentions, TF 1117 will impact more than just the tempdb data files. So use that one with caution.

Ancient Artifacts

With the release of SQL Server 2016, these trace flags were rumored to be a thing of the past and hence completely unnecessary. That is partially true. The trace flag is unneeded and SQL 2016 does have some different behaviors, but does that mean you have to do nothing to get the benefits of these Trace Flags as implemented in 2016?

As it turns out, these trace flags no longer do what they did in previous editions. SQL Server now pretty much has it baked into the product. Buuuuut, do you have to do anything slightly different to make it work? This was something I came across while reading this post and wanted to double check everything. After all, I was also under the belief that it was automatically enabled. So let’s create a script that checks these things for me.

/*
http://blog.waynesheffield.com/wayne/archive/2017/09/registry-sql-server-startup-parameters/
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
*/USE tempdb;
GO
DECLARE @ServerMajorVersion DECIMAL(4, 2)
, @TSQL VARCHAR(2048);
IF ( SELECT OBJECT_ID('tempdb..#dbsettings')) IS NOT NULL
BEGIN
DROP TABLE #dbsettings;
END;
IF ( SELECT OBJECT_ID('tempdb..#tfstatus')) IS NOT NULL
BEGIN
DROP TABLE #tfstatus;
END;
CREATE TABLE #tfstatus
(
TraceFlag INT
, Status TINYINT
, Global TINYINT
, Session TINYINT
);
CREATE TABLE #dbsettings
(
DBName VARCHAR(128)
, DataFileName VARCHAR(128)
, physical_name VARCHAR(1024)
, FileGroupName VARCHAR(256)
, is_mixed_page_allocation_on TINYINT
, is_autogrow_all_files TINYINT
, is_persistent_log_buffer TINYINT
);
SELECT@ServerMajorVersion = CONVERT(
DECIMAL(4, 2)
, PARSENAME(dt.fqn, 4) + '.'
+ PARSENAME(dt.fqn, 3))
FROM( SELECTCONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))) dt(fqn);
SELECT@ServerMajorVersion;
INSERT INTO #tfstatus ( TraceFlag
, Status
, Global
, Session )
EXECUTE ( 'DBCC TRACESTATUS' );
IF @ServerMajorVersion < 13.00
BEGIN
SET @TSQL = 'SELECTd.name AS DBName
, df.name AS DataFileName
, df.physical_name
, fg.name as FileGroupName
, NULL AS is_mixed_page_allocation_on
, NULL AS is_autogrow_all_files
, NULL AS is_persistent_log_buffer
FROMsys.databases d
INNER JOIN sys.master_files mf
ON d.database_id = mf.database_id
INNER JOIN sys.database_files df
ON mf.file_id = df.file_id
AND mf.name = df.name COLLATE Latin1_General_CI_AI
LEFT OUTER JOIN sys.filegroups fg
ON df.data_space_id = fg.data_space_id;';
INSERT INTO #dbsettings ( DBName
, DataFileName
, physical_name
, FileGroupName
, is_mixed_page_allocation_on
, is_autogrow_all_files
, is_persistent_log_buffer )
EXECUTE ( @TSQL );
SELECTdb.DBName
, db.DataFileName
, db.physical_name
, CASE WHEN db.is_mixed_page_allocation_on IS NULL
AND oa.TraceFlag IS NULL THEN
'Mixed Page Alloc Not Enabled'
END AS Feature
, CASE WHEN db.is_mixed_page_allocation_on IS NULL
AND oa.TraceFlag IS NULL THEN
'DBCC TRACEON (1118, -1);'
END AS EnableStatement
FROM#dbsettings db
OUTER APPLY ( SELECTTraceFlag
FROM#tfstatus tf
WHERE tf.TraceFlag = 1118 ) oa
WHERE is_mixed_page_allocation_on IS NULL
AND db.FileGroupName IS NOT NULL
UNION
SELECTdb.DBName
, db.DataFileName
, db.physical_name
, CASE WHEN db.is_autogrow_all_files IS NULL
AND oa.TraceFlag IS NULL THEN
'Even Data File Growth Not Enabled'
END AS Feature
, CASE WHEN db.is_autogrow_all_files IS NULL
AND oa.TraceFlag IS NULL THEN
'DBCC TRACEON (1117, -1);'
END AS EnableStatement
FROM#dbsettings db
OUTER APPLY ( SELECTTraceFlag
FROM#tfstatus tf
WHERE tf.TraceFlag = 1117 ) oa
WHERE db.is_autogrow_all_files IS NULL
AND db.FileGroupName IS NOT NULL;
SELECTd.DBName
, d.DataFileName
, d.physical_name
, d.is_mixed_page_allocation_on
, d.is_autogrow_all_files
, d.is_persistent_log_buffer
FROM#dbsettings d;
END;
IF @ServerMajorVersion >= 13.00
BEGIN
SET @TSQL = 'SELECTd.name AS DBName
, df.name AS DataFileName
, df.physical_name
, fg.name as FileGroupName
, d.is_mixed_page_allocation_on
, fg.is_autogrow_all_files
, mf.is_persistent_log_buffer
FROMsys.databases d
INNER JOIN sys.master_files mf
ON d.database_id = mf.database_id
INNER JOIN sys.database_files df
ON mf.file_id = df.file_id
AND mf.name = df.name COLLATE Latin1_General_CI_AI
LEFT OUTER JOIN sys.filegroups fg
ON df.data_space_id = fg.data_space_id;';
INSERT INTO #dbsettings ( DBName
, DataFileName
, physical_name
, FileGroupName
, is_mixed_page_allocation_on
, is_autogrow_all_files
, is_persistent_log_buffer )
EXECUTE ( @TSQL );

--just to check for giggles
--SELECT*
--FROM#tfstatus;
SELECTdb.DBName
, db.DataFileName
, db.physical_name
, CASE WHEN db.is_mixed_page_allocation_on = 1 THEN
'Mixed Page Alloc Is Enabled'
END AS Feature
, CASE WHEN db.is_mixed_page_allocation_on = 1 THEN
'ALTER DATABASE [' + db.DBName
+ '] SET MIXED_PAGE_ALLOCATIONS OFF;'
END AS EnableStatement
FROM#dbsettings db
OUTER APPLY ( SELECTTraceFlag
FROM#tfstatus tf
WHERE tf.TraceFlag = 1118 ) oa
WHERE is_mixed_page_allocation_on <> 0
AND db.FileGroupName IS NOT NULL
UNION
SELECTdb.DBName
, db.DataFileName
, db.physical_name
, CASE WHEN db.is_autogrow_all_files IS NULL
AND oa.TraceFlag IS NULL THEN
'Even Data File Growth Not Enabled'
END AS Feature
, CASE WHEN db.is_autogrow_all_files = 0 THEN
'ALTER DATABASE [' + db.DBName
+ '] MODIFY FILEGROUP [' + FileGroupName
+ '] AUTOGROW_ALL_FILES;'
END AS EnableStatement
FROM#dbsettings db
OUTER APPLY ( SELECTTraceFlag
FROM#tfstatus tf
WHERE tf.TraceFlag = 1117 ) oa
WHERE db.is_autogrow_all_files <> 1
AND db.FileGroupName IS NOT NULL;
SELECTd.DBName
, d.DataFileName
, d.physical_name
, d.FileGroupName
, d.is_mixed_page_allocation_on
, d.is_autogrow_all_files
, d.is_persistent_log_buffer
FROM#dbsettings d;
IF EXISTS ( SELECT*
FROM#tfstatus
WHERE TraceFlag IN ( 1117, 1118 ))
BEGIN
SELECT'One or Both Trace Flags (1117 & 1118) is/are enabled and has NO EFFECT. Please Disable' AS Warning
, 'DBCC TRACEOFF (1117, 1118, -1);' AS DisableStatement;
END;
END;

Holy cannoli batman – that is more than a simple script, right? Well, it may be a bit of overkill. I wanted it to work for version before and after and including SQL Server 2016 (when these sweeping changes went into effect). You see, I am checking for versions where the TF was required to make the change and also for versions after the change where the TF has no effect. In 2016 and later, these settings are database scoped and the TF is unnecessary.

The database scoped settings can actually be queried in 2016 more specifically with the following query.

SELECTd.name AS DBName
, df.name AS DataFileName
, df.physical_name
, fg.name as FileGroupName
, d.is_mixed_page_allocation_on
, fg.is_autogrow_all_files
, mf.is_persistent_log_buffer
FROMsys.databases d
INNER JOIN sys.master_files mf
ON d.database_id = mf.database_id
INNER JOIN sys.database_files df
ON mf.file_id = df.file_id
AND mf.name = df.name COLLATE Latin1_General_CI_AI
LEFT OUTER JOIN sys.filegroups fg
ON df.data_space_id = fg.data_space_id;

In this query, I am able to determine if mixed_page_allocations and if is_autogrow_all_files are enabled. These settings can be retrieved from sys.databases and sys.filegroups respectively. If I run this query on a server where the defaults were accepted during the install, I would see something like the following.

You can see here, the default settings on my install show something different than the reported behavior. While autogrow all files is enabled, mixed_page_allocations is disabled. This matches what we expect to see by enabling the Trace Flags 1117 and 1118 – for the tempdb database at least. If I look at a user database, I will find that mixed pages is disabled by default still but that autogrow_all_files is also disabled.

In this case, you may or may not want a user database to have all data files grow at the same time. That is a great change to have implemented in SQL Server with SQL 2016. Should you choose to enable it, you can do so on a database by database basis.

As for the trace flags? My query checks to see if maybe you enabled them on your instance or if you don’t have them enabled for the older versions of SQL Server. Then the script generates the appropriate action scripts and allows you to determine if you want to run the generated script or not. And since we are changing trace flags (potentially) I recommend that you also look at this article of mine that discusses how to audit the changing of trace flags. And since that is an XEvent based article, I recommend freshening up on XEvents with this series too!

The Wrap

In this article I have introduced you to some basics in regards to default behaviors and settings in tempdb along with some best practices. It is advisable to investigate from time to time some of these recommendations and confirm what we are really being told so we can avoid confusion and mis-interpretation.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.