SQL Server 2008 Top 10 Features

Last week I presented my personal top 10 of new Features in SQL Server 2008 on the Community Day.

Here's the list

1. Backup Compression
2. PowerShell
3. Policy Based Mgmt
4. Multi Server Mgmt
5. Resource Governor
6. Change Data Capture
7. Data Profiling
8. AS DMVs
9. Report Builder 2.0
10. New stuff in SQL Server Management Studio (RC0)

The session was recorded and is now available on Chopsticks.




Werner Geuens gave an excellent presentation on the new datatypes in SQL Server 2008. This is also available on Chopsticks.



The Community Day was again a big success. Looking forward to next year!!!

SQL Server 2008 - calculating backup ratios

Backup compression is one of the coolest new features in SQL Server 2008. This nice little query gives you an overview of the average compression ratios. I used another T-SQL enhancement (GROUPING SETS) to show the ratio per database, backup type, ...


SELECT
DatabaseName =ISNULL(database_name,'(All Databases)'),
BackupType =ISNULL(CAST(type AS VARCHAR(15)), '(All Types)'),
CompressionRatio = AVG( CAST(backup_size/ compressed_backup_size AS DECIMAL(5,2)))
from msdb..backupset
WHERE (backup_size <> compressed_backup_size)
GROUP BY GROUPING SETS((database_name,type), (database_name), (type), ())
ORDER BY 1,2

Check out these excelent blogposts for more info on backup compression:

Paul Randal - SQL Server 2008: Backup Compression CPU Cost

SQLCAT - Tuning the Performance of Backup Compression in SQL Server 2008<

Estimating backup/restore duration

How many times have you been watching the progress bar during a SQL Server Backup or restore operation? And how times have you answered "Wel, I guess ... " when people asked you "Hey mister DBA, how much more time will it take before the restore has completed?".

I have been doing quite some backup/restore operations during recent upgrading and migration projects. The sys.dm_exec_requests DMV contains one row for every running command on a SQL Server instance. Two interesting columns (estimated_completion_time, percent_complete) help you answering the "when" and "how much longer" questions.

The SELECT statement below will give you a time estimation for every running "BACKUP DATABASE" or "RESTORE DATABASE" statement. Play around with the WHERE clause to monitor other long running operations.

/* Created by free online sql formatter */


SELECT command, 'EstimatedEndTime' = Dateadd(ms,estimated_completion_time,Getdate()),
'EstimatedSecondsToEnd' = estimated_completion_time / 1000,
'EstimatedMinutesToEnd' = estimated_completion_time / 1000 / 60,
'BackupStartTime' = start_time,
'PercentComplete' = percent_complete
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE','RESTORE DATABASE')