Keep It Simple and Fast

Analyze performance between SQL Azure and SQL Server on premise.

2012-01-31T18:03:00.000+01:00

You need to be convinced that the performance in SQL Azure is acceptable for your end users before you can move you ron premise databases to SQL Azure. In the on-premise environment you have a lot of tools which you can use to measure the SQL performance of your application. However, in SQL Azure the tools are not so good as the on–premise versions. For instance:

You can’t connect with SQL Profiler to a SQL Azure database.
You can’t connect with Windows performance monitor (Perfmon) from an Azure worker role to your SQL Azure database server.

I strongly hope that this will be improved by Microsoft in the future. In this blog I will describe what you can do to analyze performance of your application in SQL Azure. Most of the methods requires a lot of manual work, but it is better than nothing.

First of all you need to upload a version of your on premise database to SQL Azure. Use the SQL Azure Migration Wizard. The SQL Azure Migration Wizard is an open source application, which is designed to help you to migrate your SQL Server 2005/2008/2008R2/2012 databases to SQL Azure. SQL Azure Migration Wizard will analyze your source database for compatibility issues and allow you to fully or partially migrate your database schema and data to SQL Azure. SQL Azure Migration Wizard requires SQL 2008 R2 SP1.

After uploading your database to SQL Azure we can start comparing query performance between the on-premise database and the SQL Azure database. Take into account that latency between your test load application and the SQL Azure database should be minimized. This can be done in 2 ways:

Use queries for which the result set is minimal. For instance SELECT COUNT(*) FROM TABLEX will result in one number. This is a minimum number of bytes to transfer to the client. SELECT * FROM TABLEY will result in a lot of data transfer from SQL Azure server to the client.
Execute queries from a Azure worker role which is hosted in the same data center as your SQL Azure server.

Record with SQL profiler some queries from your on premise solution. Store these queries in a SQL script file. In this SQL script file add next command before every query.

PRINT 'Query: Cashflow entries to be allocated 1'

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT Columns FROM MYtable

Add next command after every query:
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
PRINT '----------------------------------------------------------------------'

The SQL Script will be executed in SQL Server Management Studio (SSMS) . Enable Include Client Statistics. (Shift-ALT-S)

Result of the query is printed on the Results tab in SSMS

The IO and Time statistics are printed on the message tab in SSMS

SET STATISTICS IO ON: Will generate ‘SQL Profiler’ read statistics per query.
SET STATISTICS TIME ON: Will generate ‘SQL Profiler’ CPU Time and total elapsed query time.
The client statistics are printed on the Client Statistics tab.

To measure the total of all queries in one script add next command to the script.
DECLARE @STARTTIME DateTimeDECLARE @ENDTIME DateTime
SET @STARTTIME = GETDATE()
Query 1
Query 2
….
Query X
At the bottom of the script add next syntax

SET @ENDTIME = GETDATE()
SELECT GETDATE(),DATEDIFF (ms, @STARTTIME, @ENDTIME) AS QueryTime

After executing the script the last result set in the Results tab will display the execution time and exection time of the total script.

Now your script is READY for testing. Execute the script on:

The on premise database
SQL Azure database

Compare the results between the on premise results and the SQL Azure results.

In the Management Portal for SQL Azure you can get an overview of the query performance.

Thinks to take into account:

Use only SELECT queries which enables you to redo test a lot of times on the SQL Azure database without the need to restore the database.
If you plan to use INSERT, DELETE and UPDATE statements, you need to have a backup of your SQL Azure database. Backup and Restore is not supported in SQL Azure at this moment but you can use the CREATE DATABASE XXX AS COPY of YYY statement. This will create a copy of your database using a new database name.

CREATE DATABASE destination_database_name
AS COPY OF [source_server_name.]source_database_name

To copy the Adventure Works database to the same server, I execute this:
CREATE DATABASE [AdvetureWorksBackup]
AS COPY OF [AdventureWorksLTAZ2008R2]

Observations:

SQL Azure execute queries using one processor (MAXDOP 1). Parallelism is not possible.
Dynamic Views in the manage portal contain history for a small period. It’s difficult to see long running queries for a longer period. This happens because you will be connected to one of the 3 copies of your database. You never know to which of the copies you will be directed. Every copy will have it’s own content in the DMV’s .
Performance is not guaranteed on SQL Azure.
In the tests I have executed so far, the SQL Azure database (8 GB Business Edition) is significant slower in comparison with a SQL database on my laptop. (DELL Latitude E6410). One of the reasons is the single processor usage of SQL Azure.

Reasons and considerations to move from on premise SQL server to SQL Azure in the cloud.

2012-01-25T11:51:00.000+01:00

A lot of times I got questions from people why to use SQL Azure instead of using the on premise version. I strongly believe that the cloud is the future. It’s the only way to go. Big question to answer: Is it already good enough to say good bye to the on premise version? In general this question can be answered with all advantages of using the cloud but thers are still some limitations.

Advantages:

It allows you to scale up and pull back capacity as required, enabling services to be arranged around periods of peak demand
24/7 availability (99,9% monthly SLA)
Pay for what you uses. Low upfront costs
Anywhere access
Always use of the latest software
Reduced management

No patching or maintenance on SQL server level. SQL Azure administers the physical hardware such as hard drives, servers, and storage. However, database maintenance is still needed like index fragmentation and update statistics.
Every database has built-in high-availability, failover, and redundancy. Every SQL database is replicated twice (to make a total of three databases) over different hardware boundaries in the same datacenter.

No backups needed for data disaster recovery. However, backups for user failures are still needed.
Shift in focus on SQL development by Microsoft from on premise to SQL Azure. At this moment SQL Azure is already running on version (11.0.1820) while the latest on premise version is running on version 10.50.2500 (SQL 2008 R2 SP1).

So moving to the cloud with your database will have advantages but you should look to the limitations of the current version.

Limitations:

