Month: April 2017

Async Auto Create Statistics Resolves Timeout

The following is based on an actual customer support case:

Customer facing web application executes a specific stored procedure that is set to timeout after 5 seconds. Most of the time the duration of this stored procedure is sub-second, but when the server load is high, this stored procedure will timeout roughly 5% of the time.

Top wait = latch non-buffer

Analyzed extended events capture of query executions filtering on sessions where stored procedure duration > 5 sec and found 4.9 seconds of the duration was spent updating qpstats on a > 100 million row table.

Enabled auto update stats async to resolve the issue:


Statistics updates are synchronous by default which means queries always compile and execute with the latest statistics.  When the threshold has been reached and statistics are stale, the query optimizer will wait for the statistics to be updated before compiling the query plan.  If there are many rows in the table, the wait can be significant. When statistics updates are asynchronous, the query optimizer doesn’t wait but goes ahead and uses the existing query plan (or compiles a new one) using the existing statistics. Now, it is possible that the query optimizer could choose a suboptimal query plan if statistics are out-of-date when the query compiles. Queries that compile after the asynchronous updates have completed will benefit from using the updated statistics.

You should stick with the default statistics updates when you perform operations that change the distribution of data, such as truncating a table or performing a bulk update of a large percentage of the rows. If you do not update the statistics after completing the operation, using synchronous statistics will ensure statistics are up-to-date before executing queries on the changed data.

You should use asynchronous update statistics to achieve more predictable query response times for the following scenarios:

  • Your application frequently executes the same query, similar queries, or similar cached query plans. Your query response times might be more predictable with asynchronous statistics updates than with synchronous statistics updates because the query optimizer can execute incoming queries without waiting for up-to-date statistics. This avoids delaying some queries and not others.
  • Your application has experienced client request time outs caused by one or more queries waiting for updated statistics. In some cases, waiting for synchronous statistics could cause applications with aggressive time outs to fail.



Manual Setup of an AlwaysOn Availability Group on SQL Azure Virtual Machines

Provision two SQL Server VMs.   Be sure to put the VMs on the existing domain’s VNET.  If you don’t have a domain, provision two domain controllers and create one.  Create a domain account to use that has enterprise and domain admin rights.

REMEMBER – the Azure uptime SLA is only applicable if the VMs are in an availability set.  This will prevent updates from occurring on both replicas at the same time and ensure they are run on separate hardware to prevent an outage in case of hardware failure. Also, it is a requirement for them to be in an Availability Set to add them to the Backend Pool of a Load Balancer which is required for an Availability Group Listener.

Join each of the VMs to the domain. This requires a restart – use your domain account to log in after they start back up:


Add the failover clustering feature to each VM:

Create the cluster with only one of the nodes.  UNCHECK THE BOX to add all available storage to the cluster.  Run all the validation tests:

Create a file share on another machine in the network.  The domain controller is a good choice:

Configure the file share witness:

Change the cluster IP address to a free, static ip within the subnet:

Add the other node to the cluster,  UNCHECK THE BOX to add all available storage to the cluster and run all the validation tests:


From SQL Configuration Manager, enable AlwaysOn Availability Groups on each node:


Change the SQL Server service account to your domain account.

Connect to SQL Server using the local account and add your domain account as a login and add it to the Sysadmin role.

Create a share for the database backups on the primary node.

Create a rule in Windows Firewall to open port 5022 on both nodes:


Use the wizard to create your availability group but don’t create the listener at this time.

Create an internal load balancer from the azure portal that is in the same resource group as your VMs.

  1. Static frontend ip
  2. Create health probe:  TCP Port: 59999
  3. Create load balancing rule: TCP Port: 1433 Backend port: 1433 Health probe: the one created in step 2.  Floating IP: enable.  Leave everything else at the default.
  4. Create backend pools.  Associate with the availability set of the VMs and the load balancing rule created in step 3.

Create the AlwaysOn Listener using the same ip address as the internal load balancer.

Test connectivity to the listener.  Failover and test again.

Congratulations, you now have an AOAG in Azure.