Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

Administering SQL Server 2005 Server Properties

The SQL Server Properties dialog box is the main place you, as database administrator, configure server settings specifically tailored toward a SQL Server Database Engine installation.

You can invoke the Server Properties for the Database Engine by following these steps:

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.
  2. Connect to the Database Engine in Object Explorer.
  3. Right-click SQL Server and then select Properties.

The Server Properties dialog box includes eight pages of Database Engine settings that can be viewed, managed, and configured. The eight Server Properties pages include

  • General
  • Memory
  • Processors
  • Security
  • Connections
  • Database Settings
  • Advanced
  • Permissions

The following sections provide examples and explanations for each page within the SQL Server Properties dialog box.

Administering the General Page

The first Server Properties page, General, includes mostly informational facts pertaining to the SQL Server 2005 installation, as illustrated in Figure 1.1. Here, you can view the following items: SQL Server Name; Product Version such as Standard, Enterprise, or 64 Bit; Windows Platform such as Windows 2000 or Windows 2003; SQL Server Version Number; Language Settings; Total Memory in the Server; Number of Processors; Root Directory; Server Collation; and whether the installation is clustered.

Figure 1.1

Figure 1.1 Administering the Server Properties General page.

Administering the Memory Page

Memory is the second page within the Server Properties dialog box. As shown in Figure 1.2, this page is broken into two sections: Server Memory Options and Other Memory Options. Each section has additional items to configure to manage memory; they are described in the following sections.

Figure 1.2

Figure 1.2 Administering the Server Properties Memory page.

Administering the Server Memory Options

The Server Memory options are

  • Use AWE to Allocate Memory—If this setting is selected, the SQL Server installation leverages Address Windowing Extensions (AWE) memory.
  • Minimum and Maximum Memory—The next items within Memory Options are for inputting the minimum and maximum amount of memory allocated to a SQL Server instance. The memory settings inputted are calculated in megabytes.

The following Transact-SQL (TSQL) code can be used to configure Server Memory Options:

sp_configure 'awe enabled', 1
sp_configure 'min server memory', "MIN AMOUNT IN MB"
sp_configure 'max server memory', "MAX AMOUNT IN MB"

Other Memory Options

The second section, Other Memory Options, has two memory settings tailored toward index creation and minimum memory per query:

  • Index Creation Memory—This setting allocates the amount of memory that should be used during index creation operations.
  • Minimum Memory Per Query—This setting specifies the minimum amount of memory in kilobytes that should be allocated to a query.

Use the following TSQL statements to configure Other Memory Options:

sp_configure 'index create memory, "NUMBER IN KB"
sp_configure 'min memory per query, "NUMBER IN KB"

Administering the Processors Page

The Processor page, shown in Figure 1.3, should be used to administer or manage any processor-related options for the SQL Server 2005 Database Engine. Options include threads, processor performance, affinity, and parallel or symmetric processing.

Figure 1.3

Figure 1.3 Administering the Server Properties Processor page.

Enabling Processors

Similar to a database administrator, the operating system is constantly multitasking. Therefore, the operating system moves threads between different processors to maximize processing efficiency. You should use the Processor page to administer or manage any processor-related options such as parallel or symmetric processing. The processor options include

  • Enable Processors—The two processor options within this section include Processor Affinity and I/O Affinity. Processor Affinity allows SQL Server to manage the processors; therefore, processors are assigned to specific threads during execution. Similar to Processor Affinity, the I/O Affinity setting informs SQL Server on which processors can manage I/O disk operations.
  • Automatically Set Processor Affinity Mask for All Processors—If this option is enabled, SQL Server dynamically manages the Processor Affinity Mask and overwrites the existing Affinity Mask settings.
  • Automatically Set I/O Affinity Mask for All Processors—Same thing as the preceding option: If this option is enabled, SQL Server dynamically manages the I/O Affinity Mask and overwrites the existing Affinity Mask settings.


The following Threads items can be individually managed to assist processor performance:

  • Maximum Worker Threads—The Maximum Worker Threads setting governs the optimization of SQL Server performance by controlling thread pooling. Typically, this setting is adjusted for a server hosting many client connections. By default, this value is set to 0. The 0 value represents dynamic configuration because SQL server determines the number of worker threads to utilize. If this setting will be statically managed, a higher value is recommended for a busy server with a high number of connections. Subsequently, a lower number is recommended for a server that is not being heavily utilized and has a small number of user connections. The values to be entered range from 10 to 32,767.
  • Boost SQL Server Priority—Preferably, SQL Server should be the only application running on the server; thus, it is recommended to enable this check box. This setting tags the SQL Server threads with a higher priority value of 13 instead of the default 7 for better performance. If other applications are running on the server, performance of those applications could degrade if this option is enabled because those threads have a lower priority.
  • Use Windows Fibers (Lightweight Pooling)—This setting offers a means of decreasing the system overhead associated with extreme context switching seen in symmetric multiprocessing environments. Enabling this option provides better throughput by executing the context switching inline.