No performance guarantees at this moment. Part of the reason for this is the multitenant problem: many subscribers with their own SQL Azure databases share the same instance of SQL Server and the same computer, and it is impossible to predict the workload that each subscriber’s connections will be requesting. However, not having guarantees doesn’t mean that performance is not a critical aspect of the design of the SQL Azure infrastructure. SQL Azure provides load balancing services that evaluate the load on each machine in the data center. When a new SQL Azure database is added to the cluster, the Load Balancer determines the locations of the new primary and secondary replicas based on the current load on the machines. If one machine gets loaded too heavily, the Load Balancer can move a primary replica to a machine that is less loaded. The simplest way to do this move is to switch a primary and secondary replica for a SQL Azure database that is performing sluggishly. This switch can have a major and immediate impact on performance, because all reads and writes take place on the primary replica. See: Inside SQL Azure
SQL Azure provides a significant subset of the functionality of SQL 2008 R2, it does not currently have complete feature parity. For instance:

No fill factor support for indexes.
No Full-Text search support.
Cross database queries are not possible.
No support for page level or row level compression like the enterprise edition of SQL Server 2008. Work around is use the .NET CLR 2.0 System.IO.Compression namespace in your data layer
No Azure version available of SSAS and SSIS. I expect it will come in the future but it is unknown when this will come available.
No SQL Server Agent to schedule jobs. You need to use a Windows Azure worker role and create your own custom code to schedule jobs.
Performance analysis tooling is minimal. SQL Profiler is still not supported but here you can vote to get it higher on the priority list. A lot of DMV's are added to get insight in query performance. However you can't get insight in query details for one application of user connection.

Maximum of 149 user database per SQL Azure Server + one master database = 150 databases.
Maximum database size 150 Gb. Size limitations can be mitigated through the implementation of sharding which distributes load across multiple SQL Azure databases.
Clustered index requirement before inserting data. This will make a clustered index change more complex. Because you can’t drop the clustered index if there is data in the table.
SQL Azure Reporting still in CTP. No support for custom assemblies. This is needed to support multi languages and localization in reports.
Performance of SQL Azure Reporting is not consistent. This is because not contain processing cache feature. I hope this will be fixed when general available.
What to do to improve performance? I can’t add memory or add spindles to my SQL Azure database server. Is the only solution to split up my database in multiple databases using SQL Azure Elasticity?
Application can be hosted in your own data center. However to minimize network latency you should host your application in the Windows Azure platform.
More limitations can be found here.

Conclusion: One year ago, I already blogged about difference between SQL Azure and SQL 2008. A lot is done in the last year. Development of the (SQL) Azure platform is going very fast, but still a lot need to be done. This is not strange because it takes time to make all on premise functionality available in the cloud. I’m looking forward what is coming in the coming year(s).

SQL 2012 will be launched on 7th march 2012

2012-01-24T16:01:00.000+01:00

On 7th March 2012, it is time to launch SQL 2012, codename 'Denali'. As we have seen in the past, this does not mean that the bits are general available at this time. In the case of SQL Server 2008 R2, Microsoft’s release to manufacturing (RTM) of the product preceded by about a month the date on which most customers could actually get the bits.

Interesting changes can be found in the BI stack, which is called Power View. (codename 'Crescent'). The biggest disadvantage of Power View is the need to have Sharepoint. A lot of organizations do not have the knowledge to install and maintain a Sharepoint server. Please read next 2 blogpost i mande in the past about Power View.
Pefect demo of SQL Server project Crescent.
SQL Denali Codename Crescent: What is it?

Enjoy it.

Overview performance articles on my blog

2011-12-13T18:23:00.002+01:00

Over the last years I have blogged about a lot of topics related to the performance of SQL Server and SQL Reporting services. In this blog I will give an overview of the different articles I have published in the last 2 years.

SQL Server:
Monitoring

Index management

Other

Reporting Services:

SQL Azure

SQL Azure Reporting:

Executionlog of SQL Azure Reporting reports.

Executionlog of SQL Azure Reporting reports .

2011-11-28T21:43:00.000+01:00

In one of my previous blogs I wrote about performance tips to improve the performance of your SSRS reports. In this blog I wrote about the 3 different performance elements during the execution of a report:

Time to retrieve the data (TimeDataRetrieval).
Time to process the report (TimeProcessing)
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. In SQL Azure Reporting you can't access the ExectionLog3 table, however it is still possible to get the contents of this table. To get the contents of this table do the following:

Login to the Azure Management Portal.
Select Reporting
Select your reporting subscription.
Press the Download Execution Log button in the top of the management portal.
Select the date you want to export.
Open the downloaded CSV file in Excel.

How to find table scans in your database.

2011-11-17T18:04:00.000+01:00

Table scans are in most scenarios not the best way to retrieve records from the database. However, sometimes is only possible by executing a table scan because no useful indexes are available. In this situation, you can avoid table scans by adding an index. First of all, how many table scans occur in your database? This information can be retrieved with Performance Monitor, but it is also possible to execute a query. Next script is very usefull to use to get information of your SQL Server.

DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE
WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$'+@@SERVICENAME+':'
END;

-- Capture the first counter set
SELECT CAST(1 AS INT) AS collection_instance ,[OBJECT_NAME],counter_name ,
instance_name,cntr_value,cntr_type,CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_init
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix+'Access Methods'
    AND counter_name = 'Full Scans/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Access Methods'
    AND counter_name = 'Index Searches/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Buffer Manager'
    AND counter_name = 'Lazy Writes/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Buffer Manager'
    AND counter_name = 'Page life expectancy')
   OR ( OBJECT_NAME = @CounterPrefix+'General Statistics'
    AND counter_name = 'Processes Blocked')
   OR ( OBJECT_NAME = @CounterPrefix+'General Statistics'
    AND counter_name = 'User Connections')
   OR ( OBJECT_NAME = @CounterPrefix+'Locks'
    AND counter_name = 'Lock Waits/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Locks'
    AND counter_name = 'Lock Wait Time (ms)')
   OR ( OBJECT_NAME = @CounterPrefix+'SQL Statistics'
    AND counter_name = 'SQL Re-Compilations/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Memory Manager'
    AND counter_name = 'Memory Grants Pending')
   OR ( OBJECT_NAME = @CounterPrefix+'SQL Statistics'
    AND counter_name = 'Batch Requests/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'SQL Statistics'
    AND counter_name = 'SQL Compilations/sec')

