I am currently testing out a new azure server with 200 DTUs. This server is currently being used by VBA scripts in Excel / Reconciliation Programs and SSMS. However, every morning when I try to run a query within excel it times out and will not give me any results as I have timeout set to 60 seconds but it should really only take 10 seconds to run at tops. To resolve this I have to run a similar query that looks up data from the tables on SSMS to "wake them up" and that only takes 7 seconds. After I do this all the macros I have in excel work fine, does anyone know why this would happen? I understand that if you ran the exact same query it would be cached however these are not the same query just looking at the tables with different where clauses?
Asked
Active
Viewed 96 times
0
-
1Some food for thought, Caching, loading from Disk, etc https://stackoverflow.com/q/70276704/495455 – Jeremy Thompson May 24 '22 at 07:28
-
Caching is not by "query" but by table. In other words: a query will load pages from a table into memory, and if those pages are needed by another query they will not be loaded again (until evicted by other pages being cached). It's also possible you need to set Azure wakeup times https://www.sqlshack.com/automatic-pause-and-resume-an-azure-sql-database/ – Charlieface May 24 '22 at 08:42