Apart from using the Query Store tables, you can obtain a comprehensive array of information related to Query performance from dynamic management objects. Dynamic management objects more commonly known as DMVs for Dynamic Management Views or DMFs for Dynamic Management Functions.

The DMVs are found under a databases system views node in SQL Server management studio, while the DMFs under the system functions of the model database. The names all start with dm underscore followed by the category they relate to, CDC for change data capture, DB underscore iOS, for SQL Server operating system view. You get the idea. There are a lot of these dynamic management views and functions and they cover all aspects of a server instance operations, as well as database states.

Here's a small selection of the DMV categories that are available. In terms of monitoring or reporting on crew performance, we're interested in the execution-related DMVs. Of all the DMO categories, execution-related is the most well covered.

As you can see from the views and functions depicted here. The two that are of most interest to us are dm query stats and dm exec query profiles. Just to reiterate, while I'm showing you these views and functions in the context of SQL Server that also present an Azure SQL.

Here we have a table of the columns returned by the dm_exec_query_stats view. This view returns information about the query across its lifetime, as opposed to an instance of execution. For most of the matrix, you can see data from the last execution plus min-max and total values. The execution count number will allow you to calculate average values were appropriate.

Speaking of average statistics, here we have a Query returning the top five CPU intensive queries with their plans, using total worker time divided by execution count to calculate the average. As you can see, a Query statistic is related to a combination of Query and plan. This Query is returning some statistics for an SQL Server statement, regardless of execution plan by matching against DM SQL Query text.

We can see which queries have been cached by interrogating dm_exec_cache plans. Not only can we get the SQL, but also the object and cache types. You can get information like that found in live Query statistics, by using the dm_exec_query_profiles view. This view will return real-time metrics related to the plan of the query that is currently executing.

What we'll do, is have a look at the same insert Query but this time run a profile Query against it as it executes. The easiest way to do this is to open up another Query window and paste the profile Query in. As you can see the dm_exec_query profiles view requires a session ID.

So just do a select on SP ID and that is 61. The profile DMV will only return results while the Query is executing. This is good for long running queries where we want to see how they're progressing, but in this case, I'm going to need a quick mouse hand and we'll have to slow the video down.

I'll execute the target Query and then quickly flick over to the profile Query and start heading the execute button there. I've removed the index that was earlier recommended by the Query plan. So we can see on row 12 that we're starting with the table scan, as we did originally.

Also, you will recall that the final number of rows that was inserted was around 1.9 million. And on this first execution, we are at 550,000. But you can see the estimated row counts are the same as the ones we saw earlier in the estimated plan.

On the second execution, we can see that the very first operation, the table scan is still progressing and the rows are flowing through the whole operation into the destination table. I'll hit execute again in the table scan and insert a still proceeding. This is a good illustration of SQL Servers parallelism, and multithreading.