-- Wait on Second between data collection
WAITFOR DELAY '00:00:01'

-- Capture the second counter set
SELECT CAST(2 AS INT) AS collection_instance ,[OBJECT_NAME],counter_name ,
     instance_name,cntr_value,cntr_type,CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_second
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix+'Access Methods'
     AND counter_name = 'Full Scans/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Access Methods'
    AND counter_name = 'Index Searches/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Buffer Manager'
     AND counter_name = 'Lazy Writes/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Buffer Manager'
    AND counter_name = 'Page life expectancy')
   OR ( OBJECT_NAME = @CounterPrefix+'General Statistics'
    AND counter_name = 'Processes Blocked')
   OR ( OBJECT_NAME = @CounterPrefix+'General Statistics'
    AND counter_name = 'User Connections')
   OR ( OBJECT_NAME = @CounterPrefix+'Locks'
    AND counter_name = 'Lock Waits/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Locks'
    AND counter_name = 'Lock Wait Time (ms)')
   OR ( OBJECT_NAME = @CounterPrefix+'SQL Statistics'
    AND counter_name = 'SQL Re-Compilations/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'Memory Manager'
    AND counter_name = 'Memory Grants Pending')
   OR ( OBJECT_NAME = @CounterPrefix+'SQL Statistics'
    AND counter_name = 'Batch Requests/sec')
   OR ( OBJECT_NAME = @CounterPrefix+'SQL Statistics'
    AND counter_name = 'SQL Compilations/sec')

-- Calculate the cumulative counter values

SELECT i.OBJECT_NAME , i.counter_name , i.instance_name ,
    CASE WHEN i.cntr_type = 272696576
    THEN s.cntr_value - i.cntr_value
    WHEN i.cntr_type = 65792 THEN s.cntr_value
    END AS cntr_value
FROM #perf_counters_init AS i
JOIN #perf_counters_second AS s
ON i.collection_instance + 1 = s.collection_instance
   AND i.OBJECT_NAME = s.OBJECT_NAME
   AND i.counter_name = s.counter_name
   AND i.instance_name = s.instance_name
ORDER BY OBJECT_NAME

-- Cleanup tables
DROP TABLE #perf_counters_init
DROP TABLE #perf_counters_second

After executing this script you need to look to the values of : Full Scans/sec and Index searches/sec
Index scans should at least 300 times be bigger than Full Scans. To lower the number of Full Scans, you need to identify the queries which are using a table scan in their execution plan. This information can be retrieved from the procedure cache. In the execution plan you need to look for the syntax: 'TableScan'. This can be done with next query.
SELECT TOP 50 st.text AS [SQL], cp.cacheobjtype, cp.objtype,
   DB_NAME(st.dbid)AS [DatabaseName], cp.usecounts AS [Plan usage] , qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE ('%Tablescan%')
ORDER BY usecounts DESC

Now, we have the query which you can analyze in SSMS.

My experience with (SQL) Azure. Azure Reporting and Azure Data Sync Preview.

2011-11-10T20:38:00.003+01:00

In the past I have blogged about my first experience with SQL Azure and my first experience with SQL Azure Data Sync. In this blog I will give an update. A lot is changed. So time for an update.

All Azure services are combined in one portal: The Azure Management portal.

In this portal you have access to all relevant Azure services. For instance Database for SQL Azure, Data Sync for the Azure Data Sync framework and Reporting for Azure Reporting Services. I'm using Database, Data Sync and Reporting at this moment.
The performance of the management portal differs between the different Azure services. Database and Reporting responds with an acceptable performance. The Data Synce response times is too slow. A new management portal will be introduced in the Service release Q4 2011 in Metro Style. I do not like that every time the complete look and feel of the management portal is changed. Think before you deploy.
Firewall still only supports IP version 4. IP version 6 adresses are not allowed.
SQL Azure and Azure Reporting Server is now available in datacenters all over the world.
Maximum database size is 50 Gb. This will increase to 150 GB as of service release Q4 2011.
Database backup can be made by creating a copy of your database with next command:
CREATE DATABASE Database1B AS COPY OF Database1A.
Be aware that you now pay for an additional database because you created a second database. As alternative you can use BACPAC to store the backup in Azure Blob storage.
SQL Profiler is still not supported but here you can vote to get it higher on the priority list. A lot of DMV's are added to get insight in query performance. However you can't get insight in query details for one application of user connection.
The interface of Data Sync Preview version is much improved in comparison with the CTP2 version. However as mentioned in point 1, the performance is bad. For instance the refresh of schema information.
Data Synchronization of column names with spaces is not supported in the preview version. This will be fixed in the service release of end November 2011.
You can synchronize a subset of a table by selecting the columns you need to sync and you can configure row filtering by defining a filter clause.
Changing a sync group is still not possible. This is a missing feature. You need to remove the sync group and create a new one. This is a lot of manual work.
Changes in your data schema is still not support in Azure Data Sync.
Deploy of on-premise reports to Azure reporting is really simple. You can deploy your on-premise reports to Azure reporting without changing the report. You only need to change the reporting server to which you want to deploy. Reports are the same only the URL is different.
Management portal for Reporting looks good and contains all basic functions you need.
Rendering of Azure reports are slow in comparison with SSRS on premise reports. I have uploaded some of my on premise reports to Azure reporting. I have uploaded my test database to SQL Azure. Azure Reports are genereated on the Azure database. This works great. Only the performance is different. After downloading the execution log, I could see that the performance decrease was caused by the rendering process. On my premise SSRS server it took 50 milliseconds to render this report. In Azure Reporting is took 6 seconds to render. To be clear, I used the same report on the same dataset. Click here to see that somebody else encoutered the same performance decrease. What can I do to improve the performance of my reports on the Azure Reporting server?
As you can see a lot is changed and good progress is made in the last year with the Azure platform. A big shift is made, SQL Azure is now in the lead in comparison to SQL Server on- premise. For instance SQL Azure is already running on SQL 2012 (codename Denali). Engine version 11.0.1476. SQL 2008 R2 is version 10.50.2500
Performance of applications, reports etc. is something I always keep in mind. A lot is done the last year to give the user more insight in how his application\reports are running in the Azure platform. It is not a black box anymore, but it is still dark gray. For a commercial use it should be clear why and how you can improve performance.

