Scalability and Performance Enhancements (Database Engine)
Scalability and performance enhancements in the SQL Server Database Engine include filtered indexes and statistics, new table and query hints, and new query performance and processing features.
What's New in SQL Server 2008 R2 Service Pack 1
Four new DMV's have been added: sys.dm_os_windows_info (Transact-SQL), sys.dm_server_registry (Transact-SQL), sys.dm_server_services (Transact-SQL), and sys.dm_server_memory_dumps (Transact-SQL).
One new DMF has been added: sys.dm_os_volume_stats (Transact-SQL).
Additional columns have been added to sys.dm_exec_query_stats (Transact-SQL) and sys.dm_os_sys_info (Transact-SQL).
New XEvents on selected performance counters are introduced to monitor OS configurations and resource conditions related to the instance of SQL Server.
What's New in SQL Server 2008 R2
Note
Because SQL Server 2008 R2 is a minor version upgrade of SQL Server 2008, we recommend that you also review the content in the SQL Server 2008 section.
Unicode Compression
Unicode data that is stored in nvarchar(n) and nchar(n) columns is compressed by using an implementation of the Standard Compression Scheme for Unicode (SCSU) algorithm. For more information, see Unicode Compression Implementation.
What's New in SQL Server 2008
Filtered Indexes and Statistics
In SQL Server 2008, you can use a predicate to create filtered indexes and statistics on a subset of rows in the table. In earlier versions of SQL Server, indexes and statistics were created on all of the rows in the table. Filtered indexes and statistics are especially suited for queries that select from well-defined subsets of data, such as columns with mostly NULL values, columns with heterogeneous categories of values, and columns with distinct ranges of values.
A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. For more information, see Filtered Index Design Guidelines. Filtered statistics can improve query plan quality because they cover only the rows in the filtered index. The Database Engine automatically creates and maintains filtered statistics for filtered indexes. You can also create filtered statistics on a non-indexed column to improve the query plan quality for subsets of data that do not need a filtered index. For more information, seeUsing Statistics to Improve Query Performance.
Table and Query Hints
OPTIMIZE FOR Query Hint Option Includes UNKNOWN Variable Value
The OPTIMIZE FOR query hint option is enhanced with the UNKNOWN syntax, which specifies that the Database Engine use statistical data to determine the values for one or more local variables during query optimization, instead of the initial values. The syntax can be specified for all local variables in a query, or for one or more named local variables. For more information, see Query Hints (Transact-SQL).
Query Hints
Table hints can now be specified as query hints to provide advanced query performance tuning options. We recommend using a table hint as a query hint only in the context of a plan guide. For more information, see Query Hints (Transact-SQL) and Using the INDEX and FORCESEEK Query Hints in Plan Guides.
Table Hints
The FORCESEEK table hint is added to provide advanced query performance tuning options. The hint specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view referenced in the query. For more information, see Using the FORCESEEK Table Hint.
Query Performance and Processing
Lock Escalation Option
A new LOCK_ESCALATION option of ALTER TABLE allows you to disable lock escalation on a table. On partitioned tables, you can configure locks to escalate to the partitions instead of to the whole table. This option can improve concurrency by reducing lock contention when you are using partitioned tables. For more information, see ALTER TABLE (Transact-SQL).
Optimized Bitmap Filtering
The query optimizer can place bitmap filters dynamically in parallel query plans to improve the performance of queries against a star schema. Optimized bitmap filtering can significantly improve the performance of these data warehouse queries by removing nonqualifying rows from the fact table early in the query plan. For more information, see Optimizing Data Warehouse Query Performance Through Bitmap Filtering.
Parallel Query Processing on Partitioned Objects
SQL Server 2008 improves query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhances the partitioning information provided in both compile-time and run-time execution plans. SQL Server 2008 automates and improves the thread partitioning strategy for parallel query execution plans on partitioned objects. For more information, see Query Processing Enhancements on Partitioned Tables and Indexes.
Plan Guides
The sp_create_plan_guide stored procedure has been extended to accept XML Showplan output directly in the @hints parameter instead of embedding the output in the USE PLAN hint. This simplifies the process of applying a fixed query plan as a plan guide hint. In addition, a new stored procedure, sp_create_plan_guide_from_handle, allows you to create one or more plan guides from a query plan in the plan cache.
You can create multiple OBJECT or SQL plan guides for the same query and batch or module. However, only one of these plan guides can be enabled at any given time.
The new system function sys.fn_validate_plan_guide can be used to validate a plan guide. Plan guides can become invalid after changes such as dropping an index are made to the physical design of the database. By validating a plan guide, you can determine whether the plan guide can be used unmodified by the query optimizer.
New event classes, Plan Guide Successful and Plan Guide Unsuccessful, make it easier to verify that plan guides are being used by the query optimizer. When SQL Server cannot produce an execution plan for a query that contains a plan guide, the query is automatically compiled without using the plan guide. The Plan Guide Unsuccessful event occurs when the initial plan guide compilation fails.
New counters, Guided Plan Executions/sec and Misguided Plan Executions/sec, in the SQL Server, SQL Statistics Object, can be used to monitor the number of plan executions in which the query plan has been successfully or unsuccessfully generated by using a plan guide.
Operations such as creating, deleting, enabling, disabling, or scripting plan guides can be performed by using SQL Server Management Studio. Plan guides appear under the Programmability folder in Object Explorer.
Hash Values for Finding and Tuning Similar Queries
When searching for resource-intensive queries, you should consider how to find and tune similar queries that individually consume minimal system resources, but collectively consume significant system resources. The sys.dm_exec_query_stats and sys.dm_exec_requests catalog views provide query hash and query plan hash values that you can use to help determine the aggregate resource usage for similar queries and similar query execution plans. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes.