Azure sql db instance is taking very long time for a simple select query . How can we improve his ?

Question

Azure sql db instance is taking very long time for a simple select query . How can we improve his ?

Amar Agnihotri 826

Hi,

I have this azure sql db instance

User's image

I am having table in database which is having only 12000 rows. A week ago everything was working fine but suddenly since last 3 days even select query is taking very long time . See the snap. For fetching the 12000 rows it took almost 11 minutes

User's image

Here is my azure sql db settings

User's image

What happened to my azure sql db ? How can i improve the performance ?

Answer 1

Before you investigate any further, could you please update statistics and defragment indexes using Ola Hallengren scripts and let us know if that improved performance. Donwload the scripts (IndexOptimize.sql and CommandExecute.sql) from here.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',@FragmentationLow = NULL,@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationLevel1 = 5,@FragmentationLevel2 = 30,@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y'

One thing you need to know is the following:

Memory for serverless databases is reclaimed more frequently than for provisioned compute databases. This behavior is important to control costs in serverless and can impact performance.

Source: Serverless documentation.

The above excerpt from the Serverless documentation, is telling you that after some time you execute a query and it will have to bring pages of data from disk to memory (producing waits like PAGEIOLATCH and MEMORY_ALLOCATION_EXT) , instead of finding them on the memory cache, and that will impact performance. Adding to that, you have only one core assigned to the serverless database, all your queries will not use parallelism.

If the above does not help, could you please share the execution plan of the query and let us know the top waits when you execute the query using (use instructions provided on this article).

Amar Agnihotri 826 Reputation points

2023-01-30T06:12:33.54+00:00

@Alberto Morillo Thanks for the reply. I found one thing in my sql db instance and that is allocated storage space. You can see here that the max space allowed is 32 gb but total allocated space ios 8.2 gb out of which 6.5 gbs have been used

I think the problem is occurring due to the space issue so i want to fix this first. Can you please tell me how can i increase the size of allocated space.

This is my plan for azure sql db

I tried a lot but didn't find any way to increase the allocated space using azure portal. Please help me out in this and then i will check the performance
Alberto Morillo 26,111 Reputation points MVP

2023-01-30T23:16:06.47+00:00

@Amar Agnihotri on the same screen you shared with me, please scroll the bar to the right or set the numeric value yourself. Please see the image below:
Oury Ba-MSFT 10,326 Reputation points Microsoft Employee

2023-02-07T21:07:27.91+00:00

@Amar Agnihotri Please don't forget to mark as accept answer if the reply from @Alberto Morillo was helpful.

Regards,

Oury

Answer 2

Oury Ba-MSFT 10,326 Microsoft Employee

Hi Amar Agnihotri Thank you for reaching out.

Did you had the chance to check out @Alberto Morillo answer above. Let us know if that was helpful. In addition to that you could also consider increasing the service tier. To start usually anywhere between 2-4 levels up should help. Please let us know the progress.

Regards,

Oury

Azure sql db instance is taking very long time for a simple select query . How can we improve his ?

1 additional answer

Activity