An impression to your future, with an important role for BI

2011-11-09T12:14:00.000+01:00

Microsoft published a new video that shows how the company believes technology is poised to evolve over the next five to 10 years, based on the trends its researchers and engineers are seeing in software, devices, displays, sensors, processors and intelligent systems. You will see a lot of BI in this movie. BI will help you to get the information in the way you want and need it.

Enjoy it to have a look into your future.

How to implement style sheets in your SSRS reports?

2011-11-04T14:20:00.000+01:00

Within SQL Server Reporting Services (SSRS), you have a lot of ways to style your reports. You can style your report in the way you want by using colors for text and background, fonts, font sizes etc... For instance:

However, these styles are 'hard coded' in the report. You can see this in the RDL file of the report.

When the report is rendered as a HTML page it is not possible to allow Cascading Style Sheets (CSS) to be used. It is even not possible to enable a style to be configured by the user. This is a missing feature in SQL 2008 R2. SQL 2012 (codename Denali) will also not support style sheets. Style sheets can be very useful. For instance:

Assume you have applied your corporate branding to all your reports. Now the marketing department decide to change the corporate branding. You need manually change all your reports.
As an account you have build a report for your customer X in his corporate branding. This reports can't be re-used for customer Y. You need to build a second version of the report in the corporate branding of company Y.

In this blogpost I will describe what you can do to apply styles to your reports.

The style definition will not be stored in the report but in the database. By doing this, you can easily add new styles without the need to change your report. The report will make use of a Style parameter called @ReportStyeId. This parameter will retrieve the Style definition and will apply it to the report.

First we need to create some tables to store the style definition.

CREATETABLE SSRS_ReportStyle
   (ReportStyleId INT IDENTITY (1, 1),
    StyleName VARCHAR (80))

CREATE TABLE SSRS_Element
   (ElementId INT IDENTITY (1, 1),
    ElementName VARCHAR (80),
    ElementDefaultValue VARCHAR (80))

CREATE TABLE SSRS_ReportStyleElements
(ReportStyleId INT,
   ElementId INT,
   ElementValue VARCHAR (80))

Add some style definition data to the tables:
-- SSRS_Report Style
SET IDENTITY_INSERT [SSRS_ReportStyle] ON
INSERT INTO [SSRS_ReportStyle] (ReportStyleId,StyleName)
   VALUES (1, 'Blue Yellow')
INSERT INTO [SSRS_ReportStyle] (ReportStyleId,StyleName)
   VALUES (2, 'Red')
SET IDENTITY_INSERT [SSRS_ReportStyle] OFF

-- Insert SSRS_Elements like for instance font colors and background colors. Each element will
-- have a default value which will be used in case this element is not defined in the style.
SET IDENTITY_INSERT [SSRS_Element] ON
INSERT INTO [SSRS_Element]([ElementId],[ElementName],[ElementDefaultValue] )
   VALUES (1,'TABLE_HEADER_BG', 'WHITE')
INSERT INTO [SSRS_Element] ([ElementId],[ElementName],[ElementDefaultValue] )
   VALUES (2,'TABLE_HEADER_TEXT','BLACK')
INSERT INTO [SSRS_Element] ([ElementId],[ElementName],[ElementDefaultValue])
   VALUES (3,'TABLE_DETAIL_BG','WHITE')
INSERT INTO [SSRS_Element] ([ElementId],[ElementName],[ElementDefaultValue])
   VALUES (4,'TABLE_DETAIL_TEXT','BLACK')

-- SSRS_Report Style Elements, for every style element we can define the color, size etc.
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
   VALUES ( 1, 1, 'BLUE' )
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
   VALUES ( 1, 2, 'WHITE' )
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
   VALUES ( 1, 3, 'YELLOW')
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
   VALUES ( 1, 4, 'BLUE')
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
   VALUES ( 2, 1, 'RED' )
INSERT INTO [SSRS_ReportStyleElements] ([ReportStyleId], [ElementId], [ElementValue] )
   VALUES ( 2, 2, 'YELLOW' )

Now, we have the style definition stored in the database. It's time to create a dataset to retrieve the style which can be used by the report Style parameter @ReportStyleId. The resultset of the dataset is 1 record with a column for every used style element. In this example I have 4 style elements. 1) Background color header 2) Background color details 3) Color header font 4) Color detail text font. In the report definition we will use expressions for every style configuration. This expression will use a column from the style dataset. To create a dataset with one record we need to use the PIVOT T-SQL syntax. Unfortunately the PIVOT T-SQL syntax is not supported as query text. Therefor a stored procedure with the PIVOT statement will be created. The dataset will use this stored procedure with a styleID parameter.

CREATE PROCEDURE [dbo].[SP_SSRS_ReportStyle]
(@ReportStyleId AS int) AS
(SELECT [TABLE_HEADER_BG],[TABLE_HEADER_TEXT],
                   [TABLE_DETAIL_BG], [TABLE_DETAIL_TEXT]
   FROM
    (SELECT e.[ElementName] , ISNULL(ElementValue,ElementDefaultValue) AS Value
     FROM [SSRS_Element] e WITH (NOLOCK)
     LEFT JOIN [SSRS_ReportStyleElements] rse WITH (NOLOCK)
          ON rse.[ElementId] = e.[ElementId] AND
                 rse.[ReportStyleId] = @ReportStyleId
     ) AS ReportStyleElements
PIVOT (MIN(Value)
FOR ElementName IN
         ([TABLE_HEADER_BG],[TABLE_HEADER_TEXT],
          [TABLE_DETAIL_BG], [TABLE_DETAIL_TEXT])) AS [Elements]
   )

