Database Engine Scalability and Performance Enhancements
Data partitioning is enhanced with native table and index partitioning. Application concurrency is improved using the new snapshot isolation level and the ability to have multiple active result sets on a single connection.
Scalability and Performance Enhancements
Plan Guides
A new system stored procedure, sp_create_plan_guide, is available to optimize the performance of queries by attaching query hints to them when it is not possible or desirable to change the text of the query directly. Plan guides can be useful when a small subset of queries in a database application deployed from a third-party vendor is not performing as expected.
For more information, see Optimizing Queries in Deployed Applications by Using Plan Guides.
Snapshot Isolation Level
The snapshot isolation level implements row versioning to provide users with a view of the data in the database as it existed when the current transaction started. Except during recovery, a snapshot transaction does not acquire locks to protect read operations. Snapshot isolation can minimize locking and blocking problems in read-only applications.
For more information, see Row Versioning-based Isolation Levels in the Database Engine.
Statistics on Correlated datetime Columns
A new database SET option, DATE_CORRELATION_OPTIMIZATION, can be enabled to improve the performance of queries that join two tables whose datetime columns are correlated and specify a date restriction in the query predicate.
For more information, see Optimizing Queries That Access Correlated datetime Columns.
Forced Parameterization
You can specify that all queries that execute on a database be parameterized by setting a new PARAMETERIZATION database SET option to FORCED. Forced parameterization may improve the performance of certain databases by reducing the frequency of query recompilations.
For more information, see Forced Parameterization.
Asynchronous Statistics Updating
A new database SET option, AUTO_UPDATE_STATISTICS_ASYNC, can be enabled to improve the predictability of query response times. When enabled, out-of-date statistics are put on a queue for updating by a background worker thread, and the query that initiated the statistics update compiles immediately rather than waiting for the statistics to be updated.
For more information, see Index Statistics.
Persisted Computed Columns
Computed columns can be marked as PERSISTED, in which case their values are stored in the data pages for the table. This can speed retrieval for compute-intensive columns.
For more information, see Computed Columns.
Multiple Active Result Sets (MARS)
SQL Server 2005 introduces the ability for multiple statements to return result sets at the same time on a single connection. In earlier versions of SQL Server, only one statement at a time could actively return result sets for each connection, and no new statements could be executed until all of the result sets were retrieved.
For more information, see Batch Execution Environment and MARS.
Including Nonkey Columns in Nonclustered Indexes
Columns that are not part of the index key can be included in nonclustered indexes. Including the nonkey columns can speed queries that can acquire all the data they need from the index without having to access the data rows and can exceed the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes.
For more information, see Index with Included Columns.
Index Locking Granularity
The new ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options in CREATE INDEX and ALTER INDEX can be used to control the level at which locking occurs for the index.
For more information, see Setting Index Options.
Indexes on XML Type Columns
The XML documents and fragments stored using the XML data type can be very large. If they are not indexed, the Database Engine must shred the documents and fragments for each reference. Defining an index on a column using the XML data type allows the Database Engine to more efficiently process the documents and fragments.
For more information, see Indexes on xml Data Type Columns
Indexed View Enhancements
The SQL Server 2005 query optimizer can match more queries to indexed views than in previous versions, including queries that contain scalar expressions, scalar aggregate and user-defined functions, interval expressions, and equivalency conditions.
For more information, see Designing Indexed Views.
Indexed view definitions can also now contain scalar aggregate and user-defined functions with certain restrictions.
For more information, see Creating Indexed Views.
New Query Hints
Four query hints are added for use in generating optimal query plans.
RECOMPILE forces SQL Server to discard the plan generated for the query after it executes so that a new one is generated the next time the same or a similar query plan is executed. RECOMPILE is useful for queries with variable values that vary widely each time they are compiled and executed. This hint can be used in place of the WITH RECOMPILE option for creating stored procedures when you only want a subset of queries inside the stored procedure to be recompiled.
OPTIMIZE FOR instructs SQL Server to use a particular value for a local variable with a value that is otherwise unknown when the query is compiled and optimized.
USE PLAN instructs SQL Server to use an existing query plan for a query. The USE PLAN query hint can be used for queries whose plans result in slow execution times, but for which you know better plans exist.
PARAMETERIZATION specifies whether a query is parameterized as part of compiling a query plan, and is used inside a plan guide to override the current setting of the PARAMETERIZATION database SET option.
For more information, see Query Hint (Transact-SQL).
Dropping and Rebuilding Large Objects
When dropping or rebuilding tables and indexes that use more than 128 extents, SQL Server 2005 defers the actual page deallocations, and their associated locks, until after a transaction commits. Avoiding these locks is accomplished by splitting the process into a logical and physical phase.
For more information, see Dropping and Rebuilding Large Objects.
Scalable Shared Databases
This new feature allows you to attach a read-only database to multiple server instances of SQL Server 2005 over a storage area network (SAN). A scalable shared database enables you to scale-out a database using commodity hardware for reporting servers and volumes and to achieve a smooth upgrade path.
For more information, see Overview of Scalable Shared Databases.