Running Queries Tab for Server and Elastic Pool Level

[jacobgexigo]

It seems that there is some issue with the Running Queries tab when at the instance or elastic pool level. No matter the selected timeline, the data seems inaccurate at this level. For instance, I have 45 databases in an elastic pool, five elastic pools on an instance, 150 databases in total...if I got to Elastic Pool 3 under the instance, sort the data by Longest Running Query descending, and they are all near zero for time. I can go to any one of the databases and do the same thing at that level and see queries running for 7 minutes, etc.

Elastic Pool Level

One of the Databases Level inside the Elastic Pool

[jacobgexigo]

Also, if you do the same thing and chose a Custom timeline (say five days ago), this same level (EP or Instance) will not change to show the correct dates, it keeps the current snapshot dates on the rendered grid.

[DavidWiseman]

At root level, or elastic pool level, the Running Queries tab shows the data associated with the most recent snapshot for each instance. It allows you to see what is happening right now across all of your SQL instances. If you change the date range it's still showing the data from the most recent snapshot. If it took the date range into consideration it would show you the snapshot from around midnight on Monday which might not be that useful. I think what you are interested in would be to show the max running queries over the selected time period etc. Maybe the report could be changed to show this alongside the most recent snapshot data for the period, taking the date range into consideration.

[jacobgexigo]

What we have started running into is that any database in an elastic pool with a long running query can cause the Persistent Version Store of any other database in the pool to not flush. So what we are looking for is the ability to go back in time across the entire elastic pool and see which database at 2AM was the one with a long running query cause 16 other databases to grow their PVS to 50% of the total database size. We can do it by clicking on all 50 databases in the EP, but it would be better for us to be able to look at the snapshot in history at the EP level to save a huge amount of time in correlation.