If you want to use more style elements, you need to update the stored procedure to retrieve the added elements.

Now all preparations are done to implement the styles in your reports.

Open your report in BIDS.
Add dataset SSRSStyles to the report using Stored Procedure: SP_SSRS_ReportStyle

Create a parameter: @ReportStyleId with datatype Integer, allow null value, visible. Now the report can use the ReportStyle parameter.
Configure the background color of the header in your report to use the result set of the dataset. Do the same for the color of the font in the header.

Do the same for the detail rows in your report.

The report is ready to use. See the example for ReportStyle1

Example with ReportStyle 2

Enjoy it to implement styles to your SSRS reports.

How to use Power Pivot for Excel on your Exact Globe database.

2011-10-19T12:22:00.000+02:00

As described in my previous blog, easier to build reports on a Exact Globe database with your preferred reporting tools by making use of the Globe reporting views, I described the introduction of the Globe reporting views. In this blog I will describe how you can use these reporting views with Power Pivot for Excel (one of my favorite 10 features of SQL 2008 R2). PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. It’s the user-friendly way to perform data analysis using PivotTable and, PivotChart views, and slicers.

To make it a little bit easier for you I have created an Power Pivot for Excel sheet with some pre configured charts and tables on it. Of course in combinatie with the slicers. Last but not least, some relations between the different views. These relations are very important to analyze and combine different types of data.

This Power Pivot for Excel sheet make use of the Globe Reporting Views (GRV_*). As of Exact Globe product update 402 the first version of the reporting views are shipped. These views will only be installed as of SQL 2005.

In this Excel sheet you will find tabs for:

Revenue Overview (Slicers on: Year, Period,Country, Statecode, Sectorcode, Employee)
Revenue Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
Cost Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
Sales Account Analyze (Slicers on: Year, Period)
Sales Employee Analyze (Slicers on: Year, Period)
Sales Item Analyze (Slicers on: Year, Period)
Sales Assortment Analyze (Slicers on: Year, Period)
Top Suppliers (Slicers on: Year, Period)
Margin Items (Slicers on: Year, Period, Assortment)

Before starting you need to have:

MS Office Excel 2010, preferable 64 bits version.
Globe database installed on SQL 2005 or higher.
Power Pivot for Excel.
Globe Reporting Views installed on your Globe database. In case you can't wait for Globe release 402. Download the script to install the views.
Read access to the Globe Reporting Views
Download and Open the GRV_PowerPivotGlobe excel sheet.

Now it is time to load your Globe data in your sheet. Next steps are described in my blogpost: How to change the database for your Power Pivot sheet.

Change the database connection to your Globe database
Update (Refresh) the the Power Pivot Window.
Update (Refresh) the Excel sheet.
Now you are ready to analyze your Globe administration in Power Pivot for Excel.
Adjust the Charts, Pivots, tables etc to your own preferences.

Enjoy the power of Power Pivot for Excel on your Globe databases.

White paper Analysis Services 2008 R2 Performance Guide

2011-10-12T14:50:00.000+02:00

This month Thomas Kejser and Denny Lee published the white paper: Analysis Services 2008 R2 Performance Guide. This white paper describes how business intelligence developers can apply query and processing performance-tuning techniques to their Microsoft SQL Server 2008 R2 Analysis Services OLAP solutions.

This guide contains information about building and tuning Analysis Services in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 cubes for the best possible performance. It is primarily aimed at business intelligence (BI) developers who are building a new cube from scratch or optimizing an existing cube for better performance.
The goal of this guide is to provide you with the necessary background to understand design tradeoffs and with techniques and design patterns that will help you achieve the best possible performance of even large cubes.
Cube performance can be divided into two types of workload: query performance and processing performance. Because these workloads are very different, this paper is organized into four main sections.
Design Patterns for Scalable Cubes – No amount of query tuning and optimization can beat the benefits of a well-designed data model. This section contains guidance to help you get the design right the first time. In general, good cube design follows Kimball modeling techniques, and if you avoid some typical design mistakes, you are in very good shape.
Tuning Query Performance - Query performance directly impacts the quality of the end-user experience. As such, it is the primary benchmark used to evaluate the success of an online analytical processing (OLAP) implementation. Analysis Services provides a variety of mechanisms to accelerate query performance, including aggregations, caching, and indexed data retrieval. This section also provides guidance on writing efficient Multidimensional Expressions (MDX) calculation scripts.
Tuning Processing Performance - Processing is the operation that refreshes data in an Analysis Services database. The faster the processing performance, the sooner users can access refreshed data. Analysis Services provides a variety of mechanisms that you can use to influence processing performance, including parallelized processing designs, relational tuning, and an economical processing strategy (for example, incremental versus full refresh versus proactive caching).
Special Considerations – Some features of Analysis Services such as distinct count measures and many-to-many dimensions require more careful attention to the cube design than others. At the end of the paper you will find a section that describes the special techniques you should apply when using these features.

Easier to build reports on a Globe database with your preferred reporting tools by making use of the Globe Reporting Views.

2011-10-06T19:47:00.000+02:00

A lot of data is stored by business solutions in one or more databases. Every business solution will ship a lot of overviews to support your business process. However, a lot of users requires some custom made reports, which are not part of the standard solution. As of today a lot of reporting tools are available to build reports on your database. For instance:

SQL Server Reporting Services (SSRS)
Crystal Reports (CRW)
Excel
Power Pivot for Excel

Every reporting tool has it's own advantages and disadvantages. It's up to the report builder to use his favorite reporting tool.

