June 2008 - Posts

SQL Server 2008 - Performance Studio

I've been looking at this new feature of SQL Server 2008 in order to demo it to customers, and I have to say it's pretty cool ! Along with Resource Governor and compression, it's one of my favourite new features and a good reason to consider an upgrade. Performance Studio builds on the concept of the Database Reports in SQL Server 2005 and the Performance Dashboard introduced in SQL Server 2005 SP2. Like it's predecessors, it's built on top of standard DMV's but with one crucial advantage... data is historical and is persisted across service restarts. Previously, you had to roll your own code in order to persist DMV data, now SQL Server 2008 gives you it straight out the box.

Setting it up is easy... expand the 'Management' folder and then right-click 'Data Collection' then 'Configure Management Data Warehouse'. This opens a wizard which guides you through the configuration of the Management Data Warehouse (MDW). Select the 'Create or Upgrade a Management Data Warehouse' option and enter your server details. You can either configure a new database or use an existing one in order to collect the performance data.


Once the MDW has been configured, run through the wizard again and select the 'Set up Data Collection' option. At this point you need to also ensure that SQL Server Agent is running or else the performance counters will not be uploaded to the database. Also, SQL Server Integration Services is required to manage collections. There are 3 new database roles in the MDW database: MDW Admin, MDW Writer, MDW Reader. It's a good idea to restrict access to this database, especially if you are collecting sensitive data. Performance Studio will only collect data against SQL Server 2008 databases, so unfortunately you can't use it to monitor older versions of SQL Server. MSDB is used to store the log entries, Agent jobs and SSIS packages.

When creating the MDW, plan for data growth of up to 250 - 500 MB a day, depending on your query plans and consider running an archive job to aggregate summary data before SQL Server runs it's purge job after 14 days. Regarding performance overhead, Microsoft detected approximately 3 - 4% increase in CPU performance on it's TPC-C tests, which is fairly low overhead.

Performance Studio comes with 3 built-in Collection Sets: Disk Usage Collection Set, Query Activity Collection Set and Server Activity Collection Set. The Disk Usage Collection Set collects data every 6 hours and retains it for a default of 730 days. It gathers data and log disk usage and plots them over time. This gives a nice visual view of data file growth over time.


The Query Activity Collection Set uploads query activity every 15 minutes and retains it for 14 days. It caches active sessions and requests from DMV's every 10 seconds. It uses dm_exec_query_stats and uploads the 3 most "interesting" queries and any queries where the query plan has changed. What constitutes an interesting query, I have no idea :-) These can then be viewed graphically based on CPU, Duration, Total I/O, Physical Reads or Logical Writes. You can drill-down into the reports to show the query text, query plan, showplan and any missing indexes identified that could improve the query execution.


The Server Activity Collection Set may turn out to be the most useful performance tuning weapon. Data is uploaded every 15 minutes to the MDW and is collected every 10/60 seconds depending on the particular counter. This data is retained for 14 days before being purged. It collects data on Server CPU usage, Memory, Waitstats, Disk I/O and Network Usage, amongst others. Again, you can click through these reports for detailed information.


In addition to the built-in functionality, you can also create your own collection sets, however be careful doing this or you may end up collecting huge amounts of data, particularly if you run a SQL Trace collection. However, if you do want to run a Profiler collection, set up a trace in Profiler, select "Export" then "Script trace definition for SQL Trace Collection Set" option. This will then script out the XML trace definition for you which can then be executed against your SQL Server system... pretty cool !



For more information, see the following Webcast. It's well worth taking a look !



Rob Carrol

PFE SQL Server

Microsoft UK

Microsoft SQL Server 2008 Feature Pack RC0, June 2008

The Report Builder in the Feb CTP was a preview and doesn’t ship in the RTM box. However, Report Builder 2.0 is in the RC0 Feature Pack from here:



CU# 8 for SQL Server 2005 SP2 Released
Today (6/16/2008) we shipped the eighth Cumulative Update for SQL Server 2005 SP2 On Time This CU represents·         50 Resolved Issues·         39 Unique Customer Requests All teams across SQL and CSS deserve kudos for their continued success in shipping Cumulative Update releases.  We continue to make improvements to the CU process and we appreciate your feedback; please look for CU#9 announcement soon!  As per POR, this CU is not released as a public download, but as a hotfix download to http://hotfix.  The associated cumulative KB article has also been published, whereby customers are directed to contact CSS to get the CU build. Customers can also obtain the hotfix package through the new self service feature by clicking on the “Hotfix Download Available” button found at the top of the KB article.  Public KB Article


  Itay Braun                        Premier Field Engineer - SQL Server and BI                        Microsoft Services - UKE-Mail: itayb@microsoft.com       Mobile: +44-796-928-9996         Blog: http://sqlblogcasts.com/blogs/thepremiers/Veni              Vidi              Fixit 
SQL 2008 OpsDay - Data and Backup Compression

Hi all,

Just wanted to thank all the attendees that participated to yesterday's event on SQL Server 2008 in TVP - Reading.


We've received great feedback on the content of the day, and I really hope everybody went back home (for some of you I heard it was quite a long way!) sure that SQL Server 2008 is a great product full of new features and enhancements which are just what you need for your business activities.


I want to follow-up some questions which I've received during the event, and leave some links to review if you want to further learn about Data and Backup Compression.




"Data compression is an enterprise only feature. Is it possible though to restore compressed backup on standard?"


Though creating compressed backups is supported only in SQL Server 2008 Enterprise Edition and later, every SQL Server 2008 or later edition can restore a compressed backup.


"Are there some guidelines to tune performances of backup compression"?


Yes, one interesting article from Technet and from SQL CAT:


  • Tuning the Performance of Backup Compression in SQL Server 2008


  • How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)




Useful information:


Reducing Database Size by Using Vardecimal Storage Format



Creating Compressed Tables and Indexes



SQL Server Storage Engine - Data Compression Series



Backup Compression (SQL Server)



SQL Server 2008: Backup Compression



SQL Server 2008: Backup Compression CPU Cost



Download the last CTP



For any additional question, please contact myself directly at beatrice.nicolini@microsoft.com and read my blog at http://blogs.technet.com/beatrice.


- Beatrice Nicolini -



This Blog