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. 

Troubleshooting and optimizing queries on SQL Azure.

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!

Usefull DMV's for SQL Azure to analyze if you miss SQL Profiler.

I have started a research project to look to the current possibilities of SQL Azure Reporting Services. One of the key areas I will focus on is the performance of SQL Azure Reporting services. To use SQL Azure Reporting Services you need to create a SQL Azure database and upload content to it. This can be done by making use of the Import and Export wizard.  After that I created my first SQL Azure Reporting Server report in SQL Server Business Intelligence Development Studio (BIDS) and deployed it to my SQL Azure Reporting server. To run the report, it took a 5 seconds to show the results. I was a little bit suprised why this report took 5 seconds to generate. Normally I use the SQL Profiler to analyze this performance issue. However, SQL Profiler is not available for SQL Azure. It is still a feature request on  mygreatWindowsAzureidea.com
with already 96 supporters for this idea.

Up till now, we need to do it with the available DMV's. More and more DMV's will come available in future releases. In this blogpost I will share some usefull queries on these DMV's which you can use to analyze the performance of your application on a SQL Azure database. In my situation the Azure Reporting Server.

Of course these queries can't replace the powerfull features of SQL Profiler, but at least it will help you and it is better than nothing.

--1)  Last executed queries with used query plan.

SELECT TOP 5 query_plan,q2.[text],
  (total_logical_reads/execution_count) AS avg_logical_reads,
  (total_logical_writes/execution_count) AS avg_logical_writes,
  (total_physical_reads/execution_count) AS avg_phys_reads,
  execution_count,
  (total_elapsed_time/execution_count) AS avg_Duration,
  last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2
ORDER BY last_execution_time DESC

In the column Query_Plan you can click on the hyperlink.

This will show the query plan which was used during Query execution.

-- 2) Running queries.
SELECT q2.[text],database_id, user_id,session_id,
  transaction_id,status,start_time
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2

-- 3) Blocking queries
SELECT q2.[text],session_id, blocking_Session_id,database_id, user_id,transaction_id
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2
WHERE blocking_session_id <> 0 AND Blocking_Session_ID <> Session_ID

-- 4) Queries generating the most IO in SQL Azure.
SELECT TOP 5 query_plan,q2.[text],
  (total_logical_reads/execution_count) AS avg_logical_reads,
  (total_logical_writes/execution_count) AS avg_logical_writes,
  (total_physical_reads/execution_count) AS avg_phys_reads, execution_count,
  (total_elapsed_time/execution_count) AS avg_Duration,
last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2
ORDER BY total_logical_reads DESC

-- 5) Enable Client Statistics in SSMS
By enabling Client Statistics in SSMS you can see the number of Bytes received from the server, the total execution time, Wait time on server replies.





Enjoy, using these DMV's till a good replacement is available for SQL Profiler on a SQL Azure database.

Tweet

Msg 567, Level 16, State 7, Line 1 File 'D:\TraceFiles\vrijdag8161700.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file. fn_trace_gettable

If you have a SQL Profiler trace file which you want to open in the query analyzer you can make use of the fn_trace_gettable function.  Assume you are running this script from a server named: ServerA and you have a SQL Server named: ServerSQL. On ServerA you have stored a trace file in the folder D:\TraceFiles.

On ServerA you executed next command to open the trace file in the SQL Server Management Studio.

SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData
FROM :: fn_trace_gettable('D:\TraceFiles\vrijdag8161700.trc',default )

This will return:
Msg 567, Level 16, State 7, Line 1


File 'D:\TraceFiles\vrijdag8161700.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

The error is caused by the fact that the function fn_trace_gettable is executed on the SQL Server named: ServerSQL. This server has no acces to the file in 'D:\TraceFiles\vrijdag8161700.trc' on ServerA.

To solve this share the folder D:\Tracefiles for the user who need access to it.
SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData
FROM :: fn_trace_gettable('\\ServerA\TraceFiles\vrijdag8161700.trc',default )

Now the ServerSQL has access to the file on ServerA.
Enjoy using fn_trace_gettable in your SSMS.

: Tweet

Part 1: Analyze SQL Profile traces with a SSRS dashboard.

This blogpost is the first of a serie, in which I will explain how you can make a dashboard in SQL Reporting Services to analyze your SQL Server Profiler trace files. SQL Profiler is a perfect tool you need to use to analyze the performance of your application. When you have a SQL Profiler trace file the challenge begins to find that part of your application where you can make significant improvements. Improvements can be made in different ways, you can focus on:

• CPU
• Reads
• Writes
• Number of queries
• Repeating queries.
• Applications
• No SQL time. (Time between end time of query X and start time of query X+1)

The big question to answer, what is the best to focus on. This can all be done with the SQL profiler itself however it takes a lot of manual work. With SQL Server Reporting Services I made a dashboard which can be used to analyze a SQL Profile trace file. The dashboard will help you to visualize the bottleneck and to some in to this bottleneck to get more details. This dashboard contains 13 SSRS reports in total.

Click on the picture to enlarge.

In this dashboard, you will get statistics for the selected time frame about:

Total number of queries
Total trace time
Total Duration (SQL time)

Maximum number of Reads,Writes, CPU, Time SQL
Average number of Reads,Writes, CPU, Time SQL
Sum of total Reads,Writes, CPU, Time SQL
Query TOP 5 Reads,Writes, CPU, Time SQL
Repeating Query TOP 25 Reads,Writes, CPU, Time SQL
TOP 25 Reads,Writes, CPU, Time SQL by application

You can use the filters in the report to analyze a specific time frame or to zoom in to a specific bottleneck. In future posts I will explain how to make this dashboard. Please let me know if this can be interesting for you to use.

Combine SQL Profiler with Performance monitor logs

To analyze the performance of your applications which is running on SQL Server you need to make use of 2 standard tools:

1. SQL Profiler
2. Windows Performance Monitor. (Perfmon)

Both tools are useful to understand what happens at which moment. In SQL Profiler you load a profile trace however you can also load the performance monitor log file in the same loaded SQL Profiler trace file. See next example:

What do you need to do:

1. Make a SQL Profile trace of your application.
2. Make a performance log file with Performance monitor (Perfmon) of your application via a data collector set. Save the result to a file.
3. Execute some load in your application.
4. Stop the SQL profiler trace file and store it as a trace file.
5. Load the trace file in SQL Profiler.
6. In the menu of SQL Profiler Select File, Import Performance Data and select your performance log file.
7. Select the counters you want to see in the SQL profiler.

Now you have both trace files combined in one application. When you scroll through the profile entries you will see the red vertical bar moving. A perfect way to analyze the performance of your application.

Enjoy using the performance monitor with SQL Profiler.

SQL Profiler is on my second screen, but my second screen is not connected.

I use a lot of time multiple screens on my laptop. With SQL Profiler this can give some challenges. I will explain in which situation.

• Connect an extra monitor to your laptop.
• Extend this screen as the second screen.
• Start SQL Profiler and move it to the second screen.
• Close SQL profiler on the second screen.
• Disconnect the second screen.
• Start SQL Profiler.

In the taskbar you can see that SQL profiler is active however you do not see SQL Profiler. It is still open on your not connected screen. This is a bug in SQL Profiler which does not reset the window position. What should you do to make use of the SQL Profiler if you do not have a second screen available.

• Click on the SQL Profiler window in the taskbar.
• Press ALT ENTER to get the propertie window of the SQL Profiler.
• Select MOVE.
• Use you arrow keys to move the invisible window back to your screen.

Enjoy the use of SQL Profiler.