Analyze performance between SQL Azure and SQL Server on premise.

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:

1. The on premise database
2. 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. 

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.