Building reports can be divided into next steps:

Define the goal of your report.
Retreive the data from the data source.
Make calculations with the data you have retrieved.
Format the report according your UX design rules.

The report builder expert will know all in's and out's of the reporting tool. However this expert does not always understand the business logic of the solution. To get a better understanding you need to read and understand the database documentation of the solution. I will explain with one example: How can I get all outstanding invoices of customer X. To answer this question you need to know in which tables this information is stored but also how it is stored. For Exact Globe next tables are used: GBKMUT, BankTransactions, CICMPY. The outstanding items query will contain 140 lines of code. So being a report builder expert you can still not build the requested reports if you do not understand the business logic.

To solve the challenge of the business logic we have developed database views which contains the business logic. The view will present the data in an understandable format which can be used in the report.

The advantages of using views:

Business logic is in the database, not in the report. If business logic changes, the view will be updated with the new logic. No changes are needed in the report.
Data is presented in an understandable way. For instance 'Supplier code' instead of a databasse column name: 'cicmpy.crdcode'
Data sets in the reports are easier to read and maintain.
Performance can be better guaranteed.

As of Exact Globe product update 402 the first version of the reporting views are shipped. The Globe Reporting Views start with 'GRV_' in their name. In my next blog post I will describe how you can use these views with Power Pivot for Excel.

Empower your BI using Powerpivot with the datasets of the Windows Azure Marketplace.

2011-09-20T11:12:00.001+02:00

To empower you BI solutions, you want to merge data from different data providers. In most companies data is merged from databases within the company. As of today a lot more information is available which can be useful to empower your BI solution. I will explain this with an example.
Assume your company is making ice creams. The sales revenue of ice cream depends on the weather. The higher the tempature, the more ice creams will be consumed. So your revenue of ice creams should be compared with the tempature. By comparing your revenue with the tempature you can see if the increase in revenue is caused by a good job of your sales people or by extreem hot temparture of the last period. How do you know what the tempature has been in the last year? This kind of data will come available in the Windows Azure Market Place for premium data. It will enable information workers to integrate premium datasets available on DataMarket with their on-premise business data to perform analytics.

How to use premium Windows Azure DataMarket data?
In this example I will make use of a dataset which is for free: Datestream. Datestream is a date table feed designed for import into an Excel PowerPivot model. The table contains columns particularly suitable for time business intelligence applications.

Sign on to the Windows Azure market Place.
Select the Datestream dataset.
Select the hyperlink: Explorer this dataset.
Specify YearKey 2010. In my example I will analyze only the year 2010. Press on the Run Query button.
To generate a DataFeed. Select Export and press on the Export to Excel Powerpivot Download button.
Specify a name for your data feed and save it to a location on your PC.
Open Excel PowerPivot and import your on premise data you want to analyze. For instance your ice cream sales per day.
Press the From Azure DataMarket button to import the Datestream datafeed.
Specify the Azure DataMarket dataset URL. Browse to the datafeed file you made in step 6.
Specify your Account key. This can be found in the Windows Azure market place under the tab My account. Select account keys.
Refresh your datasets in the PowerPivot window. You will get 365 rows to import the date details for the year 2010.
Create a relationship between the date from the DateStream and your ice scream sales dates. This can be done in the design tab of the PowerPivot window.
Now you can start analyzing your data. For instance, which day of the week do you sell the most ice creams.

Enjoy it to use the Azure DataMarket to empower your BI experience.

Running totals per page in SSRS to display cumulative totals.

2011-09-15T11:50:00.000+02:00

In Crystal Reports, you can easily make use of running totals on every page of your report. SQL Server Reporting Service does not have a standard functionality for this. Especially for companies who want to migrate from CRW to SSRS, running totals can be important to use. This blogpost will explain how you can implement running totals in your report.

Add a tablix to your report and add a dataset to it. In my example I have 3 columns:
Debtor code, Account Name and Outstanding amount.
Add a 4th column to the tablix. Set the visibility of this column to Hide.
Select in the 4th column the detail row. Select Text Box Properties and change the name of the textbox to 'RunningTotal'.
Select Value and press the expression button. In this expression we will define the running total of the column. In my example, I have a columns Outstanding from the dataset 'CreditLimitDetails'

=RunningValue(Fields!Outstanding.Value,SUM,"CreditLimitDetails")
Now we have a running value of the outstanding amount in the 4th column.
To display the running value on every page, we will use the page footer.
Add a page footer to your report. In BIDS you can select, Report, Add Page footer.
In this example I will add a page total and a running total to the report.
Insert a 2 text boxes to the page footer.
Open the expression definition of the first text box. In this text box we will define the totaal outstanding amount of the current page.
="Page Total: " + cstr(Sum(ReportItems!Outstanding.Value))
Open the expression definition of the second text box. In this text box we will define the Running total of the outstanding column of the current page and all previous pages.
="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))

Now your report will print running totals at the bottom of every page.

Localization of your SSRS reports. Translations of labels in your reports.

2011-09-06T14:41:00.002+02:00

Localization is not a build in feature in SQL Server Reporting Service (SSRS). The user experience is very important for the success of your reports. In a lot of organizations you will see people who are using their applications in different languages. A user can install\configure his windows operating system and MS Office version in the langauage he or she wants. When using SSRS reports a user can't select the language of his reports. The reports will be generated in the language they are made. This is not a good user experience. This blog will explain what you can do to support multiple languages in your SSRS reports.

1) We need a table which will hold all translations.
CREATE TABLE [dbo].[Translations](
     [ID] [int] NOT NULL,
     [Language] [nvarchar] (10) NOT NULL,
     [Description] [nvarchar] (255) NULL,
     CONSTRAINT [PK_Translations] PRIMARY KEY CLUSTERED
     ([ID] ASC,[Language] ASC)
     ) ON [PRIMARY]

