While running SQL Server in Azure Virtual Machines, it is recommended that you continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environment. However, the performance of a relational database in a public cloud depends on many factors such as the size of a virtual machine, and the configuration of the data disks.

This post is focused on getting the best performance for SQL Server on Azure VMs. If your workload is less demanding, you might not require every optimization listed below. Consider your performance needs and workload patterns as you evaluate these recommendations.

The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:

Area Optimizations
VM size DS3 or higher for SQL Enterprise edition.

DS2 or higher for SQL Standard and Web editions.

Storage Use Premium Storage. Standard storage is only recommended for dev/test.

Keep the storage account and SQL Server VM in the same region.

Disable Azure geo-redundant storage (geo-replication) on the storage account.

Disks Use a minimum of 2 P30 disks (1 for log files; 1 for data files and TempDB).

Avoid using operating system or temporary disks for database storage or logging.

Enable read caching on the disk(s) hosting the data files and TempDB.

Do not enable caching on disk(s) hosting the log file.

Important: Stop the SQL Server service when changing the cache settings for an Azure VM disk.

Stripe multiple Azure data disks to get increased IO throughput.

Format with documented allocation sizes.

I/O Enable database page compression.

Enable instant file initialization for data files.

Limit or disable autogrow on the database.

Disable autoshrink on the database.

Move all databases to data disks, including system databases.

Move SQL Server error log and trace file directories to data disks.

Setup default backup and database file locations.

Enable locked pages.

Apply SQL Server performance fixes.

Feature specific Back up directly to blob storage.

For more information on how and why to make these optimizations, please review the details and guidance provided in this article.

2016-logo-200x135It’s been quite some time since I had the chance to speak publicly and I had really missed it so when I was given the opportunity to talk at this year’s Global Azure Bootcamp, I really jumped on it. So if you’d like to learn a few things on building Always-On, Hyper-Scalable Microservice-based Cloud Services then join me unfortunately the event is sold out, so if you didn’t have the opportunity to register, don’t worry, from what I hear, the event is going to be live streamed for those that won’t be able to make it.

devitBut there’s another chance for you to catch me speaking. Later on this May I’m going to be presenting about how to enable professional search features for your websites at another great event at Thessaloniki – devitconf. I’m very excited and happy to be part of this great line up of speakers. If you haven’t register for it, there’s still some time before seats run out.

 

InsightsAnalyticsWhen running many services at large scale, you rely very heavily on telemetry to help you understand what’s happening across your services and to detect, diagnose and repair problems. Application Insights Analytics, is a near realtime log analytics platform for interactive data exploration that enables you to do amazing things. While Application Insights has had a log search capability for a good while now, Analytics takes it to a whole new level.

The Application Insights Analytics preview ingests any data the Application Insights SDK sends – built in or custom and allows you to query over it easily from a browser.

Here’s a very simple query. “requests” is the name of the table we are querying data from. “timestamp” is a field name that identifies when a datapoint was collected. “summarize” is a little like a SQL group by expression. “order by” says to sort the data by count, descending. Each of these clauses are strung together in sequence by a pipe operator “|”. “count_” is the auto generated field name for the count() of requests for each CountryOrRegion.

InsightsAnalytics2

The result will look something like that

InsightsAnalytics3

That’s just a tiny taste of the kinds of things you can do with Application Analytics.  It’s very powerful.  You can read more about it in theApplication Insights Analytics docs.  You can also run queries over the log data and export the results to PowerBI and use the full reporting power of Power BI to create great visualizations.  For the moment, there’s an 8 days retention.  Over time, more retention options will be made available – but 8 days is generally plenty for trouble shooting of live site incidents.

 


Azure troubleshoot

As a developer you’re probably familiar with the pain of troubleshooting web applications. In Azure App Services you have a rich set of different tools to help you make this troubleshooting a lot easier. In this post though we are going to discuss the latest addition of these tools, the Azure App Service support.

To access the tool you can click on any of the troubleshoot blade links as shown in the picture. The Azure App Service support web application will open on a new web page and present you with three troubleshooting sections. Note that, if your live id is associated with more than one subscriptions (Active directories) you will first have to select the subscription you want to work with and the web app you want to troubleshoot from the drop down menu on the header.

Observe:

