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
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.
[sourcecode language="sql"] 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]; [/sourcecode]
*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
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!