Category: Best Practices

In this blog post, I wanted to cover how to go about troubleshooting an App Service in Aure which is a web app with a SQL server backend whereby users have reported issues with the slow performance of the website.

The first thinh I tend to look at is the backend store, in this case, Azure SQL Server and we have some really great tooling we can use to troubleshoot perforamce issues with Azure SQL.

The first port of call was to open up the Azure Portal and go to the Resource Group with the issues and click on the SQL Server database and head to the Intelligent Performance section on the left-hand menu as highlight below: –

Performance Overview
This currently has a recommendations area that suggests adding 5 different Indexes which are all set as HIGH impact.

Indexes can sometimes cause adverse effects so it’s recommended to look at the suggestions, copy the script from the recommendations and consider if this Index will indeed help with the performance of queries.

Query Performance Insight
The second area I look at is query performance insight and from here we can see the average CPU, Data IO, Log IO on the SQL Server database across the last 24 Hours as an average. We also get an insight into what queries are running and taking the longest time to complete.

I changed the graph above to show the last 7 days and I can see CPU is maxed out at 100% for a long period within the last 7 days as seen below:-

Long Running Queries
This area identifies queries which are taking a long time to complete and always worth checking on this regularly.
The following is a screen shot of long running queries within the database for the past week. To find this information select the database instance in the portal and then select Query Performance Insight and select Long running queries, then I chose custom and changed the time period to Past week.

We can see above the yellow query is the database query which has the longest duration this past week, you can click on the yellow area and it will show you the details of the query which is a long running query.

Automatic Tuning

Azure SQL Database built-in intelligence automatically tunes your databases to optimize performance. What can automatic tuning do for you?

• Automated performance tuning of databases
• Automated verification of performance gains
• Automated rollback and self-correction
• Tuning history
• Tuning action Transact-SQL (T-SQL) scripts for manual deployments
• Proactive workload performance monitoring
• Scale out capability on hundreds of thousands of databases
• Positive impact to DevOps resources and the total cost of ownership

I would recommend that FVN turn this on and leave it like the following:-

This means that Azure will tune the indexes using built in intelligence and create indexes when it thinks you need them based on usage patterns. A word of caution here as these recommendations aren’t always correct so please bare this in mind.

Log Analytics
I always recommend adding the Azure SQL Analytics workspace solution to the subscription and this gives us further insight into the SQL Server in Azure. Once you turn this on you need to wait sometime before it can gather a decent amount of data.

The screen shot below shows us the type of information we can get from it, this screen shot was taken not long after being turned on so if you wait some time it will have much more useful details:-

From here we can get more information about deadlocks, timeouts, etc.

Now lets take a look at the website which is in an App Service in Azure and see what tool we can use to help us troubleshoot issues with the performance.

I always recommned adding Application Insights into Azure for resources when possible, and here if we click on the App Insights for the web app we can instantly get soe basic info. If you click on the Application Dashboard as seen below we get a high level vue of whats going on in our App Service.

The Application dashboard for a typical web app might look something like this: –

Ok, so let’s now do some further investigation into our app service issues. This time I chose the App Service itself and then I chose Diagnose and solve problems from the left-hand menu. This feature is underused in my opinion and is very useful indeed, not sure if many people have looked at it but it can be pretty helpful with recommendations and also pointing out some things that you may want to think about remediating.

Once in the Diagnose and solve problems area I usually click on Availability and Performance within the Troubleshooting categories section and if you do, you’ll see something like this: –

In the image above we can see that we have some App Performance issues to go and investigate. Clicking into the App Performance section we get in-depth details about the Performance and we get Observations that say things like Slow Request Execution with details of the web page, average latency, total execution time, etc. The detail here is very helpful in tracking down potential issues in the code, or the configuration of your web application. There are a number of options to check within each section of the 6 troubleshooting categories, an example is shown below for the Availbility and Performance section: –

Summary
In summary, there are a number of really awesome tools to aid us with troubleshooting App Service perormance issues, go check them out the next time your web app is running poorly.

I have been familiar with Azurecharts.com long before it was mentioned on twitter by Mark Russinovich who you are all hopefully following on twitter.

The site is written by Alexey Polkovnikov and is one I keep coming back to and always point people to. There is a lot of very useful links on here.

The site has a number of features that I wanted to cover and you can read about what it can be used for in the about page.

The main concept is that you can go here and see what is new in each area of Azure right down to the actual azure service itself and this is continually updated for you.

I wanted to cover the areas which I think are incredibly useful that you may have missed within the site, so lets dive right in.

Highlights
Highlights allow you to choose a job role and it will highlight the azure services most likely to be of interest for that role.

Overview
The overview lists all of the Azure services and you can click on each to be taken to the relevant azure docs page to learn more.

SLA
The SLA section shows you the SLA (service level agreement for Azure services which has an SLA.

Status
This screen shows us which services are Generally Available or in Preview.

Timeline
This screen shows us the timeline for when we can expect to see the feature rolled out to the particular region show for the service.

Regions
This screen allows us to compare services by region, and will shows us the timeline and the status for the service you click on.

Presence
Presence shows us how many regions the service you click on is present within and can show future availability stats for some services.

Solutions
Solutions are where you can choose a service and then from the drop-down at the top make a choice and it will take you to the docs for the selection you make – so for example, if I select the Azure Cosmos DB service I can then choose from the solutions drop down like so: –

Stories
Stories are where you can select an azure service and read customer stories on how they implemented the service and has some very interesting ideas and usages of the technology.

Fun
Within the fun screen there is an azure quiz and an azure menu covering starters, main dishes, and desserts, go take a look.

Summary
In summary, this is a great site with a tonne of useful information and one to share with your team or on twitter, etc. – huge thanks to Alexey for putting this together and keeping it up to date.