These TSQL statements should be used to set processor settings:

sp_configure 'affinity mask', "VALUE";

sp_configure 'affinity 1/0 mask', :"VALUE";

sp_configure 'lightweight pooling', "0 or 1";

sp_configure 'max worker threads', :"INTEGER VALUE";

sp_configure 'priority boost', "0 or 1";

Administering the Security Page

The Security page, shown in Figure 1.4, maintains server-wide security configuration settings. These SQL Server settings include Server Authentication, Login Auditing, Server Proxy Account, and Options.

Figure 1.4

Figure 1.4 Administering the Server Properties Security page.

Server Authentication

The first section in the Security page focuses on server authentication. At present, SQL Server 2005 continues to support two modes for validating connections and authenticating access to database resources: Windows Authentication Mode and SQL Server and Windows Authentication Mode. Both of these authentication methods provide access to SQL Server and its resources.

The Windows Authentication Mode setting is the default Authentication setting and is the recommended authentication mode. It tactfully leverages Active Directory user accounts or groups when granting access to SQL Server. In this mode, you are given the opportunity to grant domain or local server users access to the database server without creating and managing a separate SQL Server account. Also worth mentioning, when Windows Authentication mode is active, user accounts are subject to enterprise-wide policies enforced by the Active Directory domain, such as complex passwords, password history, account lockouts, minimum password length, maximum password length, and the Kerberos protocol. These enhanced and well-defined policies are always a plus to have in place.

The second Authentication Option is SQL Server and Windows Authentication (Mixed) Mode. This setting, which is regularly referred to as mixed mode authentication, uses either Active Directory user accounts or SQL Server accounts when validating access to SQL Server. SQL Server 2005 has introduced a means to enforce password and lockout policies for SQL Server login accounts when using SQL Server Authentication. The new SQL Server polices that can be enforced include password complexity, password expiration, and account lockouts. This functionality was not available in SQL Server 2000 and was a major security concern for most organizations and database administrators. Essentially, this security concern played a role in helping define Windows authentication as the recommended practice for managing authentication in the past. Today, SQL Server and Windows Authentication mode may be able to successfully compete with Windows Authentication mode.

Login Auditing

Login Auditing is the focal point on the second section on the Security page. You can choose from one of the four Login Auditing options available: None, Failed Logins Only, Successful Logins Only, and Both Failed and Successful Logins.

Server Proxy Account

You can enable a server proxy account in the Server Proxy section of the Security page. The proxy account permits the security context to execute operating system commands by the impersonation of logins, server roles, and database roles. If you're using a proxy account, you should configure the account with the least number of privileges to perform the task. This bolsters security and reduces the amount of damage if the account is compromised.

Additional Security Options

Additional security options available in the Options section of the Security page are

  • Enable Common Criteria Compliance—When this setting is enabled, it manages database security. Specifically, it manages features such as Residual Information Protection (RIP), controls access to login statistics, and enforces restrictions where, for example, the column titled GRANT cannot override the table titled DENY.
  • Enable C2 Audit Tracing—When this setting is enabled, SQL Server allows the largest number of the success and failure objects to be audited. The drawback to capturing for audit data is that it can degrade performance and take up disk space.
  • Cross Database Ownership Chaining—Enabling this setting allows cross database ownership chaining at a global level for all databases. Cross database ownership chaining governs whether the database can be accessed by external resources. As a result, this setting should be enabled only when the situation is closely managed because several serious security holes would be opened.

Administering the Connections Page

The Connections page, as shown in Figure 1.5, is the place where you examine and configure any SQL Server settings relevant to connections. The Connections page is broken up into two sections: Connections and Remote Server Connections.

Figure 1.5

Figure 1.5 Administering the Server Properties Connections page.


The Connections section includes the following settings:

  • Maximum Number of Concurrent Connections—The first setting determines the maximum number of concurrent connections allowed to the SQL Server Database Engine. The default value is 0, which represents an unlimited number of connections. The value used when configuring this setting is really dictated by the SQL Server hardware such as the processor, RAM, and disk speed.
  • Use Query Governor to Prevent Long-Running Queries—This setting creates a stipulation based on an upper limit criteria specified on the time period in which a query can run.
  • Default Connection Options—For the final setting, you can choose from approximately 16 advanced connection options that can be either enabled or disabled, as shown in Figure 1.5.

Remote Server Connections

The second section located on the Connections page focuses on Remote Server settings:

  • Allow Remote Connections to This Server—If enabled, the first option allows remote connections to the specified SQL Server.
  • Remote Query Timeout—The second setting is available only if Allow Remote Connections is enabled. This setting governs how long it will take for a remote query to terminate. The values that can be configured range from 0 to 2,147,483,647. Zero represents infinite.
  • Require Distributed Transactions for Server-to-Server Communication—The final setting controls the behavior and protects the transactions between systems by using the Microsoft Distributed Transaction Coordinate (MS DTC).

Administering the Database Settings Page