In this first section you will be able to monitor – see the average requests per second and average server errors per second your web app is serving in real time.

Azure app Service support

Analyze:

This is probably the most useful section of the application as through it you will be able to

  • Configure your diagnostics settings for your web app and download web diagnostics (note that you will have to configure your app to produce these) like:
    • Event viewer logs
    • Memory dumps
    • Http Logs
    • PHP Error Logs
    • PHP process Report
    • Node Process Report

Azure app Service support 2

  • Take a pick on the Event Viewer and identify, using rich filtering capabilities, possible issues that might be causing problems in your web app.

Azure app Service support 3

  • See various metrics and statistics like CPU and memory utilization, http and networking statistics for your web app as well as restart the w3wp process hosting your web application and see other sites that might be running on the same server in the case of using the shared hosting plan.

Azure app Service support 4

  • See application emitted FREB logs

Mitigate:

Using this section you can specify rules for mitigating possible problems in your web app.
So you can mitigate for:

  • Max Requests: Consider a scenario where you have a need to recycle your application automatically after it has served X number of requests in Y amount of time. You know that it just doesn’t scale well after huge influx of requests in short amount of time. You want to detect this condition and recycle worker process automatically or log an event or Run a custom action.
  • Status Code: Consider a scenario where you would like to get notified of a situation when your web Web App starts throwing specific HTTP status codes, sub-status code or win32 status codes. You can chose to recycle or simply log an event or Run a custom action.
  • Slow Requests: Consider a scenario where the performance of your application starts degrading and several pages start taking longer time to render. You would like to detect this situation and recycle worker process automatically or log an event or Run a custom action.
  • Memory Private Set: Consider a scenario where you have a need to recycle your application when your worker process hits specified memory limit (private bytes in Kb). You can chose to recycle or simply log an event or Run a custom action.

Hope you enjoy this as much as I do.

The Microsoft Azure Storage Data Movement Library in an open source library designed for high-performance uploading, downloading and copying Azure Storage Blob and File. This library is based on the core data movement framework that powers AzCopy.

The library can help you take full advantage of cloud storage with a number of advance features like:

  • Blobs

    • Download/Upload/Copy Blobs.

    • Synchronous and asynchronous copy Blobs
    • Concurrently transfer Blobs and Blob chunks, define number of concurrent operations
    • Download Specific Blob Snapshot
  • Files

    • Download/Upload/Copy Files.

    • Synchronous and asynchronous copy Files
    • Concurrently transfer Files and File ranges, define number of concurrent operations
  • General

    • Track data transfer progress

    • Recover the data transfer
    • Set Access Condition
    • Set User Agent Suffix
    • Directory/recursive transfer

With these new features, you can perform data movement at the Blob container and Blob virtual directory level, or the File share and File directory level.

The library is powered by a very ative community at Github, providing code samples, help and of course any community contributions to these code samples are highly appreciated.

You can install the Azure Storage Data Movement Library from Nuget or download the source code from Github. For more details, please read the Getting Started documentation.

Managing and tuning the performance of relational databases is a challenging task that requires significant expertise and time investment but now we have another tool in our arsenal to help us optimize our relational workloads.

Query Performance Insight allows you to spend less time troubleshooting database performance by providing the following:

  • Deeper insight into your databases resource (DTU) consumption.
  • The top DTU consuming queries, which can potentially be tuned for improved performance.
  • The ability to drill down into the details of a query.

For more information, visit the Query Performance Insight page on MSDN

Following up on my Azure SQL Database performance tools and practices I’m very happy to bring your attention to a tool that was published a few days back and allows you to spend less time tuning your database performance.

The Azure SQL Database Index Advisor recommends new indexes for your existing SQL Databases that can improve current query performance.

The SQL Database service assesses index performance by analyzing historical resource usage for a SQL Database and the indexes that are best suited for running the database’s typical workload are recommended.

recommended-indexes

Index advisor makes index management easier by providing recommendations on which indexes to create. For V12 servers, Index advisor can also create and validate indexes with just a few clicks in the Azure Portal. After the index is created, the SQL Database service analyzes performance of the database workload and provides details of the impact of the new index. If the analysis determines that a recommended index has a negative impact on performance, then the index is reverted automatically.

You can find more information about Azure SQL Database Advisor here

