10

The documentation for sys.dm_exec_query_stats states the following:

An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

I sometimes query that DMV during an active workload and would prefer accurate results. I do not know how to apply the above warning in practice. Should I always query the DMV twice and use the second result set because that will be more accurate? That feels a bit far-fetched. Do I need to be aware of the ways in which the DMV can be inaccurate so I can factor that into my analysis? If so, what kind of inaccuracies can appear: missing rows, outdated values, inconsistent rows, or something else?

What are best practices when using sys.dm_exec_query_stats during an active workload?

6

I've updated the documentation to be more straight-forward on the wording. The intent was to tell the user that running a query against the DMV will produce output only for items that had completed and not for items in-flight. Thus, run the workload in its entirety to make sure all is captured.

The new wording will be the following and applied the various DMVs the previous wording was incorporated into.

The results of sys.dm_exec_query_stats may vary with each execution as the data only reflects finished queries, and not ones still in-flight.

4

I do not know how to apply the above warning in practice.

That's because it is vague. There is a space at the end of the documentation page to leave feedback. That is pretty much the only way someone will be able to look into the history of that documentation page and see why that warning was added, if it is still necessary, or needs to be updated.

I took a quick look at the implementation on SQL Server 2017 CU 15 and it seems there is no special reason to expect inconsistency (or "inaccuracy"), aside from the totals only being updated at the end of each statement execution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.