Share via

  • Article

Behavior Changes to Database Engine Features in SQL Server 2008

This topic describes behavior changes in the Database Engine. Behavior changes affect how features work or interact in SQL Server 2008 as compared with earlier versions of SQL Server.

SQL Server Agent

Changes to behavior in scripting a SQL Server Agent task.

In SQL Server 2008, if you create a new job by copying the script from an existing job, the new job might inadvertently affect the existing job. To create a new job using the script from an existing job, manually delete the parameter @schedule\_uid which is usually the last parameter of the section which creates the job schedule in the existing job. This will create a new independent schedule for the new job without affecting existing jobs.

Access Check Cache Options

In SQL Server 2005, the access check result cache internal structure cannot be configured except by using trace flags. In SQL Server 2008, you can use the access check cache options to modify this structure. For more information, see access check cache Options.

Full-Text Search

SQL Server 2008 introduces a new full-text search architecture. The Full-Text Search Engine is now fully integrated into the SQL Server Database Engine, rather than a separate service. Integration provides better full-text search manageability, scalability, security and performance than in previous releases of SQL Server. For more information about the main differences between full-text search in SQL Server 2005 and SQL Server 2008, as well as best practices associated with this new integrated Full-Text Search Engine, see the technical article, "SQL Server 2008 Full-Text Search: Internals and Enhancements" on MSDN.

Linked Servers

SQL Server 2008 changes the transaction semantics of INSERT...EXECUTE statements that execute against a loopback linked server. In SQL Server 2005, this scenario is not supported and causes an error. In SQL Server 2008, an INSERT...EXECUTE statement can execute against a loopback linked server when the connection does not have multiple active result sets (MARS) enabled. When MARS is enabled on the connection, the behavior is the same as in SQL Server 2005.

Parallelism

Partitioned Table Query Processing and Parallelism

In SQL Server 2008, improvements to partitioned table design facilitate better parallelism during query processing over partitioned tables than in SQL Server 2005. As a by-product of this redesign, only two-way joins can be collocated. The query plans for two-way collocated joins in SQL Server 2008 look the same as in SQL Server 2005 and have performance comparable to SQL Server 2005. If additional tables with aligned partitioning are included in the join, a different plan will be selected, such as a two-way collocated join that is followed by a hash join with the third table. Collocated joins between more than two tables are unusual, and collocated joins do not benefit from the parallelism improvements in SQL Server 2008. However, if you have a query for which SQL Server 2005 performs a three- (or more) way collocated join, it is possible that the query may run slower in SQL Server 2008 if the amount of memory is small relative to the size of the tables. Ways to improve performance in this situation include increasing the amount of memory that is available, and re-writing the query so that individual partitions are joined separately before combining the results. For additional information about collocated joins, see Query Processing Enhancements on Partitioned Tables and Indexes.

Star Join and Parallelism

SQL Server has a new optimization for processing queries with star joins that uses hash joins and bitmap filters. When a query processes large amounts of data from joining fact tables to dimension tables in a star schema, a query plan using the new optimization can execute much faster. 

Thus, you may see a new query plan for your existing queries if they fit the star join pattern. The query optimizer chooses this plan when its estimates indicate that query performance will increase. However, if the statistics used in the cost estimate are inaccurate, the query optimizer might choose the star join optimization when a different plan would be faster.

If the max degree of parallelism configuration option or the MAXDOP index option is set to 1, the query optimizer will not use the star join optimization and you will not experience the benefits provided by the new star join optimization. If the query execution system dispatches a query optimized with a parallel plan with only one thread, some bitmap filters may be removed from a multiple-bitmap-filter star join plan. This change may slow down execution more than expected when you go from 2 threads down to 1 thread, for example.

Star join optimization is available only in the Enterprise, Developer and Evaluation editions of SQL Server. For more information about bitmap filtering, see Optimizing Data Warehouse Query Performance Through Bitmap Filtering. For more information about how to interpret query plans containing bitmap filters, see Interpreting Execution Plans Containing Bitmap Filters. For more information about the star join optimization, see the TechNet Magazine article, "Data Warehouse Query Performance".

Few Outer Rows Parallelism

SQL Server 2008 facilitates parallelism for nested loop joins when the outer side of the join has only a few rows. In SQL Server 2005, if multiple threads are available, each thread is allocated a page of rows from the outer side of the join. If there are only a few rows, they are likely to be on the same page. In such cases, only one thread is employed and the potential benefits of parallelism are lost. SQL Server 2008 recognizes such cases and introduces an exchange operator that allocates one row per thread so that all available CPUs are employed. The increased parallelism means that CPU consumption will increase temporarily as compared with SQL Server 2005, but query execution will be faster. This new behavior is only seen if the number of outer rows is small and if the cost of the query is estimated to be large enough to benefit from the additional parallelism. If the query cost is estimated to be small or if the cardinality estimate for the outer side is greater than 1000, SQL Server will allocate one page per thread as in SQL Server 2005. For more information about exchange operators and parallel query processing. see Parallel Query Processing.

Partitioned Table Queries That Use the USE PLAN Hint

SQL Server 2008 changes the way queries on partitioned tables and indexes are processed. Queries on partitioned objects that use the USE PLAN hint might contain an invalid plan. We recommend the following procedures after upgrading to SQL Server 2008.

When the USE PLAN hint is specified directly in a query:

  1. Remove the USE PLAN hint from the query.

  2. Test the query.

  3. If the optimizer does not select an appropriate plan, tune the query and then specify the USE PLAN hint with the desired query plan.

