SQL 2008 R2 Service Pack 1 (SP1) available for download.

Maybe you have read it already but since 11 july 2011 Microsoft SQL Server 2008 R2 Service Pack 1 is available for download. See: http://www.microsoft.com/download/en/details.aspx?id=26727

Beside a lot of bugfixes which are listed in this Master KB article , some existing functionality is improved: .

1. Dynamic Management Views for increased supportability: sys.dm_exec_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.
2. ForceSeek for improved querying performance :Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.
3. Data-tier Application Component Framework (DAC Fx) for improved database upgrades:The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.
4. Disk space control for PowerPivot:
   This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.

Perfect demo of SQL Server Project "Crescent" Denali.

Last year I blogged about: SQL Denali codename Crescent What is it. This was based on my visit of the head quarters of Microsoft in Redmond. Product managers from the SQL Server team showed to me the great possibilities of project: Crescent. Unfortunaltely this event was under NDA, so I could not blog about it. But with the availability of SQL Denali CTP3, you can play your self with Crescent. Project Crescent is a new immersive ad-hoc visualization tool that is part of SQL Server Code Name "Denali" Reporting Services. Project "Crescent" is designed with end users in mind to quickly, easily, and visually explore their data and answer ad-hoc questions in just a few clicks. Have a look to the next video to understand the great possibilities.



One thing to keep in mind: Crescent will only be available in SharePoint mode. For companies without an ICT department, deploying and maintaining of a Sharepoint server can be too complex. I think that the decision makers of these companies will have the same need to 'play' with the data.

To start your experience with Crescent you can download SQL Server code name Denali CTP 3 from here.

More tips to improve performance of SSRS reports.

In the past I have written something about performance in combination with SQL Server Reporting Services.See  Analyze performance of your reporting services reports by using SSRS statistics and Tips to improve performance of MS Reporting service reports (SSRS). In this blog post I will give some more tips to analyze and improve the performance of your SQL 2008 R2 SSRS reports.

To analyze the performance of your SSRS report (RDL) you need to understand what will have impact on the total time to generate the reports. The total time to generate a reporting server report (RDL) can be divided into 3 elements:

1. Time to retrieve the data (TimeDataRetrieval).
2. Time to process the report (TimeProcessing)
3. Time to render the report (TimeRendering)  

Total time = (TimeDataRetrieval) + (TimeProcessing) + (TimeRendering)

As of SQL Server 2008 R2, this 3 performance components are logged every time for which a deployed report is executed. This information can be found in the table Executionlog3 in the ReportServer database. You can use next query:

SELECT TOP 10 Itempath,parameters,
     TimeDataRetrieval + TimeProcessing + TimeRendering as [total time],
     TimeDataRetrieval, TimeProcessing, TimeRendering,
     ByteCount, [RowCount],Source, AdditionalInfo
FROM ExecutionLog3
ORDER BY Timestart DESC

1. Itempath
This is the location and name of the executed report (RDL)

2. Parameter
The parameter values used to execute the report.

3. TimeDataRetrievalThe number of milliseconds spent interacting with data sources and data extensions for all data sets in the main report and all of its subreports. This value includes:

• Time spent opening connections to the data source
• Time spent reading data rows from the data extension

TimeDataRetrieval is is the time needed for SQL Server to retrieve the data of all datasets in your report. This is time spent openings connections to the data source and time spent reading data rows from the data extension. Keep in mind that all dataset defined in your report will be retrieved. Even if you do not use it in the report to display the data. Remove datasets which are not used to display data. Datasets will be executed in parallel, by making use of multiple database connections. In next example you will that my report is started at 2011-06-29 14:16:42.677 and ended at 2011-06-29 14:16:46.757. Total time to retrieve the data = 4 seconds and 80 milliseconds.  However every the total time of every single data set is much more: 235 + 1242 + 2442 + 3470 + 3678 + 4069 = 15 seconds and 136 milliseconds.

In this example I have a server with multiple CPU's in it. Every dataset is retrieved using a seperate database connection. (See SPID 61, 63,64,65,66,67,68). Execution these datasets over multiple database connections saves a lot of time. TimeDataRetrieval contains the duration of the longest DataSet

4. TimeProcessingThe number of milliseconds spent in the processing engine for the request. This value includes:

• Report processing bootstrap time 
• Tablix processing time (e.g. grouping, sorting, filtering, aggregations, subreport processing), but excludes on-demand expression evaluations (e.g. TextBox.Value, Style.*)
• ProcessingScalabilityTime (see AdditionalInfo column)

Processing time can be very high if you have datasets with a big number of records for which a lot of GROUP BY and SORTING need to be done.

 5. TimeRenderingThe number of milliseconds spent after the Rendering Object Model is exposed to the rendering extension. This value includes:

• Time spent in renderer
• Time spent in pagination modules
• Time spent in on-demand expression evaluations (e.g. TextBox.Value, Style.*). This is different from prior releases of SQL server, where TimeProcessing included all expression evaluation. 
• PaginationScalabilityTime (see AdditionalInfo column)
• RenderingScalabilityTime (see AdditionalInfo column)

 6. ByteCount
Total number of bytes received from all datasets in the report.

7. RowCount
Total number of records received from all datasets in the report.

8. SourceSpecifies the type of the execution. It is one of the following values: Live, Cache, Snapshot, History, AdHoc, Session, Rdce

• Live indicates a live executed dataset queries. 
• Cache indicates a cached execution, i.e. dataset queries are not executed live.
• AdHoc indicates either a dynamically generated report model based drillthrough report, or a Report Builder 2.0 report that is previewed on a client utilizing the report server for processing and rendering.
• Session indicates a subsequent request within an already established session (e.g. the initial request is to view page 1, and the subsequent request is to export to Excel with the current session state).
• Rdce indicates a Report Definition Customization Extension (RDCE; a new feature in RS 2008) that can dynamically customize a report definition before it is passed to the processing engine upon report execution.

The information in the Executionlog3 table will help you to find the bottlenck in your report. What to do in the situation of:

1. High TimeDataRetrieval. 
• Remove not used datasets from your report. Every dataset will be executed, even if it is not used to display data.  
• Use SQL Profiler to analyze the data set query to see where you can improve your data set. See Guidelines to write well performing queries.
• Check if the RowCount (number of records returned by all datasets) is not too big. Ask yourself is all data needed in the report. Data which is not needed should not be retrieved.
• Data set will be executed once. You can use one data set for multiple report parts like tablix, chart, lists, sprakline, indicator, data bar, map, gauge etc. Look if you can combine two or more data sets which are almost identical to one dataset. 
2. High Timeprocessing.
• Processing the data is done sequentially. The more report parts like tablix, chart, lists, sprakline, indicator, data bar, map, gauge etc. you put on your report, the more time it will take to process the report. 
• Minimize the number of records of your data set to process. Do you need all data, or can you group and sort the data in your dataset. This will lower the result set which will results in faster processing time. In most situations the SQL Server engine will group data much faster in comparions with Reporting Services. 
3. High TimeRendering.
• Be critical if all rendering is needed.

Enjoy it, to deliver a great user experience to your users by improving the performance of your reports.