Often we run into situations where there is a need to add a trace flag or a startup parameter other than the default startup parameters that SQL Server uses. What you may notice sometimes is that even after adding these parameters/flags, the desired behavior from those parameters/flags does not occur. We will use the following parameter and trace flag to demonstrate the reasons but this applies to any combination of trace flags and parameters.“-g <memory in MB>” – This is used to expand the Mem-To-Leave region of SQL Server memory on x86 systems when you observe that your SQL Server is running out of memory in this region (256MB by default). You can read more about the purpose of –g here. “-T1118” – This is used to reduce tempdb contention. More details at this earlier blog post.All startup parameters are stored in Windows Registry and read by SQL Server every time it is started. For this purpose, they need to be added via SQL Server Configuration Manager (SSCM) so that they can be persisted and used every time SQL Server is started. NOTE: Depending on your situation (example: tempdb contention), even if the trace flags are added correctly, you may still continue to experience the same problem. This blog post does not apply to such situations. Upon further investigation, we have observed that depending on how the parameter is added from SSCM, the registry keys may not be created correctly resulting in SQL Server startup code skipping these parameters. To add startup parameters correctly from SSCM, follow these guidelines: 1. Add any new parameters after the first 3 default parameters –d, -e, -l. –d, -e and -l need to be the first 3 (and in that order) for SQL Server to start correctly.
2. Specify the “;” delimiter between each parameter. SSCM uses ”;” as the delimiter between parameters.
3. Do NOT use a space after the delimiter and the parameter identifier (which is the “-“ character). Not following these guidelines will result in these being created incorrectly in registry. As a result, SQL Server startup code will not be able to parse these parameters correctly.Read more: CSS SQL Server Engineers
2. Specify the “;” delimiter between each parameter. SSCM uses ”;” as the delimiter between parameters.
3. Do NOT use a space after the delimiter and the parameter identifier (which is the “-“ character). Not following these guidelines will result in these being created incorrectly in registry. As a result, SQL Server startup code will not be able to parse these parameters correctly.Read more: CSS SQL Server Engineers