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 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:
@Amar Agnihotri Please don't forget to mark as accept answer if the reply from @Alberto Morillo was helpful.