Monitoring current query execution statistics

In order to perform query tuning on production database server, you need to identify those resource-consuming queries and the source from where they are coming. For this, you need to monitor the incoming query requests and examine their execution time, number of read/write operations, and so on.

SQL Server has dedicated a separate category of DMVs and DMFs to query execution statistics. These DMVs and DMFs provide a wide range of statistics on query execution requests. The names of these execution-related DMVs and DMFs are generally prefixed by sys.dm_exec_. By examining the results returned by these DMVs and DMFs, you can find out long-running and resource-consuming queries, and pick them up for query tuning.

In this recipe, we will see how to monitor current incoming query requests made on SQL Server by using DMVs and DMFs in order to find out the queries with higher execution time. We will also learn how to monitor currently opened cursors...