In SQL Server there are a couple of ways to set startup parameters:
- SQL Server Configuration Manager
- Command Prompt Net start
Of these only one can be relied upon as a permanent option. That is the Configuration Manager. I have been toying with startup parameters recently as part of my interest in Fast Track Data Warehousing. I have noticed there are a couple of things you really need to be mindful of when setting these values.
To illustrate these little gotchas I thought it would be worth while stepping through a scenario whilst configuring the –E startup parameter.
How To : Enable –E
Below are the steps you need to follow:
Step 1 : Fire Up SQL Server Configuration Manager
Start >> All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager
Step 2: Right Click on SQL Server Service and Select Advanced Tab
Step 3 : Click on the drop down arrow and update the Start-up Parameter property
To set the –E parameter tack ;-E to the end of the list of startup parameters
Step 4 : Click on Apply and then Clear the warning box
Click Apply
Click OK
Step 5 : Click on the Logon Tab and Restart SQL Server by clicking on the “Restart” button
So What’s the Problem? Where are the gotchas?
Assuming you followed Steps above EXACTLY then nothing will be wrong and –E will work perfectly well for you.
However, gotchas do exist. Look back at Step 3
This is where the gotchas are. The Startup Parameter property box is very sensitive.
- If you put your new parameter at the end of the list you must precede your new option with a semi-colon
- If you put your parameter at the beginning of the list you must append the option with a semi-colon (documented but not clearly)
- You must NOT have any spaces between the semi-colon and the hyphen.
The last one is the real killer
If you put a space between the semi-colon and the hyphen then the parameter will be ignored.
This works:
This does not:
SQL Server interprets the startup parameter as “space-E” not “–E”. It will therefore not have the desired effect.
The SQL Server Log
To compound the matter the SQL Log shows something quite interesting.
With ;-E
2010-04-20 09:27:36.91 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
2010-04-20 09:27:36.91 Server (c) 2005 Microsoft Corporation.
2010-04-20 09:27:36.91 Server All rights reserved.
2010-04-20 09:27:36.91 Server Server process ID is 1456.
2010-04-20 09:27:36.91 Server System Manufacturer: 'Dell Inc.', System Model: 'Latitude D830'.
2010-04-20 09:27:36.91 Server Authentication mode is MIXED.
2010-04-20 09:27:36.91 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-04-20 09:27:36.91 Server This instance of SQL Server last reported using a process ID of 5876 at 20/04/2010 09:27:30 (local) 20/04/2010 08:27:30 (UTC). This is an informational message only; no user action is required.
2010-04-20 09:27:36.91 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-E
2010-04-20 09:27:36.92 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
Illustration of ;-E
I will use the great Internals Viewer to demonstrate the impact of setting this parameter.
In the graphic above the blueish boxes represent data pages that have been allocated from three extents. It can be clearly seen that all extents have been allocated from a single file.
Without going into the details here (please see my next post to get the juice on –E) the default behaviour of SQL Server has changed. Proportional fill has not occurred.
With ;space-E
2010-04-20 09:47:19.86 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
2010-04-20 09:47:19.86 Server (c) 2005 Microsoft Corporation.
2010-04-20 09:47:19.86 Server All rights reserved.
2010-04-20 09:47:19.86 Server Server process ID is 1604.
2010-04-20 09:47:19.86 Server System Manufacturer: 'Dell Inc.', System Model: 'Latitude D830'.
2010-04-20 09:47:19.86 Server Authentication mode is MIXED.
2010-04-20 09:47:19.86 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-04-20 09:47:19.86 Server This instance of SQL Server last reported using a process ID of 1456 at 20/04/2010 09:47:13 (local) 20/04/2010 08:47:13 (UTC). This is an informational message only; no user action is required.
2010-04-20 09:47:19.86 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-E
2010-04-20 09:47:19.87 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
Illustration of ;space-E
As you can see from the graphic below the purple boxes representing data pages are now spread in three different data files. Three extents have again been allocated and the proportional fill algorithm has spread this allocation evenly across the files.
So what does the SQL Server log show?
What’s the difference in the log? Almost nothing! One could be very easily led to think that the –E startup parameter had been set as it shows in both logs. You have to look pretty closely to see that the second log –E is mis-aligned.
However, the actual difference in behaviour is significant.
In the first instance SQL Server behaviour has been altered. In the second the change has no impact on SQL Server. However, by looking at the log one could be led to believe that –E had in fact been set. Confusing? I think so.
SQL Server Log Conclusions
Discovering this issue has led me to an interesting conclusion.
The SQL Server Log is a statement of intent not a statement of fact.
I was expecting the log to tell me to tell me that –E had not been enabled when I had configured ;space-E. It did not. It tells me that SQL Server was started with space-E.
The log is therefore spitting out the configured values not the running values (the ones that actually have an effect).
What would be better is if the Log told me three things.
- What options were configured as Startup parameters
- What options had been set – i.e. the Runtime values
- What options weren’t recognised and therefore had been ignored
I think it’d be much more helpful (and a lot less confusing) if SQL Server did something like this:
2010-04-20 09:47:19.86 Server System Manufacturer: 'Dell Inc.', System Model: 'Latitude D830'.
2010-04-20 09:47:19.86 Server Authentication mode is MIXED.
2010-04-20 09:47:19.86 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-04-20 09:47:19.86 Server This instance of SQL Server last reported using a process ID of 1456 at 20/04/2010 09:47:13 (local) 20/04/2010 08:47:13 (UTC). This is an informational message only; no user action is required.
2010-04-20 09:47:19.86 Server Configured registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-E
2010-04-20 09:47:19.86 Server Running registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2010-04-20 09:47:19.86 Server Unrecognised Registry startup parameters:
-E
It would be even nicer if the log actually explained what these options are. It could even provide a link to the BOL documentation. That’d be useful.
Do you agree? I have posted a connect item up. If you think this is worth changing in SQL Server then please vote!
https://connect.microsoft.com/SQLServer/feedback/details/553439/sql-server-log-and-startup-parameter-logging
You may have noticed that I haven’t really explained in full what the –E parameter is. That is because I want to cover that in my next post and kick of my series entitled "Focus On Fast Track". It’s nearly done so don’t worry you won’t have to wait long!
Until the next time, James