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

Amar Agnihotri 826 Reputation points


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 ?

Azure SQL Database
No comments
{count} votes

Accepted answer
  1. Alberto Morillo 26,111 Reputation points MVP

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

1 additional answer

Sort by: Most helpful
  1. Oury Ba-MSFT 10,326 Reputation points 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.