Administering SQL Server 2005 Database Engine

Although SQL Server 2005 is composed of numerous components, one component is often considered the foundation of the product. The Database Engine is the core service for storing, processing, and securing data for the most challenging data systems. Likewise, it provides the foundation and fundamentals for the majority of the core database administration tasks. As a result of its important role in SQL Server 2005, it is no wonder that the Database Engine is designed to provide a scalable, fast, and highly available platform for data access and other components.

This chapter focuses on administering the Database Engine component and managing the SQL server properties and database properties based on SQL Server 2005 Service Pack 2. Database Engine management tasks are also covered.

Even though the chapter introduces and explains all the management areas within the Database Engine, you are directed to other chapters for additional information. This is a result of the Database Engine component being so large and intricately connected to other features.

• Upon the launch of SQL Server 2005, the installation of SQL Server 2005 Integration Services (SSIS) was warranted if organizations wanted to run maintenance plans. This has since changed. Integration Services is no longer required because maintenance plans are now a fully supported feature within the Database Engine.
  
• Many enhancements have been made to maintenance plans in SQL Server 2005 with SP2 including support for environments with multiple servers, logging on to remote servers, and providing users with multiple schedules. Previously, maintenance plans could be run only on a server-only installation after SSIS was installed. 
   
• A new storage format is introduced with the release of SP2 to increase functionality and minimize disk space. The new format, known as vardecimal, stores decimal and numeric data as variable-length columns. 
   
• Logon triggers are included with SP2. In addition, SQL Server 2005 Enterprise Edition now has a Common Criteria Compliance Enabled option that follows common criteria for evaluating SP_CONFIGURE. See Common Criteria Certification in Books Online for more information. 
   
• Supported with SQL Server 2005 SP2 for the first time is the sqllogship application, which is responsible for operations involving backup, copy, and restore procedures. In addition, the application performs cleanup jobs for a log shipping configuration. 
   
• Plan cache improvements are part of the Database Engine enhancements with SP2, improving system performance and improving the use of the physical memory readily available to database pages. Plan cache improvements also can return XML query plans with an XML nesting level greater than or equal to 128 by using the new sys.dm_exec_ text_query_plan table-valued function. This feature is supported in SQL Server 2005 Express Edition SP2. 
   
• SQL Server Management Studio (SSMS) for Relational Engine features the following:
   
  1. The Table.CheckIdentityValue() is supported only with the Express Edition of SQL Server 2005 and is involved in generating a schema name for an object name that meets the criteria. 
      
  2. The Column.AddDefaultConstraint() feature is also supported only with the Express Edition of SQL Server 2005. This feature is responsible for working against table columns for SQL Server 2000 database instances.

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                            ■ Connections

■ Memory                            ■ Database Settings

■ Processors                       ■ Advanced

■ Security                            ■ Permissions

Note

Each SQL Server Properties setting can be easily scripted by clicking the Script button. The Script button is available on each Server Properties page. The Script options available include Script Action to New Query Window, Script Action to a File, Script Action to Clipboard, and Script Action to a Job.

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.  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.

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.

 
Figure 1.2.  Administering the Server Properties Memory page.

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

sp_configure ‘awe enabled’, 1
RECONFIGURE
GO
sp_configure ‘min server memory’, “MIN AMOUNT IN MB"
RECONFIGURE
GO
sp_configure ‘max server memory’, “MAX AMOUNT IN MB"
RECONFIGURE
GO

Note

The information in double quotes needs to be replaced with a value specific to this example. This applies to this Transact-SQL example and subsequent ones to follow in this chapter.

Other Memory Options

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

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

 Note

It is best to let SQL Server dynamically manage both the memory associated with index creation and for queries. However, you can specify values for index creation if you’re creating many indexes in parallel. You should tweak the minimum memory setting per query if many queries are occurring over multiple connections in a busy environment.

Use the following TSQL statements to configure Other Memory Options:

sp_configure ‘index create memory, "NUMBER IN KB"
RECONFIGURE
GO
sp_configure ‘min memory per query, "NUMBER IN KB"
RECONFIGURE
GO

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.   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.

Tip

SQL Server 2005 does a great job of dynamically managing and optimizing processor and I/O affinity settings. If you need to manage these settings manually, you should reserve some processors for threading and others for I/O operations. A processor should not be configured to do both.

1. 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.
   
2. 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.

Threads

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

1. 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. 
    
2. 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. 
    
3. 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.

Note

Enabling fibers is tricky because it has its advantages and disadvantages on performance. This is derived from how many processors are running on the server. Typically, performance gains occur if the system is running a lot of CPUs, such as more than 16; whereas performance may decrease if there are only 1 or 2 processors. To ensure the new settings are optimized, it is a best practice to monitor performance counters, after changes are made.

These TSQL statements should be used to set processor settings:

sp_configure ‘affinity mask’, “VALUE"; RECONFIGURE;
GO

sp_configure ‘affinity 1/0 mask’, :”VALUE"; RECONFIGURE;
GO
sp_configure ‘lightweight pooling’, “0 or 1";
RECONFIGURE;
GO

sp_configure ‘max worker threads’, :”INTEGER VALUE";
RECONFIGURE;
GO

sp_configure ‘priority boost’, “0 or 1"; RECONFIGURE;
GO