2) We need to add translations to this tabe which are needed in the reports.
INSERT INTO Translations (ID,Language,Description) VALUES (1000000,'en-US','Management')
INSERT INTO Translations (ID,Language,Description) VALUES (1000000,'nl-NL','Management')
INSERT INTO Translations (ID,Language,Description) VALUES (1000001,'en-US','Dashboard')
INSERT INTO Translations (ID,Language,Description) VALUES (1000001,'nl-NL','Dashboard')
INSERT INTO Translations (ID,Language,Description) VALUES (1000002,'en-US','Revenue')
INSERT INTO Translations (ID,Language,Description) VALUES (1000002,'nl-NL','Opbrengst')
INSERT INTO Translations (ID,Language,Description) VALUES (1000003,'en-US','Cost')
INSERT INTO Translations (ID,Language,Description) VALUES (1000003,'nl-NL','Kosten')
INSERT INTO Translations (ID,Language,Description) VALUES (1000004,'en-US','Result')
INSERT INTO Translations (ID,Language,Description) VALUES (1000004,'nl-NL','Resultaat')
INSERT INTO Translations (ID,Language,Description) VALUES (1000005,'en-US','Cash')
INSERT INTO Translations (ID,Language,Description) VALUES (1000005,'nl-NL','Liquiditeit')
INSERT INTO Translations (ID,Language,Description) VALUES (1000006,'en-US','Risk')
INSERT INTO Translations (ID,Language,Description) VALUES (1000006,'nl-NL','Risico')
INSERT INTO Translations (ID,Language,Description) VALUES (1000007,'en-US','Figures are expressed in ')
INSERT INTO Translations (ID,Language,Description) VALUES (1000007,'nl-NL','Cijfers worden weergegeven in ')
INSERT INTO Translations (ID,Language,Description) VALUES (1000008,'en-US','up to')
INSERT INTO Translations (ID,Language,Description) VALUES (1000008,'nl-NL','t/m')
3) Open a new SSRS report in Business Intelligence Development Studio. (BIDS)
4) Create a dataset which will return the set of labels for a given language. Add dataset named 'Labels' using next query:
SELECT ID, Language, Description
FROM Translations
WHERE Language = @Language

5) After adding the dataset 'Labels' a report parameter named 'Language' is added to the report. Configure the default value of the parameter 'Language' to the language ID of the client running the report.

The language ID of the client is configured by the user in Internet Explorer, Internet Options, Tab General, Button Language

6) For testing purposes it is easier to make the parameter 'Language' visible.

7) Create a second dataset with all available languages. This dataset will be used as available values for the parameter. You can use next query:
SELECT DISTINCT(Language) FROM Translations

8) Create a hidden, multi-valued parameter called 'Labels'.
Set the available values to the Labels dataset, value field: ID, label field: Description

Set the Default values to the Labels dataset, value field: ID This is important as you don't have access to tha available values from within the report. When the report is started by the user, the 'Labels' parameter will contain all translation for the selected language.

9) Add a function to the report. This function will find the translation for the selected language based on the specified ID in the report. To add a function to a report, right click on the yellow part of the report. Choose Report Properties, Code

Use next code:

Public Function GetLabel(P as Parameter, Label as String) as String
Dim i As Integer
For i = 0 to Ubound(P.Value)
If (P.Value(i) = Label) Then Return P.Label(i)
Next i
Return Label
End Function

10) Now we are ready to configure the labels in your report to use this function.
As example for translation of the label: 'Revenue' use next expresion in your label:
=Code.GetLabel(Parameters!Labels,1000002)

11) Next step is to configure the report to use the date and number format to the selected language.
Open the report properties window and configure language with the parameter value of the selected language.
=Parameters!Language.Value

12) Now execute your report. The report will be generated in the configured language of your Internet Explorer.

13) Select another language if you want, in this case nl_NL\

You will see that all labels are translated and the thousand seperator is changed from , to .

Enjoy it to make your reports in the localization of your users to improve the user experience.

Another Project Crescent movie which show the new features.

2011-08-31T20:48:00.000+02:00

Last month I blogged about a perfect movie about Project Crescent. Movies are a great way to show what your product can do. I'm very enthousiastic about Project Crescent and found a new movie which will show more Crescent functionality. This movie will talk about:

Crescent design environment
Crescent visualizations overview
Converting a table to a matrix
Converting a table to a card
Why icons are disabled sometimes
Charts with series
Bubble charts
Filtering vs highlighting
Slicers versus filters
Tiles

Enjoy it.

Thanks to all 5000 customers who have used the Exact System Information tool

2011-08-23T12:22:00.000+02:00

In februari 2010 we introduced the Exact System Information (ESI) tool, which can generate an Improvement Report for you Exact solution. Last week we reached a new milestone.

5000 customers, from all over the world,
have used the ESI tool.

2800 Improvement reports have been generated.

I want to thank all customers for using the ESI tool. Beside the fact that we will help you to optimize your Exact solution, it will help Exact to better understand how our customers are using the Exact solution.

Additional information about the Exact System Information tool:

How to delete cached data in SQL Server Reporting Services for Visual Studio in Preview Mode?

2011-08-04T20:42:00.000+02:00

In Business Intelligence Development Studio (BIDS) you can build your Reporting Service Reports. The preview mode of the report make use of cached data. The following objects are cached: result sets of your datasets, parameter value and credentials. Sometimes this feature can be annoying, if changes in the preview mode should be visible immediately. The cached data is stored in the report RDL directory with the extension *.Data. Assume you have a report called Dashboard.RDL, in the same directory you will find a file called: Dashboard.RDL.Data

Delete this file and rerun your report. The report is now generated without cached data. After completion of the report a new Dashboard.RDL.Data is created.

How to combine a bar chart with a line graph and running totals in SSRS.

2011-08-03T22:21:00.000+02:00

In of my previous blogs a wrote about dashboards design rules and the do's and don'ts. Easy readable charts are essential in the success of your reports. When building reports you have a lot of different chart types you can choose for your reports. By default you can use a bar chart of a line chart. When comparing figures for instance current year with prevoius year of current year with the budget. You can use a standard bar chart like this one:

