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.

   vs.volume_mount_point, -- e.g. C:\
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   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),
   sys.sysaltfiles AS f
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
   f.dbid < 32767
   drive_percent_free DESC,

*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!

SQL Server 2008 R2 Service Pack 1 Released for download

Following the release news around SQL Server it seems that this week is quite busy with the CTP3 of SQL Server Code Name “Denali” and now the brand new Service Pack for SQL Server 2008 R2.

You can download it here.

So what is new in SP1? Well I did not have time to install it (still downloading Smile ) but according to some articles there is going to be a change in the DMVs we are so used to and keen on. One of them would be “sys.dm_exec_query_stats DMV which is extended with additional columns to improve supportabilities over troubleshooting long-running queries”. Of course there is and quite a long list with fixed bugs but I will leave to you to check them out here.

SQL Server Code Name “Denali” CTP3 available for download

OK, I have been waiting for the next CTP release of SQL Server Code Name “Denali” for a bit now and as far as I was able to follow the news I would have expected to see CTP2 instead of CTP3. However, two days ago Microsoft release to the public CTP3 which can be downloaded here (the link is valid as of July 2011).

According to the official communication the CTP3 includes huge number of new capabilities and a lot more functionalities that users can see and test.

Product highlights for this CTP include:

  • Greater availability. Deliver the required 9s and data protection you need with AlwaysOn which delivers added functionality over CTP1.
  • Blazing-fast performance. Get unprecedented performance gains with Project “Apollo,” a new column store index that offers 10-100x star join or similar query performance improvements available for the first customer preview.
    Breakthrough insight. Unlock new insights with rapid data exploration across your organization with Project “Crescent,” available to customers for the first time. Get your hands on the data exploration and visualization tool that everyone is talking about and enables end users to explore data at the speed of thought.
  • Credible, consistent data. Provide a consistent view across heterogeneous data sources with the BI Semantic Model (BISM) a single model for Business Intelligence applications, from reporting and analysis to dashboards and scorecards. Make data quality a part of everyday life with the Master Data Services add-in for Excel and new Data Quality Services that is integrated with third party data providers through Windows Azure Marketplace. Customers can test this functionality for the first time and put data management and cleansing tools in the hands of those who need it.  Available integrated Marketplace providers include:
    • Cdyne: CDYNE Phone Verification will validate the first 7 digits of your phone number(s) and return what carrier the phone number id is assigned to, whether it is a cellular number or a land line, the telco, and additional information including time zone, area code and email address if it is a cellular number
    • Digital Trowel: Powerlinx allows users to send data for refinement and enhancement and receive back cleansed and enriched data. The database contains 10 million company website addresses, 25 million detailed company profiles, and 25 million executives, including 5 million in-depth profiles with email addresses and phone numbers. 
    • Loqate: The Loqate Verify enables users to parse, standardize, verify, cleanse, transliterate, and format address data for 240+ world countries.
    • Loqate: The Geocode enables a latitude-longitude coordinate to be added to any world address with worldwide coverage to city or postal code for over 120 countries
    • Melissa Data: WebSmart Address Check parses, standardizes, corrects and enriches U.S. and Canadian addresses to increase deliverables, reduce wasted postage and printing, and enhance response
  • Productive development experience. Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Developer Tools code name “Juneau” for a unified and modern development experience across database, BI, and cloud functions. Additionally, Express customers can test a new LocalDB version for fast, zero-configuration installation.

Today, you have the chance to preview and test these marquee capabilities and the upgrade or migration experience. CTP3 is a production quality release that includes access to upgrade and migration tools like Upgrade Advisor, Distributed Replay and SQL Server Migration Assistant (SSMA). Upgrade Advisor and Distributed Replay allow you to perform thorough analysis and testing of your current SQL Server applications before upgrading so you know what to expect. You can also use SSMA to automate migrate non-SQL Server databases to SQL Server Code Name “Denali”. For more detailed information about the new SQL Server Migration Assistant v5.1, visit the SSMA team blog.

You can download and test yourself. I hope you enjoy it! Smile

Performance dashboard in SQL Server Management Studio