The fourth free ebook in Microsoft Press’s free Microsoft Azure Essentials series, Microsoft Azure Essentials: Azure Web Apps for Developers (9781509300594), by Rick Rainey, has been released and is available for all of you that want to dive in essential information about developing web applications hosted on Azure Web Apps. It is written with the developer who has experience using Visual Studio and the .NET Framework in mind. If Azure Web Apps is new to you, then this book is for you. If you have experience developing for Azure Web Apps, then this book is for you, too, because there are features and tools discussed in this text that are new to the platform.

Organization of this book
This book provides information you can use to start building web applications using Azure Web Apps. It will guide you through development, deployment, and configuration tasks that are common for today’s developer building cloud applications.

Each chapter stands alone; there is no requirement that you perform the hands-on demonstrations from previous chapters to understand any chapter. The topics explored in this book include the following:

  • Chapter 1, “Microsoft Azure Web Apps”: This chapter starts with an introduction to Azure Resource Groups and App Service Plans and progresses into essential tasks such as creating and configuring a web app. Learn best practices for storing and retrieving app settings and connection strings. Configure deployment slots and set up continuous deployment using Visual Studio Online. Wrap up with a discussion about Role Based Access Control (RBAC) and how you can use it to manage access to your Azure resources.
  • Chapter 2, “Azure WebJobs”: Learn everything you need to know to build and deploy background processing tasks using Azure WebJobs. You will learn the basics of the WebJobs feature and proceed into a deeper discussion on how to use the WebJobs SDK. You will learn about the Azure WebJobs Dashboard and how the WebJobs SDK enhances the dashboard experience.
  • Chapter 3, “Scaling Azure Web Apps”: Learn how to scale up and scale out your Azure web app and web jobs. You will learn how to configure Autoscale to scale your web app dynamically based on performance metrics and schedules. See how you can use Azure Traffic Manager to achieve global scale for your web apps.
  • Chapter 4, “Monitoring and diagnostics”: Learn about the many logging features built into the Azure Web Apps platform and how to configure logging to get the diagnostics data you need to troubleshoot issues. You will learn how to configure storage locations and retention policies for logs, how to view logs in real time using the log streaming service, and even how to debug your web app remotely while it is running in Azure. You will get an introduction to some powerful site extensions you can use to view logs and perform analysis directly from your browser. Finally, you will learn how you can monitor your resource group down to individual resources and how you can use Application Insights to deliver a complete 360-degree view into your application code for monitoring and diagnostic purposes.

Download it from here

 

The patterns & practices team has been working on developing Azure architecture guidance.

The first round of  guidance is now available to public at https://github.com/mspnp/azure-guidance. The purpose of this project is to provide architectural guidance to enable you to build and deploy world-class systems using Azure. These documents focus on the essential aspects of architecting systems to make optimal use of Azure, and summarize best practice for building cloud solutions. The current set of guidance documents contains the following items.

· API Design describes the issues that you should consider when designing a web API.

· API Implementation focuses on best practices for implementing a web API and publishing it to make it available to client applications.

· Autoscaling Guidance summarizes considerations for taking advantage of the elasticity of cloud-hosted environments

· Background Jobs Guidance describes the options available, and best practices for implementing tasks that should be performed in the background.

· Content Delivery Network (CDN) Guidance provides general guidance and good practice for using the CDN to minimize the load on your applications, and maximize availability and performance.

· Caching Guidance summarizes how to use caching with Azure applications and services to improve the performance and scalability of a system.

· Data Partitioning Guidance describes strategies that you can use to partition data to improve scalability, reduce contention, and optimize performance.

· Monitoring and Diagnostics Guidance provides guidance on how to track the way in which users utilize your system, trace resource utilization, and generally monitor the health and performance of your system.

· Retry General Guidance covers general guidance for transient fault handling in an Azure application.

· Retry Service Specific Guidance summarizes the retry mechanism features for the majority of Azure services, and includes information to help you use, adapt, or extend the retry mechanism for that service.

· Scalability Checklist summarizes best practices for designing and implementing scalable services and handling data management.

· Availability Checklist lists best practices for ensuring availability in an Azure application.

The authors state that this is a living project and they welcome feedback, suggestions, and other contributions to those items. So if you have any comments you can  join the gitter chat for questions or suggestions.