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

Question

Hi,

I have this azure sql db instance

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

Here is my azure sql db settings

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).

Answer 2

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

Answer 3

I have the same problem with a slightly larger table ~ 400,000

For months a full read (select <list of columns> from <table>) completed in a few seconds.

Suddenly it takes 15 minutes.

I have applied all the answer suggestions, but nothing makes a difference.

The retrieval strategy is a Clustered Index Scan, which seems inappropriate for a full table select, where it doesn't need to go via an index at all.

Why the sudden change (for the worse)?