Amazingly enough I have just learned for a new very convenient way of investigating bad performance. Usually when someone says “My SQL is running very slowly” I log on to the server and start looking at usual Task manager, Activity Monitor and of course – running a bunch of queries against system DMVs and decoding whatever info you find relevant. Well…shame on me Smile There are quite cool SQL Server management reports published just for that purpose and they go under the name  of SQL Server Performance Dashboard reports. They are released for SQL Server 2005 and you can download them from this link.

You can also use them with SQL Server 2008 but with some additional edit of the setup script. And all is very well documented here.

And for those of you who do not enough time to play with it on their own, there is a video that shows pretty much everything you need to know for Performance Dashboard – from setting it up, through using it and performing analysis.

Of course this reports are just providing you the data. You will have to interpret it yourself and turn it into proper information so you can react adequately to the situation Smile

Happy reporting and troubleshooting!

SQL Server performance and troubleshooting tools, tips and tricks

I’ve planned to write a series of post regarding different tools which you can use for performance analysis and troubleshooting but tonight I found that there is already such series posted. I have to say it is pretty neat and has a lot of amazing things to read and get to know. This includes RML Utilities, XPerf, Process monitor, Debug diagnostics and Crash rules. So instead of covering pretty much the same, I’d like to share the round-up post of the series so you can enjoy it.

Troubleshooting SQL: Tools Tips and Tricks round-up

I can only add to this that you can also consider PAL 2.0 (PAL@Codeplex) which  can help you directly analyze performance counters logs and prepare a template for counter collection. Also there is one more thing that I completely love about this tool – almost all counters have thresholds defined according to best practices and added explanation with reference links for each counter. The tool is pretty straight forward and after analysis it provides you with a report with marked problematic areas (IO, CPU, Memory or network).

So get introduced to the post I shared with you and all the tools, tips and tricks it provides and have a look at PAL 2.0 Tool which I think you will find very useful!


Today I am going to share a completely new tool that has been released last couple of weeks. The tool is called “ PSSDIAG/SQLDIAG Manager”. The tools is aiming at providing graphical interface for configuring custom collection sets for SQLDIAG and PSSDIAG utilities. Those two utilities are used for collecting performance and statistic data by Microsoft. I will talk further more on those tools, their aim and how to use them, but it is going to be in another post. For now I will stick to the new tool I am completely thrilled by.

I have experienced a lot of difficulties configuring any of the predefined .XML files used for SQLDIAG collection and now when I have a graphical interface the life just became easier. So let’s start with the quick introduction. The tool has a single screen where you can configure everything:


On the top left we have the menu for choosing platform type – x86, x64 or Itanium. On the top of the screen you can choose the version of the SQL Server that you are preparing the configuration. Of course so far we have SQL Server 7.0, SQL Server 2000, 2005 and 2008. Hopefully we will have soon and SQL 11Winking smile

I was kind of amazed seeing on top left corner a small edit box for the MS Case that the configuration is prepared for. This will not be used by the general DBA however it surely shows that the tool is the same as the one MS Engineers are using (used or modified).

Next we have the connection part:


The important thing here is the Machine name and Instance name configuration. It is important to enter specific instances if you are troubleshooting a server with several instances. Machine name can come useful when you are collecting information for SQL clustered instance.

The other three sections of the screen are for configuring Performance counters collection, predefined diagnostics and traces for DB Engine and Analysis Services engine. I still have not played enough with those in order to share with you how and what but I believe its going to be easy enough for you to check that out by yourself Smile

Once you have the configuration in place you can proceed with saving it. The screen is as follows:


You have the possibility to just have the XML configuration file and use it directly or generate a CAB file which will include everything needed for immediate execution and starting of the collection on the specified instance. Personally I prefer using the CAB file as it comes with several other scripts/configurations.

Download SQLDIAG Manager tool.

Well from now on you can just enjoy configuring SQLDIAG and generate your own investigation templates and scripts. Even if you are not that keen on altering defaults you can still use the tool for entering Machine and Instance name if you need to.

I have to say that with this tool Microsoft have a complete set of tools for performance analysis that are easy to use configure and analyze.

