1 / 23

SQL Server 2005 features for VLDBs

SQL Server 2005 features for VLDBs. SQL Server 2005 features for VLDBs aka (it’s fixed in the next release). Topics. Memory and File Management Statistics and Recompilation Index creation and maintenance Data Partitioning Backup and restore General Performance Improvements.

odeda
Download Presentation

SQL Server 2005 features for VLDBs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2005 features for VLDBs

  2. SQL Server 2005 features for VLDBsaka(it’s fixed in the next release)

  3. Topics • Memory and File Management • Statistics and Recompilation • Index creation and maintenance • Data Partitioning • Backup and restore • General Performance Improvements

  4. Memory and File Management • Dynamic AWE Memory Management • Instant File Initialization • File Autogrow improvements • fast startup via online rollback • Checkpoint Enhancements

  5. Statistics and Recompilation • Statement Level Recompilation • New Query Hints • Asynchronous Statistics Updating • Row Mod Counters (RMC) vs Column Mod Counters (CMC) • Persisted Computed Columns

  6. Index creation and maintenance • Reorganizing and Rebuilding Indexes • Parallel Index Operations • Online Index Operations • How to find out about the online index progress? • Including Nonkey Columns in Nonclustered Indexes • Disabled Indexes • Indexed View Enhancements

  7. SQL Server 2000 • Drop and recreate the index manually • Rebuild the index - DBCC DBREINDEX (atomic, offline, space intensive) • Atomic operation – potentially long rollback on interrupt • Requires creating a new index before dropping the old • Table is Offline for user updates (S) table lock for non-clustered index, X table lock for clustered index • Defrag the index - DBCC INDEXDEFRAG

  8. SQL Server 2005 • Drop and recreate the index manually • Rebuild the index - DBCC DBREINDEX • Defrag the index - DBCC INDEXDEFRAG • Defrag the index - ALTER INDEX ..REORGANIZE • Rebuild the index - ALTER INDEX .. REBUILD • Rebuild the index - ALTER INDEX .. REBUILD WITH (ONLINE=ON)

  9. Data Partitioning • Overview • Data Partitioning Terms • Switching • Changing a Partition? • Restrictions for Choosing the Partitioning Key • Query Optimization • Partitioning Best Practices • Other Considerations

  10. Sliding Window

  11. Sliding Window

  12. Sliding Window

  13. Sliding Window

  14. Sliding Window

  15. Sliding Window

  16. Sliding Window

  17. Backup and Restore • Early Restore Access • Partial Backups • Online Restores • Piecemeal Restores • Short Restore Sequence (Read-Only Data)

  18. General Perf. Improvements • Dropping and Rebuilding Large Objects • Database Tuning Advisor (Index Tuning Wizard) • Implementation of Parallelism • SQL Server 2005 Buffer I/O Management • Proactive Detection of Blocking and Deadlocks

  19. General Perf. Improvements(cont) • Dedicated Administrator Connection • Improved Concurrency • Bulk Operations • Compressed Drives • Replication • Full-Text Search

  20. More Information • Partitioning http://www.sqlskills.com/resources/Whitepapers/Partitioning in SQLServer 2005 Beta II.htm • Indexed Views http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx#EFAA • Batch Compilation, Recompilation, and Plan Caching in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx • Snapshot Isolation http://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx • Use the forums at www.sqlserver.org.au • Email Rowan.Burke@gmail.com

More Related