Using an Azure SQL elastic pool with 10 cores when it is completely idle (over a weekend), query store shows an SP/query using 29 seconds of CPU and causing a high average CPU usage alert to trigger at 80%.
I recreated the situation when the pool was also idle the following Monday early morning, and fired the SP/Query with the same parameter values and watched for the results in sys.dm_exec_query_stats.
CPU was about 0.1 ms. Query store showed it used the same QueryId, same PlanId, same distribution of load evenly spread throughout the plan. Also, the plan did not go parallel.
Because it was over a weekend, the volume of data in the tables had not changed in any measurable way. I also checked that the data being returned by the SP existed months before this incident so it is not a case of the results changing in between times.
I'm much more of a developer than a DBA, so can anyone me where else to look to find out why, and how, a query could perform so drastically different under the apparent same environment? Is this a phenomenon that has something to do with the serverless environment of Azure SQL specifically?
Some more stats requested in comments:
- compile time for both query runs was 77ms and 47ms CPU.
- plan was retrieved from cache in both instances.
- wait time doesn't seem to be available for the short run time.
- wait time was "CPU" 6303ms for the long run time.
Many thanks for your help!
1条答案
按热度按时间s4n0splo1#
This is a standard SQL Server pattern. The first time you run a query it has to do a lot of physical IO, memory allocation raises and it's slow. You may take a look at queries performing slow and they may be showing the PAGEIOLATCH_SH and MEMORY_ALLOCATION_EXT waits and that corresponds to pages being pulled from disk to the buffer. The second time you run the query the data is in buffers and it's fast.
After a period of inactivity memory allocation drops. Azure SQL Database shrinks memory allocation after the database has not been used for some time or the database tier has been scaled up or down. You will see this happening on Azure SQL Database but not on SQL Server instances (IaaS). If you set a database as Serverless you will see memory is reclaimed even more frequently as you can read in the Azure SQL Serverless documentation here, and that has a greater impact in performance.
Another possible reason is queries are waiting for a synchronous statistics update to complete before the compilation and then execution can resume. Please try to enable Async update statistics as explained here . Regularly updating statistics removing index fragmentation can improve the performance also, so no need to update stats prior to execute queries because they are updated by a maintenance job regularly.