– Configuration of analysis tools – SQLDIAG Manager, PAL Tool

– Analysis – PAL and SQL Nexus

I am planning to do another post for those tools so you can have the big picture in place (if you already don’t have it). So stay tuned!

How to check who performed a SQL Server database backup

In this post I would like to share a simple way of extracting latest database backup including the user that has initiated it. Generally SQL Server engine does not store such info unless you have some custom piece of code (such as triggers) or you are using auditing in SQL Server 2008 and above. As you know each backup triggers an event that is stored in the application log of the operating system. This event includes the message that is written in the SQL Server error log and additionally has an user name, that triggered the event. Based on that and using PowerShell here is a simple list that returns newest 2000 backup events (full, differential or log) for all databases, sorting them descending by time. Columns returned are Time (the time the backup completed), Username (the user performed the backup) and Message(the message, which includes the type of backup and the database).

Get-EventLog "Application" -newest 2000 | Where-Object {$_.EventID -eq 18264 -or $_.EventID -eq 18265 -or $_.EventID -eq 18270}  |sort -descending TimeWritten |
Format-Table TimeWritten,UserName,Message -auto

As you can see I am querying directly the OS Application log and filtering three specific events:

  • EventID 18264 – Database full backup event
  • EventID 18265 – Database log backup event
  • EventID18270 – Database differential backup event

Also keep in mind that Backup and Restore events are not written to SQL Server Error log and NT Application event log if trace flag 3226 is enabled. So before trying to find out who performed a certain backup, check if those events are logged at first placeSmile

How to extract SQL Server non-default configuration settings

Have you ever wondered how easily to extract non-default SQL Server instance settings? Well recently I had to perform such task so I can apply those changed settings to a new instance that was replacing the current one. And the only thing that came to my mind was to extract all settings in Excel and then compare them one by one with the settings of this new instance. Later on when I was browsing in SSMS reports I noticed that in the “Server Dashboard” report there was such section. It was definitely not that visible and I felt quite lucky I found it.


