Vipul Shah's SQL Blogs

"Transparent" Benefits of SQL Server 2005

Did you know?

In SQL Server 2005:

• DBCC - uses snapshot framework avoiding locks - no table level schema locks
• sys.dm_db_index_physical_stats replaces DBCC SHOWCONTIG - Both of these tools doesn't require S lock.  It only require IS lock
• Rebuilding clustered index does not necessarily rebuild associated nonclustered indexes unless the keyword ALL is specified
• Asynch stats update - ALTER DATABASE <DBNAME> SET AUTO_UPDATE_STATISTICS_ASYNC ON:   Query compilation with out-of-date statistics causes auto update to be done in background.  Compilation does not pause, but proceeds with old statistics
• It automatically skips updating stats for tables and indexes that haven't had any updates since the last time statistics were created or updated
• Scalability of tempdb has been enhanced - caching of initial pages occurs upon declaration of temporary tables and table variables - saves time and boosts performance
• Backup data and Log concurrently - Logshipping backups are not blocked by a data backup
• Page checksum is on by default - higher reliability.  Buffer pool replacement policy is much better at keeping the right pages in cache
• Deadlock output is now XML (graphical) by using DeadLock Graph event type in Profiler
• Missing Index feature is always on.  With new feature - SET STATISTICS XML ON, you can see the ShowPlan in XML format and within that output there is an element of MissingIndexes.   This element contains information about missing indexes based on the query execution plan.