Hi Friends,
I would like to share an interesting scenario with you all. The issue started on Azure Managed instance where my lower Environment is residing (DEV/QA/UAT) and the production is hosted on on-premises.
Application Team raised a ticket stating the performance is way too bad on Azure MI. When cross checked the query was taking 25 minutes and on production it was just taking 1 minute.
Now the ball has come to my court and below are the things what I verified.
Resource comparison in Azure MI(RAM:20.4 GB CPU's:4) and On-premises (RAM: 32 GB CPU's :8)
In Azure MI for every 1 core we will have 5.1 GB of RAM
Then I went with Index definition ,compatibility level and statistics and they all look one at the same.
By the way Azure MI is hosted in General purpose tier so obviously the storage will not be faster.
Forgot to mention I verified whether the application server is hosted in Azure or not and it is.
My initial suspect was Resources shortage(Ram & CPU) on MI. However anything in Azure is cost based. Now when I asked the developer to run the query most of times it is in suspended state with waitstats as "PAGEIOLATACH". This again made me to think from Storage perspective again we can't change that from General to Business critical.
After a day developer told me that it was working fine on one of the databases. However the no of rows are almost Zero for many of the tables related to problematic query.
As you can see from above image hence we can expect that to be faster.
Now I am back to square one but this time I thought of changing the CL from 110 to 150 and it did worked.
Before providing the explanation I made use of https://statisticsparser.com/ to see the number of reads before and after the CL change.
Before Change:
After change:
As you can see the logical reads has come down a lot and also the query which was running for 25 min got reduced to 5 min.
The query was not able make use of parallelism as it has inhibitors when you right click and open the execution plan as XML you will very well find the reason. In my case it was below
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable"
of course I could have changed that but this being vendor application I can't change the code. Now finally how did SQL Server 2019 helped me because it was making use of new feature batch-mode-on-rowstore.
In my query they were making use of Windows partitions like below
SELECT ROW_NUMBER() OVER (PARTITION BY IDMASTransaction ORDER BY txfe.ID DESC) As RowNum
Usually batch mode are super good with Colum Store Indexes which were introduced in SQL Server 2012. However from 2019 there is no need for the table to have Column Store Indexes and it can work very well with Analytical Queries.
This is Database Scoped configuration
1 | ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON |
That being said kindly don't turn it on until proper testing has been done. In my case it was fruitful
Below link will further help you to understand this concept
https://techcommunity.microsoft.com/t5/azure-sql/introducing-batch-mode-on-rowstore/ba-p/386256
I hope you will explore this in your Environment as well. Thanks for Reading.
Comments