Is my query running fine in the cloud?
This is the fifth installment in a blog series. The previous entry is located here
We ended the last blog post with some tips on basic administration that you need to do in WASD. Now let us get more specific with regards to query tuning and what tool are available to help you in this area.
Although WASD is a smaller feature set from the on premise SQL Server, it has a set of DMVs that allow you to get insight into your workload and do some basic troubleshooting of issues that you see with query performance. Here is listing of some of the common ones that I use frequently.
Query Performance related DMVs
The challenge here is figuring out whether your queries are running or waiting. If they are waiting, then is there a common wait_type that we can go tune.
These two DMVs combined can give you a lot of useful information.
The typical columns to check for are as follows
Blocking_session_id:- If this shows a non-zero number then you have blocking going on
Wait_type , wait_time,wait_resource :- These columns give you an idea of what is the dominant wait types. Use this to look for multiple sessions waiting on similar waits.
Total_elapsed_time, cpu_time:- these correspond to total time spent by the query vs. time spent on CPU.A big difference between these two typically indicates we are waiting.
Sys.dm_db_wait_stats :- Use this DMV primarily to understand what your dominant wait type is
wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
> 0 OR
> 0 OR
For example here is the output from my database after running a blocking scenario. As expected, locks (i.e. LCK_*) show up as the top wait type
For ad-hoc query or stored procedures that are executing slower than expected, but do not show you a dominant wait type, you can get the execution plan from Management Studio by hitting Ctrl+M ( Include Actual Execution Plan)
You can then look for any issues in the execution plan. Here are a few common ones that we see all the time
Missing Indexes will typically be shown at the top of the execution plan as follows
Implicit Conversions can be found in the operators in the execution plan e.g.
Connectivity related DMVs
Sys.event_log, sys.database_connection_stats:- Use these DMVs to understand connectivity and throttling errors & deadlocks against your user database
Here is a screenshot of how it appears in the Management Portal (under SQL Database àDashboard)
sys.database_connection_stats provides you a high level view of successful vs failed connections and further breaks down the failed connections as failed /terminated/throttled. More information about this DMV is outlined here
Sys.event_log gives you detailed reason for connection failures.
Additionally it also displays any deadlocks. More information about this DMV is outlined here
For deadlocks you can click on the additional_data column, save the output as an xdl file & get to the graphical deadlock graph as follows
Resource Usage related DMVs
Sys.resource_stats,sys.resource_usage :- Use it to baseline your database performance metrics.
For all practical purposes, the output from sys.resource_stats can be used to baseline your database performance. More information about this DMV is outlined here
Here is typical output from it for a database where I kept adding rows of data into a table
Putting it all together
In the on premise world we have long had a collection of scripts (sometimes called the PerfStats scripts) to collect DMV information from SQL Server. We have updated those scripts for the cloud and added the cloud-specific DMVs. The scripts are available for your download here. Additionally, when you open a support case for certain support topic areas the latest version of the scripts will be sent as part of an automated data collection (see KB article 2843748). Running the automated diagnostic and uploading the results can assist support with resolving your performance or connectivity problem more quickly.
- System Views (Windows Azure SQL Database) http://msdn.microsoft.com/en-us/library/windowsazure/ee336238.aspx#sqlazure
Author: - Rohit Nayak (@sqlrohit)
Reviewers: - Keith Elmore, José Batista-Neto
Escalation Services, Microsoft