What is new in SQL Server 2008 R2 Service Pack 1

I just patched my dev instance with Service Pack 1 for SQL Server 2008 R2 and in this post I will share some first impressions and facts about it.

After you apply the patch the first thing that comes to your attention is the version of the SQL Server. The new version of R2 with SP1 is 10.50.2500. I have to say I like that number Smile

Next thing taken out from BOL and the SP1 release notes is the modified DMV sys.dm_exec_query_stats. In SP1 we have four new columns introduced: total_rows, last_rows, min_rows, max_rows. Those columns provide information on returned rows statistics of a plan since its been last compiled.

There are couple of new interesting DMVs which might come handy from time to time:

sys.dm_os_windows_info –> returns information on Windwos release, service pack level and language

sys.dm_os_volume_stats –> this is a new DMF which returns information on the partitions SQL databases are hosted on. The valuable info here is the new possibility to check the free space on that partition.

SELECT 
   vs.volume_mount_point, -- e.g. C:\
   vs.volume_id,
   vs.logical_volume_name,
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   f.[filename],
   file_size_MB = f.[size] / 128,
   drive_size_MB = vs.total_bytes/1024/1024, 
   drive_free_space_MB = vs.available_bytes/1024/1024, 
   drive_percent_free = CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes),
   vs.supports_compression, 
   vs.supports_alternate_streams, 
   vs.supports_sparse_files, 
   vs.is_read_only, 
   vs.is_compressed
FROM
   sys.sysaltfiles AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
WHERE
   f.dbid < 32767
ORDER BY
   drive_percent_free DESC,
   db,
   [file];

*The above script is taken from the original post of Aaron Bertrand without any change whatsoever

sys.dm_server_registry –> this one is AMAZING. It has been a nightmare if you want to check any SQL Server related registry setting. Now you are able to check many of them just using this simple DMV

sys.dm_server_memory_dumps –> this one is keeping info on SQL Server dumps created during a crash so you can find easily the relevant info on the dump such as filename, when it was created and so on. At least now you do not have to browse to the error log folder and browse through all the files there looking for the one created around the crash you’ve been investigating.

sys.dm_server_services –> another just marvelous new DMV! With this one you can check all running services on the given server, their startup type, startup account, startup time and so on. Just keep in mind that this DMV just lists SQL Server and SQL Server Agent services.

sys.dm_os_sys_info –> this one is not new but has two new very important columns concerning virtualization – virtual_machine_type and virtual_machine_type_desc.

The rest of the stuff is for Extended Events and so far this is not my strong side so I will not just copy/paste the new stuff here trying to impress you Smile

Having said that I already have some great improvements in scripts I am regularly using in reports or ad-hoc information collection. The bad thing is that I still cannot lay hands on many (or more than one actually) SQL Server 2008 R2 SP1. I guess that is soon to be changed!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s