During performance tuning in SQL Server, one of the most frequently asked questions is how to determine the number of rows returned by a query and its execution plan. While this information can be useful, it is equally important to consider the number of reads performed by the query and the worker time.

If you are interested in finding the most expensive queries and their execution plans, you can use the following query:

SELECT
    DB_NAME(qt.dbid) AS database_name,
    qs.execution_count,
    qt.text AS query_text,
    qs.total_rows,
    qs.last_rows,
    qs.min_rows,
    qs.max_rows,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.plan_handle) AS qt
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
    qs.execution_count DESC

This query retrieves the query execution count, the number of rows returned, and the execution plan. By clicking on the link in the last column, you can view the execution plan of the query.

It is important to note that this query only returns results from the cache. On a busy system, the cache may be cleaned more frequently, resulting in less accurate results. Additionally, like any other DMV (Dynamic Management View), this query returns results from the time when your SQL Server services were last restarted.

If you find this information useful, I recommend bookmarking this page for future reference.