The Database Settings page, shown in Figure 1.6, contains configuration settings that each database within the SQL Server instance will inherit. The choices available on this page are broken out by Fill Factor, Backup and Restore, Recovery, and Database Default Locations.

Figure 1.6

Figure 1.6 Administering the Server Properties Database Settings page.

Default Index Fill Factor

The Default Index Fill Factor specifies how full SQL Server should configure each page when a new index is created. The default setting is 0, and the ranges are between 0 and 100. The 0 value represents a table with room for growth, whereas a value of 100 represents no space for subsequent insertions without requiring page splits. A table with all reads typically has a higher fill factor, and a table that is meant for heavy inserts typically has a low fill factor. The value 50 is ideal when a table has plenty of reads and writes. This setting is global to all tables within the Database Engine.

For more information on fill factors, refer to Chapter 8, "SQL Server 2005 Maintenance Practices" and Chapter 9, "Managing and Optimizing SQL Server 2005 Indexes."

Backup and Restore

The Backup and Restore section of the Database Settings page includes

  • Specify How Long SQL Server Will Wait for a New Tape—The first setting governs the time interval SQL Server will wait for a new tape during a database backup process. The options available are Wait Indefinitely, Try Once, or Try for a specific number of minutes.
  • Default Backup Media Retention—This setting is a system-wide configuration that affects all database backups, including the translation logs. You enter values for this setting in days, and it dictates the time to maintain and/or retain each backup medium.


The Recovery section of the Database Settings page consists of

  • Recovery Interval (Minutes)—Only one Recovery setting is available. This setting influences the amount of time, in minutes, SQL Server will take to recover a database. Recovering a database takes place every time SQL Server is started. Uncommitted transactions are either committed or rolled back.

Database Default Locations

Options available in the Database Default Locations section are

  • Data and Logs—The two folder paths for Data and Log placement specify the default location for all database data and log files. Click the ellipses on the right side to change the default folder location.

Administering the Advanced Page

The Advanced Page, shown in Figure 1.7, contains the SQL Server general settings that can be configured.

Figure 1.7

Figure 1.7 Administering the Server Properties Advanced Settings page.

Miscellaneous Settings

Options available on the Miscellaneous section of the Advanced page are

  • Allow Triggers to Fire Others—If this setting is configured to True, triggers can execute other triggers. In addition, the nesting level can be up to 32 levels. The values are either True or False.
  • Cursor Threshold—This setting dictates the number of rows in the cursor that will be returned for a result set. A value of 0 represents that cursor keysets are generated asynchronously.
  • Default Full-Text Language—This setting specifies the language to be used for full-text columns. The default language is based on the language specified during the SQL Server instance installation.
  • Default Language—This setting is also inherited based on the language used during the installation of SQL. The setting controls the default language behavior for new logins.
  • Max Text Replication Size—This global setting dictates the maximum size of text and image data that can be inserted into columns. The measurement is in bytes.
  • Scan for Startup Procs—The configuration values are either True or False. If the setting is configured to True, SQL Server allows stored procedures that are configured to run at startup to fire.
  • Two Digit Year Cutoff—This setting indicates the uppermost year that can be specified as a two-digit year. Additional years must be entered as a four digits.

Network Settings

Options available on the Network section of the Advanced page are

  • Network Packet Size—This setting dictates the size of packets being transmitted over the network. The default size is 4096 bytes and is sufficient for most SQL Server network operations.
  • Remote Login Timeout—This setting determines the amount of time SQL Server will wait before timing out a remote login. The default time is 30 seconds, and a value of 0 represents an infinite wait before timing out.

Parallelism Settings

Options available on the Parallelism section of the Advanced page are

  • Cost Threshold for Parallelism—This setting specifies the threshold above which SQL Server creates and runs parallel plans for queries. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Set this option only on symmetric multiprocessors. For more information, search for "cost threshold for parallelism option" in SQL Server Books Online.
  • Locks—The default for this setting is 0, which indicates that SQL Server is dynamically managing locking. Otherwise, you can enter a numeric value that sets the utmost number of locks to occur.
  • Max Degree of Parallelism—This setting limits the number of processors (up to a maximum of 64) that can be used in a parallel plan execution. The default value of 0 uses all available processors, whereas a value of 1 suppresses parallel plan generation altogether. A number greater than 1 prevents the maximum number of processors from being used by a single query execution. If a value greater than the number of available processors is specified, however, the actual number of available processors is used. For more information, search for "max degree of parallelism option" in SQL Server Books Online.
  • Query Wait—This setting indicates the time in seconds a query will wait for resources before timing out.

Administering the Permissions Page

The Permissions Page, as shown in Figure 1.8, includes all the authorization logins and permissions for the SQL Server instance. You can create and manage logins and/or roles within the first section. The second portion of this page displays the Explicit permission based on the login or role.

Figure 1.8

Figure 1.8 Administering the Server Properties Permissions page.

For more information on permissions and authorization to the SQL Server 2005 Database Engine, refer to Chapter 13, "Administering SQL Server Security."

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020