What is Startup Parameters in SQL Server?

Startup Parameters 

Working on a server is always something DBAs cherish. With every environment that they monitor, they want to know how applications can be optimally run on a specific server. In this constant pursuit of performance tuning, they always find unique ways of optimizing SQL Server startup. This article is around finding these parameters that most DBAs use in their daily life.

Whenever SQL Server starts, it needs three startup parameters:

• Master database data file location (-d parameter)
• Errorlog file location (-e parameter)
• Master database transaction log file location (-l parameter)

There are multiple ways to get startup parameters. If you are running SQL Server 2008 and above then we can get values of startup parameter via below T-SQL. A typical query looks like:

SELECT value_name,

       value_data,

       registry_key

FROM  sys.dm_server_registry

where value_name like 'SQLArg%'

As part of output we can see the registry key used to store these parameters:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters

This registry key depends on SQL Version and Instance Name (above the value of MSSQL12 is for SQL 2014 and SQL2014 is name of SQL Server Instance). There are situations where some additional parameters are needed. Few parameters are performance related, few are troubleshooting related. These parameters must be known to a DBA.

The right place to change startup parameter is via the SQL Server Configuration Manager. The interface to change the startup parameters has been changed in SQL Server 2012, so we are showing both these interfaces next.

Below is the Configuration Manager UI from SQL Server 2005 to SQL Server 2008 R2. We need to go to properties of SQL Server Service, go to “Advanced” tab and then click on dropdown at “Startup Parameters”. In this UI new parameters are separated by a semicolon. For example, if we want to add trace flag 1222 then we need to add “;-T1222” at the end.

As you can see the above interface is error prone and not very intuitive. This was the very reason why Startup parameters has been moved as a separate tab from SQL Server 2012.

This UI makes it easy to add/remove startup parameters. If there is a need to add a startup parameter temporarily, then that can be added while starting SQL Services via the command line. In the below examples, we would use this trick.

PARAMETER 1: -M

This is the parameter which is used to start SQL Server in single user mode. This option is generally used to restore master database. Here is an attempt to start SQL Server via “/m” parameter from command line:

Here is what we would see in SQL Server ERRORLOG

2014-11-28 16:54:24.22 Server      Registry startup parameters:

    -d E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf

    -e E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Log\ERRORLOG

    -l E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mastlog.ldf

2014-11-28 16:54:24.22 Server      Command Line Startup Parameters:

    -s "SQL2014"

    -m

…

2014-11-28 16:54:25.75 spid8s      Warning ******************

2014-11-28 16:54:25.75 spid8s      SQL Server started in single-user mode. This an informational message only. No user action is required.

PARAMETER 2: -F

This parameter is used to start SQL Server in “minimal configuration” mode. This parameter is used in situations when a DBA changed the configuration options causing SQL Server service startup failure. This startup parameter could be the only way to correct the mistakes in the SQL Server configuration.

2014-11-28 17:22:23.59 Server      Registry startup parameters:

       -d E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf

       -e E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Log\ERRORLOG

       -l E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mastlog.ldf

2014-11-28 17:22:23.59 Server      Command Line Startup Parameters:

       -s "SQL2014"

       -f

2014-11-28 17:22:23.81 Server      Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically. After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.

2014-11-28 17:22:24.04 spid8s      Warning ******************

2014-11-28 17:22:24.04 spid8s      SQL Server started in single-user mode. This an informational message only. No user action is required.

PARAMETER 3: -T

This is one of the very common parameter should be known to almost every DBA. This is a parameter which is used to enable trace flags in SQL Server. There are various documented and undocumented trace flags which are used to change the behavior of SQL Server Engine. One of the most commonly used trace flags is 1222 which is to print deadlock graph in XML format in Errorlog. There are few trace flags like 3608 which are used to troubleshoot and fix startup related problem. MSDN has list of trace flags available in SQL Server.http://msdn.microsoft.com/en-us/library/ms188396.aspx.

PARAMETER 4: -X

This is another documented trace flag to disable capture of performance counters in SQL Server Engine.

2014-11-28 17:42:27.02 Server      Registry startup parameters:

       -d E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf

       -e E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Log\ERRORLOG

       -l E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mastlog.ldf

2014-11-28 17:42:27.02 Server      Command Line Startup Parameters:

        -s "SQL2014"

        -x

…2014-11-28 17:42:27.43 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.

If we look at SQL Server Performance counters, we can notice that they are not available.

SELECT DISTINCT object_name FROM sys.dm_os_performance_counters

ORDER BY 1

Notice that parameter is lower case x. if we try with upper case X then we would get error

An invalid startup option 'X' was supplied, either from the registry or the command prompt. Correct or remove the option.

These are some of the most commonly used startup parameters that are worth a look as a DBA. Hope this post got you going in understanding some of these parameters. Using UI or via command prompt as shown above, all these techniques are powerful and quite handy if you are going to become an seasoned DBA.

Ref:

http://community.embarcadero.com/article/articles-database/1056-top-4-startup-parameters-dbas-must-know