However the graph is a little bit busy to read. Using the combination of a bar for the current year and line graph for the comparing previous year will make the chart easier to read. Like this:

How to do this:

Open the report in Business Intelligent Development Studio (BIDS).
Double click on the bars in the chart.

Select the Bar of previous year in the Chart data window. In my example RevenueYTD_1.
Change chart type
Select the line type you want. That's all you need to do.

How to create running totals?

Double click on the bar in the chart.
Select the Bar of current year in the Chart data window.
Series properties.
Press on the expression button for the value field.
Use next expression: =Runningvalue(Sum(Fields!Previous_Year.Value),SUM,"RevenueYTD_YTDPY")
RevenueYTD_YTDPY is the name of my dataset. The column name used is 'Previous_Year'
Do the same for the line graph.
That's all you have to do.

Enjoy it to build nice and easy readable reports.

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

2011-07-31T21:27:00.000+02:00

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: .

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.
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.
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.
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.

2011-07-20T09:41:00.000+02:00

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.

2011-07-01T08:10:00.008+02:00

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:

Time to retrieve the data (TimeDataRetrieval).
Time to process the report (TimeProcessing)
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:

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.

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.

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.

The value provided for the report parameter 'LastUploadDate' is not valid for its type

2011-06-27T09:08:00.000+02:00

I have a data driven subscription set up that passes a date value from a query to the report for processing. After processing an email is sent. However I did not receive the email. When the report runs the following error occurs in the report server log.

Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterTypeMismatchException: , Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterTypeMismatchException: The value provided for the report parameter 'LastUploadDate' is not valid for its type.;

The report server log can be found the installation directory of SQL Reporting Server. In my case:
C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

The field in the database is of type Date, as is the report parameter definition which is DateTime. If I pass the date parameter as '2010-06-23' it works fine. On my previous reporting server this subscription work without any problem. I do not understand why it does not work anymore. It is a SQL2008 R2 Reporting Server which connects to a SQL 2008 server. After one day struggling with it I implemented next work around.
The parameter is called: LastUploadDate
The database field is called: Statdate

My previous query for the dat driven subscription:
SELECT Statdate, CustomerID
FROM Mytable

My workaround:
SELECT Cast(YEAR(Statdate) as char(4)) + '-' + CAST(MONTH(Statdate) as CHAR(2)) + '-' + CAST(DAY(Statdate) AS CHAR(2)) AS CASTDATE,

CustomerID
FROM Mytable

So I made a hard coded string of the date I needed. It is indeed not so nice, but it works.
If you have had the same experience? Please let me know how you have fixed it.

Troubleshooting and optimizing queries on SQL Azure.

2011-06-21T23:03:00.000+02:00

In one of my previous blogs I wrote about some usefull DMV's to analyze SQL Azure performance. SQL Azure is a cloud based relational database with SQL Server 2008 engine at its core. In the first release of SQL Azure most useful DMVs have been disabled. As part of the scheduled Service Updates (SUs) to SQL Azure, these DMVs are enabled in phases. Since SQL Azure is a shared infrastructure model, the DMVs have to be modified to filter the output and show information only as appropriate. In this effort, the following DMVs have been enabled in the first phase. These DMVs being released typically require VIEW SERVER STATE permissions in an on-premise SQL Server. The new permission level required on SQL Azure would be VIEW DATABASE STATE to query these DMVs.

Transaction related DMVs

sys.dm_tran_active_transactions - returns information about transactions for the SQL Azure server
sys.dm_tran_database_transactions - returns information about transactions at the user database level
sys.dm_tran_locks - returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.
sys.dm_tran_session_transactions - returns correlation information for associated transactions and sessions.

Execution related DMVs

sys.dm_exec_connections - returns information about the connections established to SQL Azure and the details of each connection.
sys.dm_exec_query_plan - returns the showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
sys.dm_exec_query_stats - returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
sys.dm_exec_requests - returns information about each request that is executing within SQL Azure.
sys.dm_exec_sessions - returns one row per authenticated session on SQL Azure.
sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.
sys.dm_exec_text_query_plan - returns the showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing. This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences: 1) The output of the query plan is returned in text format. 2) The output of the query plan is not limited in size.

Database related DMVs

sys.dm_db_partition_stats - returns page and row-count information for every partition in the current database.

As you can see, the number of DMVs is growing but unfortunaltely still no SQL Azure Profiler available. At this moment I got 204 votes for my idea for a SQL Azure Profiler on mygreatwindowsazureidea.com. So let's hope that a SQL Azure Profiler will come available in one of the coming Service Updates (SUs).

Enjoy it!

Whitepaper: Analysis Services Operatings Guide (SSAS)

2011-06-17T09:00:00.000+02:00

Microsoft has published the whitepaper: Analysis Services Operatings Guide. In this guide you will find information on how to test and run Microsoft SQL Server Analysis Services in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 in a production environment. The focus of this guide is how you can test, monitor, diagnose, and remove production issues on even the largest scaled cubes. This paper also provides guidance on how to configure the server for best possible performance. It is the goal of this guide to make your operations processes as painless as possible, and to have you run with the best possible performance without any additional development effort to your deployed cubes. In this guide, you will learn how to get the best out of your existing data model by making changes transparent to the data model and by making configuration changes that improve the user experience of the cube.However, no amount of operational readiness can cure a poorly designed cube. Although this guide shows you where you can make changes transparent to end users, it is important to be aware that there are cases where design change is the only viable path to good performance and reliability. Cubes do not take away the ubiquitous need for informed data modeling. Fortunately, this operations guide has a companion volume targeted at developers: the Analysis Services Performance Guide. We highly recommend that your developers read that white paper and follow the guidance in it.

Enjoy reading the whitepaper Analysis Services Operatings Guide. To directly download the whitepaper from the Microsoft Download Center click here.