When the USE PLAN hint is specified in a plan guide:

  1. Use the sys.fn_validate_plan_guide function to check the validity of the plan guide. Alternatively, you can check for invalid plans by using the Plan Guide Unsuccessful event in SQL Server Profiler.

  2. If the plan guide is invalid, drop the plan guide. If the optimizer does not select an appropriate plan, tune the query and then specify the USE PLAN hint with the desired query plan.

For more information about query processing on partitioned objects, see Query Processing Enhancements on Partitioned Tables and Indexes.

Plan Guides

In SQL Server 2008, if a plan guide cannot be honored, the query compiles using a different plan and no error is returned. In SQL Server 2005, an error is raised and the query fails.

Plan guides created in SQL Server 2005 may not be valid after upgrading to SQL Server 2008. Invalid plan guides will not cause the application to fail, but the plan guide will not be used. We recommend re-evaluating and testing plan guide definitions when you upgrade your application to a new release of SQL Server. Performance tuning requirements and plan guide matching behavior may change. After you upgrade a database to SQL Server 2008, you should perform the following tasks to validate existing plan guides by using the sys.fn_validate_plan_guide function. Alternatively, you can monitor for invalid plan guides by using the Plan Guide Unsuccessful event in SQL Server Profiler.

Query Processor Architecture

SQL Server 2008 changes the way queries on partitioned tables and indexes are processed. Queries on partitioned objects that use the USE PLAN hint for a plan generated by SQL Server 2005 might contain an invalid plan. For more information, see Considerations for Upgrading the Database Engine. For more information about query processing on partitioned objects, see Query Processing Enhancements on Partitioned Tables and Indexes.

REPLACE Function

In SQL Server 2005, trailing spaces specified in the first input parameter to the REPLACE function are trimmed when the parameter is of type char. For example, in the statement SELECT '<' + REPLACE(CONVERT(char(6), 'ABC '), ' ', 'L') + '>', the value 'ABC ' is incorrectly evaluated as 'ABC'.

In SQL Server 2008, trailing spaces are always preserved. For applications that rely on the previous behavior of the function, use the RTRIM function when specifying the first input parameter for the function. For example, the following syntax will reproduce the SQL Server 2005 behavior SELECT '<' + REPLACE(RTRIM(CONVERT(char(6), 'ABC ')), ' ', 'L') + '>'.

System Databases

Resource Database

In SQL Server 2005, the data and log files for the Resource database depend on the location of the data file of the master database. Therefore, moving the master database also requires moving the Resource database to the same location. In SQL Server 2008, this dependency does not exist. The master database files can be moved without moving the Resource database.

In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The Resource database cannot be moved.

tempdb Database

In earlier versions of SQL Server, the PAGE_VERIFY database option is set to NONE for the tempdb database and cannot be modified. In SQL Server 2008, the default value for the tempdb database is CHECKSUM for new installations of SQL Server. When upgrading an installation of SQL Server, the default value remains NONE. The option can be modified. We recommend that you use CHECKSUM for the tempdb database.

Using INSERT…SELECT to Bulk Load Data with Minimal Logging

In earlier versions of SQL Server, bulk loading rows into a target table by using the statement INSERT INTO <target_table> SELECT <columns> FROM <source_table> is always a fully logged operation. In SQL Server 2008, this operation can be performed with minimal logging when the target table is a heap, the recovery model of the database is set to simple or bulk-logged, and the TABLOCK hint is specified on the target table. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction. For more information, see INSERT (Transact-SQL).

XML

Upgrading Typed XML from SQL Server 2005 to SQL Server 2008

SQL Server 2008 contains several extensions to the XML schema support, including support for lax validation, improved handling of xs:date, xs:time and xs:dateTime instance data, and added support for the list and union types. In most cases, the changes do not affect the upgrade experience. However, if you use an XML schema collection in SQL Server 2005 that allows values of type xs:date, xs:time, or xs:dateTime (or any subtype), the following upgrade steps occur when you upgrade your SQL Server 2005 database to SQL Server 2008.

  1. For every xml column, that is typed with an XML schema collection that contains elements or attributes that are typed as either xs:anyType, xs:anySimpleType, xs:date or any of its subtypes, xs:time or any of its subtypes, or xs:dateTime and any of its subtypes, or are union or list types containing any of these types the following occurs:

    1. All XML indexes on the column are disabled.

    2. All SQL Server 2005 values continue to be represented in the Z time zone, because they have been normalized to the Z time zone.

    3. Any xs:date or xs:dateTime values that are smaller than January 1st of year 1 will lead to a runtime error when the index is rebuilt or an XQuery or XML-DML statements are executed against the xml data type containing that value.

  2. Any negative years in xs:date or xs:dateTime facets or default values in an XML schema collection are automatically updated to the smallest value allowed by the base xs:date or xs:dateTime type. For example, 0001-01-01T00:00:00.0000000Z for xs:dateTime.

Note that you can still use a simple SQL select statement to retrieve the whole xml data type, even if it contains negative years. We recommend replacing negative years with a year within the newly supported range, or changing the type of the element or attribute to xs:string. For more information, see Typed XML Compared to Untyped XML.

Lax Validation and xs:anyType Elements

In SQL Server 2005 lax validation is not supported and strict validation is applied for elements of the type anyType. In SQL Server 2008, the content of elements of type anyType are validated using lax validation. For more information, see Wildcard Components and Content Validation.

Change History

Updated content

Added the sections, "Access Check Cache Options", "Full-Text Search", "Parallelism", and "XML".

Added the section, "Using INSERT… SELECT to Bulk Load Data with Minimal Logging".

Added the section, “Changes to behavior in scripting a SQL Server Agent task”.