Common Tempdb Trace Flags - Back to Basics

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	( SELECT	CONVERT(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 = 'SELECT	d.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
		FROM	sys.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 );

		SELECT	db.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 ( SELECT	TraceFlag
								FROM	#tfstatus tf
								WHERE tf.TraceFlag = 1118 ) oa
		WHERE is_mixed_page_allocation_on IS NULL
			AND db.FileGroupName IS NOT NULL
		UNION
		SELECT	db.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 ( SELECT	TraceFlag
								FROM	#tfstatus tf
								WHERE tf.TraceFlag = 1117 ) oa
		WHERE db.is_autogrow_all_files IS NULL
			AND db.FileGroupName IS NOT NULL;

		SELECT	d.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 = 'SELECT	d.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
		FROM	sys.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;

		SELECT	db.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 ( SELECT	TraceFlag
								FROM	#tfstatus tf
								WHERE tf.TraceFlag = 1118 ) oa
		WHERE is_mixed_page_allocation_on <> 0
			AND db.FileGroupName IS NOT NULL
		UNION
		SELECT	db.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 ( SELECT	TraceFlag
								FROM	#tfstatus tf
								WHERE tf.TraceFlag = 1117 ) oa
		WHERE db.is_autogrow_all_files <> 1
			AND db.FileGroupName IS NOT NULL;

		SELECT	d.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;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

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;

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 ( SELECT CONVERT(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 = 'SELECT d.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

FROM sys.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 );

SELECT db.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 ( SELECT TraceFlag

FROM #tfstatus tf

WHERE tf.TraceFlag = 1118 ) oa

WHERE is_mixed_page_allocation_on IS NULL

AND db.FileGroupName IS NOT NULL

UNION

SELECT db.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 ( SELECT TraceFlag

FROM #tfstatus tf

WHERE tf.TraceFlag = 1117 ) oa

WHERE db.is_autogrow_all_files IS NULL

AND db.FileGroupName IS NOT NULL;

SELECT d.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 = 'SELECT d.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

FROM sys.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;

SELECT db.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 ( SELECT TraceFlag

FROM #tfstatus tf

WHERE tf.TraceFlag = 1118 ) oa

WHERE is_mixed_page_allocation_on <> 0

AND db.FileGroupName IS NOT NULL

UNION

SELECT db.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 ( SELECT TraceFlag

FROM #tfstatus tf

WHERE tf.TraceFlag =