The aim of this post is to present a lightweight alternative to the SQL Server Query Store. You can find the full code on my Github Page here:
https://github.com/bbalazs83/SQL-Server-Query-Statistics

With a heavy adhoc workload and a lot of parsing, query store can cause major memory bottlenecks by consuming a lot of MEMORYCLERK_QUERYDISKSTORE_HASHMAP memory. Although there are settings that can be used to mitigate this memory usage (limiting stale_query_threshold_days or setting query_capture_mode to auto) but sometimes these settings will not help at all.

If you still would like to collect statistics for the queries running in your SQL Server Instance you can use the following 3 views to gather statistics on a regular basis:
– dm_exec_query_stats
– dm_exec_procedure_stats
– dm_exec_function_stats
To get the SQL Texts we will use the table-valued function dm_exec_sql_text.

Collecting data about query statistics

First we will need to create our tables corresponding to the views mentioned above. To keep it short I decided I do not want to collect the data from the dm_exec_function_stats view.

After creating the objects as in the installer.sql file we can set up an SQL Server Agent Job that is executed periodically (for example once an hour) to dump the data from the views into the tables. The procedure collect_execution_statistics will be used for this purpose.

Querying the collected data

Querying can be done by using the table valued functions get_query_statistics and get_procedure_statistics. The functions will calculate the deltas from the snapshots taken during data collection.

Using the above mentioned views and functions we are able to quickly get an overview of the historical load and find out which queries are consuming resources without using Query Store or a third party tool.