What s New for Indexes in SQL Server 2005


What's New for Indexes in SQL Server 2005

SQL Server 2005 has so many new features it's an absolute garden of delight for any DBA, but at the same time it can be challenging to find the exact information relevant to what you want to do. This section highlights the new index-related features in SQL Server 2005, and provides a very brief overview of each.

Partition Tables and Indexes

You can now create tables on multiple partitions, and indexes on each partition. This enables you to manage operations on very large datasets, such as loading and unloading a new set of data, more efficiently by indexing just the new partition, rather than having to re-index the whole table. You will find a lot more information about partition tables and indexes later in this chapter.

Online Index Operations

Online index operations are a new availability feature. They enable users to continue to query against a table while indexes are being built or rebuilt. The main scenario for using this new feature is when you need to make index changes during normal operating hours. The new syntax for using online index operations is the addition of the ONLINE = ON option with the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE operations.

Parallel Index Operations

Parallel index operations are another new feature. They are available only in Enterprise Edition, and only apply to systems running on multi-processor machines. The key scenario for using this new feature is when you need to restrict the amount of CPU resources that index operations consume. This might be either for multiple index operations to coexist, or more likely when you need to allow other tasks to complete while performing index operations. They enable a DBA to specify the MAXDOP for an index operation. This is very useful on large systems, allowing you to limit the maximum number of processors to be used in index operations. It's effectively a MAXDOP specifically for index operations, and it works in conjunction with the server-configured MAXDOP setting. The new syntax for parallel index operations is the MAXDOP=n option, which can be specified on CREATE INDEX, ALTER INDEX, DROP INDEX (for clustered indexes only), ALTER TABLE ADD (constraint), ALTER TABLE DROP (clustered index), and CONSTRAINT operations.

Asynchronous Statistics Update

This is a new performance SET option - AUTO_UPDATE_STATISTICS_ASYNC. When this option is set, outdated statistics are placed on a queue and will be automatically updated by a worker thread at some later time. The query that generated the auto update request will continue before the stats are updated. Note that asynchronous statistics update cannot occur if any data definition language (DDL) statements such as CREATE, ALTER, or DROP occur in the same transaction.

Full Text Indexes

Full Text Search now supports the creation of indexes on XML columns. It has also been upgraded to use MSSearch 3.0, which includes additional performance improvements for full text index population. It also means that there is now one instance of MSSearch for each SQL Server instance.

Non-Key Columns in Nonclustered Indexes

With SQL Server 2005, non-key columns can be added to a nonclustered index. This has several advantages. It enables queries to retrieve data faster, as the query can now retrieve everything it needs from the index pages without having to do a bookmark lookup into the table to read the data row. The non-key columns are not counted in the limits for the nonclustered index number of columns (16 columns), or key length (900 bytes). The new syntax for this option is INCLUDE (Column Name,...), which is used with the CREATE INDEX statement.

Index Lock Granularity Changes

The CREATE INDEX and ALTER INDEX T-SQL statements have been enhanced by the addition of new options to control the locking that occurs during the index operation. ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS specify the granularity of the lock to be taken during the index operation.

Indexes on XML Columns

This is a new type of index on the XML data in a column. It enables the database engine to find elements within the XML data without having to shred the XML each time.

Dropping and Rebuilding Large Indexes

The database engine has been modified to treat indexes occupying over 128 extents in a new, more scalable way. If a drop or rebuild is required on an index larger than 128 extents, then the process is broken into logical and physical stages. In the logical phase, the pages are simply marked as deallocated. Once the transaction commits, the physical phase of deallocating the pages occurs. The deallocation takes place in batches occurring in the background, thereby avoiding taking locks for a long period of time.

Indexed View Enhancements

Indexed views have been enhanced in several ways. They can now contain scalar aggregates, and some user-defined functions (with restrictions). In addition, the query optimizer can now match more queries to indexed views if the query uses scalar expressions, scalar aggregates, user-defined functions, interval expressions, and equivalency conditions.

Version Store

The version store is a major enhancement for SQL Server 2005. It provides the basis for the row versioning framework that is used by Online Indexing, MARS, triggers, and the new row versioning-based isolation levels.

Database Tuning Advisor

The Database Tuning Advisor (DTA) replaces SQL Server 2000's Index Tuning Wizard (ITW). DTA now offers the following new features:

  • Time-bound tuning: Allows you to limit the amount of time spent analyzing a workload. The more time spent, the better the analysis, but the higher the load on the system.

  • Tune across multiple DBs: You can now tune workloads that run across multiple databases.

  • Tune a broader class of events and triggers: DTA adds the capability to tune using workloads with a larger range of events than was possible with the ITW.

  • Tuning log: DTA writes a log of any events it is unable to tune, along with the reason why it cannot tune that event.

  • What-if analysis: DTA enables you to specify a theoretical server configuration in an input XML file, and provides tuning recommendations for the given workload based on the theoretical server configuration. This enables you to model how changing the server configuration will change the tuning required for optimal performance. Unfortunately, using this option requires manually editing an XML configuration file.

  • More control over tuning options: DTA adds more options to control tuning recommendations.

  • XML file support: DTA adds support for input and output XML files. The input file enables you to provide a different server configuration. The output file enables you to store the DTA recommendations in an XML file. Again, in most cases using this and other XML file-based features requires manual editing of the XML file.

  • Partitioning support: One of the new tuning options is the capability to ask for partitioning recommendations.

  • Offloading tuning load to lower-spec hardware: DTA uses just the metadata and statistics from a server to perform its tuning analysis. Because it doesn't need all the data as well, it can easily be run on lower-spec hardware without affecting the quality or speed of delivery of the tuning recommendations. However, during the tuning process, the test server must have access to the production server so that metadata about the server configuration can be captured. It would be nice if this metadata could be persisted to an XML file that you could edit for further what-if analysis, but this doesn't seem to be possible yet.

  • Db_owners can now play too: With ITW, only members of the sysadmin role could execute the wizard. With DTA, members of the db_owners role now have the rights to get the same recommendations. Note that the first time DTA is run, it must be run by a member of the sysadmin role before a member of the db_owners role can use it.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net