In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so.
In this example, we will want to configure SQL Server to enable a few trace flags (TF) when SQL Server starts. Specifically, let’s set trace flags 1117 and 1118 so that they are enabled when SQL Server starts up, and enable them now. Additionally, we have trace flags 1204 and 1222 now enabled on some servers, and we want to disable those (since we have the deadlocks being captured in the system health XE, we don’t need them in the error log also). We also don’t want to force a restart of the SQL Server services.
Let’s start off with how the environment is configured. The below screen shot (from SQL Server Configuration Manager) shows the startup parameters now in use:
Notice that this instance has only TF 1204 enabled.
Retrieving the Startup Parameters
The registry key “HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters” stores the startup parameters. Especially relevant is the “MSSQL12.SQL2014” portion, which will vary depending on the SQL Server version and your instance name. However, if you remember from the earlier post, there are instance-aware versions of the registry extended stored procedures that automatically handle this. For the instance-aware versions, use this registry key instead: “HKLM\Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters”.
To get all the startup parameters, we would use the xp_instance_regenumvalues extended stored procedure. As noted in the earlier post, this produces a result set for each value. Therefore, this example loads them into a table variable and then displays this table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @RegHive VARCHAR(50), @RegKey VARCHAR(100); SET @RegHive = 'HKEY_LOCAL_MACHINE'; SET @RegKey = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters'; -- Get all of the arguments / parameters when starting up the service. DECLARE @SQLArgs TABLE ( Value VARCHAR(50), Data VARCHAR(500), ArgNum AS CONVERT(INTEGER, REPLACE(Value, 'SQLArg', ''))); INSERT INTO @SQLArgs EXECUTE master.sys.xp_instance_regenumvalues @RegHive, @RegKey; SELECT * FROM @SQLArgs; |
This script could have omitted the two variables, but since I’m planning on using them for updating the registry, I decided to store their locations in variables and just use those instead. Additionally, since I’ll be adding to and removing from this list and I want to keep the parameters in the same order, I have added a computed column to calculate the argument number. Running this script shows all the startup parameters:
This matches the earlier screen shot. Running the following will show the running trace flags:
|
1 |
DBCC TRACESTATUS; |
Enabling / disabling trace flags
The next step that needs performed is to enable trace flags 1117 and 1118, and to disable trace flags 1204 and 1222. The commands DBCC TRACEON and DBCC TRACEOFF are used to accomplish this. These commands take a comma-delimited list of trace flags. To perform their action at a global level, use the optional flag of “-1”. These commands set and verify the trace flags:
|
1 2 3 |
DBCC TRACEOFF (1204, 1222, -1); DBCC TRACEON (1117, 1118, -1); DBCC TRACESTATUS; |
Setting trace flags to handle the instance restarting
This will start and stop the trace flags immediately; however this has not changed their startup state when the instance next restarts. Consequently, we need to modify the registry to remove TF 1204 and to add TFs 1117 and 1118. The following statements will override SQLArg3 with the TF 1117, and add TF 1118 to SQLArg4. Remember that writing to the registry requires access for the service account to the specified keys. Optionally, the service account can be a member of the Local Administrators group. In order for SQL Server to pick up this change, the service must be restarted after these changes have been made.
|
1 2 3 4 |
EXECUTE master.sys.xp_instance_regwrite @RegHive, @RegKey, 'SQLArg3', 'REG_SZ', '-T1117'; EXECUTE master.sys.xp_instance_regwrite @RegHive, @RegKey, 'SQLArg4', 'REG_SZ', '-T1118'; |
Cleaning up the registry
If we remove more values than what were added, we might need to call master.sys.xp_instance_regdeletevalue. Additionally, if we are only deleting some values, there might be gaps left in the argument number which need eliminated. The following script enables and/or disables current trace flags, modifies the registry for the startup parameters, and handles the registry consolidation / cleanup of this key.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
/****************************************************************************** Globally enable / disable the specified trace flags. Use DBCC TRACEON/TRACEOFF to enable disable globally trace flags, then adjust the SQL Server instance startup parameters for these trace flags. SQL Server startup parameters are stored in the registry at: HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters To use the xp_instance_reg... XPs, use: HKLM\Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters. To use: 1. Add the Trace Flags that you want modified to the @TraceFlags table variable. 2. Set the @DebugLevel variable to 1 to see what will happen on your system first. 3. When satisified what will happen, set @DebugLevel to 0 to actually execute the statements. ******************************************************************************** MODIFICATION LOG ******************************************************************************** 2016-08-03 WGS Initial Creation. *******************************************************************************/ SET NOCOUNT ON; -- Declare and initialize variables. -- To use with SQL 2005, cannot set the variables in the declare statement. DECLARE @MaxValue INTEGER, @SQLCMD VARCHAR(MAX), @RegHive VARCHAR(50), @RegKey VARCHAR(100), @DebugLevel TINYINT; SET @RegHive = 'HKEY_LOCAL_MACHINE'; SET @RegKey = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters'; SET @DebugLevel = 0; -- only makes changes if set to zero! -- Add the trace flags that you want changed here. -- If enable = 1, DBCC TRACEON will be run; if enable = 0 then DBCC TRACEOFF will be run. -- If enable_on_startup = 1, then this TF will be added to start up on service restart; -- If enable_on_startup - 0, then this TF will be removed from starting up service restart DECLARE @TraceFlags TABLE ( TF INTEGER, enable BIT, enable_on_startup BIT, TF2 AS '-T' + CONVERT(VARCHAR(15), TF) ); INSERT INTO @TraceFlags (TF, enable, enable_on_startup) -- To work with SQL 2005, cannot use a table value constructor. -- So, use SELECT statements with UNION ALL for each TF to modify. SELECT 1117, 1, 1 UNION ALL SELECT 1118, 1, 1 UNION ALL SELECT 1204, 0, 0 UNION ALL SELECT 1222, 0, 0; -- Get all of the arguments / parameters when starting up the service. DECLARE @SQLArgs TABLE ( Value VARCHAR(50), Data VARCHAR(500), ArgNum AS CONVERT(INTEGER, REPLACE(Value, 'SQLArg', ''))); INSERT INTO @SQLArgs EXECUTE master.sys.xp_instance_regenumvalues @RegHive, @RegKey; -- Get the highest argument number that is currently set SELECT @MaxValue = MAX(ArgNum) FROM @SQLArgs; RAISERROR('MaxValue: %i', 10, 1, @MaxValue) WITH NOWAIT; -- Disable specified trace flags SELECT @SQLCMD = 'DBCC TRACEOFF(' + STUFF((SELECT ',' + CONVERT(VARCHAR(15), TF) FROM @TraceFlags WHERE enable = 0 ORDER BY TF FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,1,'') + ', -1);' IF @DebugLevel = 0 EXECUTE (@SQLCMD); RAISERROR('Disable TFs Command: "%s"', 10, 1, @SQLCMD) WITH NOWAIT; -- Enable specified trace flags SELECT @SQLCMD = 'DBCC TRACEON(' + STUFF((SELECT ',' + CONVERT(VARCHAR(15), TF) FROM @TraceFlags WHERE enable = 1 ORDER BY TF FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,1,'') + ', -1);' IF @DebugLevel = 0 EXECUTE (@SQLCMD); RAISERROR('Enable TFs Command: "%s"', 10, 1, @SQLCMD) WITH NOWAIT; DECLARE cSQLParams CURSOR LOCAL FAST_FORWARD FOR WITH cte AS ( -- Current arguments, with new TFs added at the end. Get a row number to sort by. SELECT *, ROW_NUMBER() OVER (ORDER BY ISNULL(ArgNum, 999999999), TF) - 1 AS RN FROM @SQLArgs arg FULL OUTER JOIN @TraceFlags tf ON arg.Data = tf.TF2 ), cte2 AS ( -- Use the row number to calc the SQLArg# for new TFs. -- Use the original Value (SQLArg#) and Data for all rows if possible, -- Otherwise use the calculated SQLArg# and the calculated TF2 column. -- Only get the original non-TF-matched parameters, and the TFs set to be enabled -- (existing startup TFs not in @TraceFlags are left alone). SELECT ca.Value, ca.Data -- in case any TFs are removed, calculate new row numbers in order -- to renumber the SQLArg values , ROW_NUMBER() OVER (ORDER BY RN) - 1 AS RN2 FROM cte -- Again, for SQL 2005, use SELECT statement instead of VALUES. CROSS APPLY (SELECT ISNULL(Value, 'SQLArg' + CONVERT(VARCHAR(15), RN)), ISNULL(Data, TF2) ) ca(Value, Data) WHERE ISNULL(enable_on_startup, 1) = 1 -- ISNULL handles non-TF parameters ) -- The first three parameters are the location of the errorlog directory, -- and the master database file locations. Ignore these. -- This returns the remaining parameters that should be set. -- Also return the highest number of parameters, so can determine if any need to be deleted. SELECT 'SQLArg' + CONVERT(VARCHAR(15), RN2) AS Value, Data, MAX(RN2) OVER () AS MaxRN2 FROM cte2 WHERE RN2 > 2 ORDER BY RN2; DECLARE @Value VARCHAR(50), @Data VARCHAR(500), @MaxRN2 INTEGER; OPEN cSQLParams; FETCH NEXT FROM cSQLParams INTO @Value, @Data, @MaxRN2; WHILE @@FETCH_STATUS = 0 BEGIN IF @DebugLevel = 0 EXECUTE master.sys.xp_instance_regwrite @RegHive, @RegKey, @Value, 'REG_SZ', @Data; RAISERROR('EXECUTE master.sys.xp_instance_regwrite ''%s'', ''%s'', ''%s'', ''REG_SZ'', ''%s''', 10, 1, @RegHive, @RegKey, @Value, @Data) WITH NOWAIT; FETCH NEXT FROM cSQLParams INTO @Value, @Data, @MaxRN2; END; CLOSE cSQLParams; DEALLOCATE cSQLParams; -- In case deleting more TFs than added, there may be extra SQLArg values left behind. -- Need to delete the extras now. WHILE @MaxValue > @MaxRN2 BEGIN SET @Value = 'SQLArg' + CONVERT(VARCHAR(15), @MaxValue); IF @DebugLevel = 0 EXECUTE master.sys.xp_instance_regdeletevalue @RegHive, @RegKey, @Value; RAISERROR('EXECUTE master.sys.xp_instance_regdeletevalue ''%s'', ''%s'', ''%s''', 10, 1, @RegHive, @RegKey, @Value) WITH NOWAIT; SET @MaxValue = @MaxValue - 1; END; |
After running, you can see that the changes have in fact been changed:
Nicely Done!!
Reply
In SQL Server 2016, the trace flag 1117 which controls simultaneous growth of the TempDB files, and trace flag 1118 which reduces page latch contention, are now enabled by default.
Reply
Hi Shah, MODIFY FILEGROUP AUTOGROW_ALL_FILES statement. See //docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options and //docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options for details on these statements.
Thanks for your comment. Actually, the functionality of these is enabled by default in the tempdb database. Those trace flags are now obsolete and no longer do anything starting with SQL Server 2016. Both of these are now database level settings (whereas the trace flags were server level and applied to all databases). Mixed page allocations (TF 1118) are set with the ALTER DATABASE SET MIXED_PAGE_ALLOCATIONS ON/OFF statement, and simultaneous growth of all files in the same filegroup (TF 1117) are set with the ALTER DATABASE
Reply
Every once in a while, I have to do startup checks and sets, especially when setting up new servers and automation makes for “Martha Stuart Moments”. I never did thank you for this post and, although years late, better late than never.
So, with that being said, THANK YOU! Keep hitting them hard and far, Wayne!
Reply
As a passing note, MS making it so you CANNOT override the settings on TempDB for the equivalent of TF 1117 really put the screws to me in the area of temporary work especially since they still haven’t fixed the bug with SET IDENTITY_INSERT ON and how it beats the hell out of TempDB even when a sort isn’t required.
This is yet another example of MS trying to make things “idiot proof”. They’ve forgotten that if you make something “idiot proof”, only idiots will use it. 😀
Reply