If you have followed my posts, not long time ago I posted a link to SSMS report sources. So far so good, however it was not very comfortable for me to have the configurations without being able to export them (the report can be exported only printed and the table is not selectable at all. So I needed a better way of finding and after that – storing those non-default configuration settings. I checked the source of the Server Dashboard report and I was surprised how simple that was…it was consisting of INSERT section and one simple join which was using data from sys.configuration. Not that big of a deal, you will say, however if you do not know it, it is quite a big deal actually Smile

So there is the script you can use (it is straight extract from the report source):

begin try
declare @configurations_option_table table (
        name nvarchar(128)
,       run_value bigint
,       default_value bigint 
declare @sp_configure_table table (
        name nvarchar(128)
,       minimum bigint
,       maximum bigint
,       config_value bigint
,       run_value bigint 
declare @tracestatus table(
        TraceFlag nvarchar(40)
,       Status tinyint
,       Global tinyint
,       Session tinyint

insert into @sp_configure_table 
select name
,       convert(bigint,minimum)
,       convert(bigint,maximum)
,       convert(bigint,value)
,       convert(bigint,value_in_use) 
from sys.configurations  

insert into @configurations_option_table values('Ad Hoc Distributed Queries',0,0)
insert into @configurations_option_table values('affinity I/O mask',0,0)
insert into @configurations_option_table values('affinity mask',0,0)
insert into @configurations_option_table values('Agent XPs',0,0)
insert into @configurations_option_table values('allow updates',0,0)
insert into @configurations_option_table values('awe enabled',0,0)
insert into @configurations_option_table values('blocked process threshold',0,0)
insert into @configurations_option_table values('c2 audit mode',0,0)
insert into @configurations_option_table values('clr enabled',0,0)
insert into @configurations_option_table values('cost threshold for parallelism',5,5)
insert into @configurations_option_table values('cross db ownership chaining',0,0)
insert into @configurations_option_table values('cursor threshold',-1,-1)
insert into @configurations_option_table values('Database Mail XPs',0,0)
insert into @configurations_option_table values('default full-text language',1033,1033)
insert into @configurations_option_table values('default language',0,0)
insert into @configurations_option_table values('default trace enabled',1,1)
insert into @configurations_option_table values('disallow results from triggers',0,0)
insert into @configurations_option_table values('fill factor (%)',0,0)
insert into @configurations_option_table values('ft crawl bandwidth (max)',100,100)
insert into @configurations_option_table values('ft crawl bandwidth (min)',0,0)
insert into @configurations_option_table values('ft notify bandwidth (max)',100,100)
insert into @configurations_option_table values('ft notify bandwidth (min)',0,0)
insert into @configurations_option_table values('index create memory (KB)',0,0)
insert into @configurations_option_table values('in-doubt xact resolution',0,0)
insert into @configurations_option_table values('lightweight pooling',0,0)
insert into @configurations_option_table values('locks',0,0)
insert into @configurations_option_table values('max degree of parallelism',0,0)
insert into @configurations_option_table values('max full-text crawl range',4,4)
insert into @configurations_option_table values('max server memory (MB)',2147483647,2147483647)
insert into @configurations_option_table values('max text repl size (B)',65536,65536)
insert into @configurations_option_table values('max worker threads',0,0)
insert into @configurations_option_table values('media retention',0,0)
insert into @configurations_option_table values('min memory per query (KB)',1024,1024)
insert into @configurations_option_table values('min server memory (MB)',0,0)
insert into @configurations_option_table values('nested triggers',1,1)
insert into @configurations_option_table values('network packet size (B)',4096,4096)
insert into @configurations_option_table values('Ole Automation Procedures',0,0)
insert into @configurations_option_table values('open objects',0,0)
insert into @configurations_option_table values('PH timeout (s)',60,60)
insert into @configurations_option_table values('precompute rank',0,0)
insert into @configurations_option_table values('priority boost',0,0)
insert into @configurations_option_table values('query governor cost limit',0,0)
insert into @configurations_option_table values('query wait (s)',-1,-1)
insert into @configurations_option_table values('recovery interval (min)',0,0)
insert into @configurations_option_table values('remote access',1,1)
insert into @configurations_option_table values('remote admin connections',0,0)
insert into @configurations_option_table values('remote login timeout (s)',20,20)
insert into @configurations_option_table values('remote proc trans',0,0)
insert into @configurations_option_table values('remote query timeout (s)',600,600)
insert into @configurations_option_table values('Replication XPs',0,0)
insert into @configurations_option_table values('RPC parameter data validation',0,0)
insert into @configurations_option_table values('scan for startup procs',0,0)
insert into @configurations_option_table values('server trigger recursion',1,1)
insert into @configurations_option_table values('set working set size',0,0)
insert into @configurations_option_table values('show advanced options',0,0)
insert into @configurations_option_table values('SMO and DMO XPs',1,1)
insert into @configurations_option_table values('SQL Mail XPs',0,0)
insert into @configurations_option_table values('transform noise words',0,0)
insert into @configurations_option_table values('two digit year cutoff',2049,2049)
insert into @configurations_option_table values('user connections',0,0)
insert into @configurations_option_table values('user options',0,0)
insert into @configurations_option_table values('Web Assistant Procedures',0,0)
insert into @configurations_option_table values('xp_cmdshell',0,0)

insert into @tracestatus exec('dbcc tracestatus')
update @tracestatus set TraceFlag = 'Traceflag ('+TraceFlag+')'

select 1 as l1
,       st.name as name 
,       convert(nvarchar(15),st.run_value) as run_value
,       convert(nvarchar(15),ct.default_value) as default_value
,       1 as msg  
from @configurations_option_table ct 
inner join  @sp_configure_table st on (ct.name = st.name  and ct.default_value != st.run_value)
select 1 as l1
,       TraceFlag as name
,       convert(nvarchar(15), Status) as run_value
,       '0' as default_value
,       1 as msg  
from @tracestatus where Global=1 order by name
end try
begin catch
select -100 as l1
,       ERROR_NUMBER() as name
,       ERROR_SEVERITY() as run_value
,       ERROR_STATE() as default_value
,       ERROR_MESSAGE() as msg
end catch