Data I/O Spiking to 100% in Azure SQL DB | Production vs QA

-










Hi everyone, welcome to another blog of iLearn. 

First of all, it always gives a different "high" when you tackle a performance-related issue. This particular scenario was that the query in our quality assurance environment was running quicker than the query in production. 

Here is how we started the investigation and arrived at the solution.

First of all, started looking at the statistics. 

1. The First check was to see if the auto stats property is set to active or not and it was ON both in Production and QA databases

SELECT name,is_auto_create_stats_on FROM sys.databases WHERE name = 'OurDatabase';  

2. The Second check was to update the stats on the table (in Production) being queried (since we found that the stats were not up to date)

update STATISTICS OurTable;

Didn't do any good, the query was still running slow

3. Finally decided to compare the configuration for Production and QA Database instances and found that the QA is set to 8 vCores and Production to 2vCores and bingo! Found the culprit!!!

Ideally, this should be the first stop as far as the investigation goes. But no issues all part of learning. 

One of the other challenges we faced was how to correlate high Data I/O to vCore. I mean to say how could we say that if vCores are increased Data I/O spikes will be eliminated. Any suggestions on this?


Comments

Post a Comment

Popular posts from this blog

Issues Integrating Azure Data Factory with GITHUB | IN spite of admin rights on repository

-
Image
Although I have been aware of GitHub for quite a while but got a chance to use it as a repository in the project for storing artifacts only over the past few months.  We use GitHub as the sole cloud repo for storing SQL, Databricks, and Azure Data Factory artifacts.  Got an opportunity to set up GitHub with ADF right from scratch and all the excitement to learn more about GitHub took a back seat when I stumbled upon the following issue Problem Statement - You are an admin on a repo in GitHub and even then get a permission error while linking the ADF to GitHub Solution - After scratching my hairs and going on a searching spree on google for some days, I was able to figure out what needed to be done, however was not aware of how to implement it. Finally thanks to Microsoft for getting on a call and helping me resolve this issue. What needed to be done - Need to authorize AzureDataFactory service so that Github can communicate with it.  How it should be done -  Basically, you need to auth
Read more

SQL QUERY NIGHTMARE

-
Image
Last week, we faced a basic and yet the only major problem that sql developers/admin had to deal with and yes your guess is right, It was with SQL QUERY RUNNING SLOW.  I will be explaining the problem below, however I want everyone reading this to help me find answers because I still haven't found one! One more amazing aspect of the issue was that it worked fairly quickly in Development but was running like a tortoise in Production even though the number of records were practically the same. I'll start by throwing some light on to the execution plan, so that we narrow down to the code which was the culprit here. The code is enclosed into a stored proc. On contacting my dba to enquire about the issue, I got the following replay:- I must hand it to our dba to provide such a detailed response.  So my job was now to look at the code and modify it. What I did was to shrink the calender table into a temp table consisting of few rows that would speed up the operation. Bel
Read more

Visual Studio Git Error | "terminal prompts disabled"

-
Image
I was trying to sync our team's database project and was getting the below error git: 'credential-manager-core' is not a git command could not read Username for 'https://github.com': terminal prompts disabled One thing to call out here is that I had not synced the changes in the database project for over a month and there were no issues before.  My guess is that somehow the OWA authentication got wiped out from my system and this might be the reason for this issue.  In terms of the Solution I tried searching online on  terminal prompts disabled and got a plethora of solutions mostly suggesting some git commands. I tried a few of the suggestions and didn't get any luck. This morning I started searching for  git: 'credential-manager-core' is not a git command   and came across the following issue reported on Microsoft forums  Warning/error message when running git fetch within Visual Studio 2019 It basically suggests downloading/updating the latest version
Read more