Behavior Changes to Database Engine Features in SQL Server 2008

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.

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

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.

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.

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.

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.

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.

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.