Welcome to EMC Consulting Blogs Sign in | Join | Help

Claypole's World - The SQL Server Side

Concerning development of bespoke applications and database administration.

Enabling SQL Server Start-up Parameters – A Couple of Pitfalls

In SQL Server there are a couple of ways to set startup parameters: 

  1. SQL Server Configuration Manager
  2. 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

image

Step 2: Right Click on SQL Server Service and Select Advanced Tab

image

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

image

Step 4 : Click on Apply and then Clear the warning box

image

Click Apply

image

Click OK

Step 5 : Click on the Logon Tab  and Restart SQL Server by clicking on the “Restart” button

image

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:

image

This does not:

image

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.

 image

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.

image 

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.

  1. What options were configured as Startup parameters
  2. What options had been set – i.e. the Runtime values
  3. 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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Claypole's World - The SQL Server Side said:

One of my focuses at the moment is the work that Microsoft have done with various hardware providers

April 25, 2010 7:17 AM
 

Harry said:

You are right,that the server log should show the actual state of affairs. In the meantime look at the files with notepad or another text editor: When all parameters start in the same column, it is ok. When not, some parameters will not work.

October 21, 2010 2:24 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About James.Rowland-Jones

James is an Advisory Practice Consultant with EMC Consulting. He works primarily with SQL Server technologies in architecture, development and administration capacities. He also co-ordinates our Internal SQL Server Community.

View James Rowland-Jones's profile on LinkedIn

Powered by Community Server (Personal Edition), by Telligent Systems