Systems & Databases

Balazs Berki

  • Home
  • Downloads
  • About

A lightweight alternative to Query Store

17th November, 2018 · Balazs Berki

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.

Posted in Query Store, SQL Server |
« Issues with monitoring open transactions: Schema lock
Login timeouts in SQL Server due to delayed SSPI handshakes »

Leave a comment Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • SQL Server Failed logins: TLS Handshake issues
  • Scheduled tracing with procmon
  • Login timeouts in SQL Server due to delayed SSPI handshakes
  • A lightweight alternative to Query Store
  • Issues with monitoring open transactions: Schema lock

Archives

  • January 2020
  • May 2019
  • March 2019
  • November 2018
  • January 2018
  • April 2017
  • February 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016

Categories

  • ALTER TABLE MOVE
  • Avaloq
  • connectivity_ring_buffer_recorded
  • CPU
  • DATA_COMPRESSION
  • Database Crash
  • dba_users
  • emocmrsp
  • expiry_date
  • extended events
  • Full Table Scan
  • Kerberos
  • Kerberos Keytab
  • Kerberos Ticket
  • ktutil
  • LCK_M_SCH_S
  • logman
  • LogMiner
  • modelcache
  • open transaction
  • ORA-17627
  • ORA-17629
  • ORA-56904
  • Oracle
  • Oracle Client
  • Oracle client Install Script
  • Oracle Database In-Memory
  • Oracle monitoring
  • Oracle Single sign-on
  • Oracle WaitEvents
  • PerfMon
  • Pivot
  • Powershell
  • procmon
  • Python
  • Query Store
  • READ_COMMITTED_SNAPSHOT
  • Scheduled Task
  • Schema Lock
  • SQL Server
  • SSMS
  • TLS Handshake
  • wait_info_external
  • Windows Event Log

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org
© balazsberki.com
  • Downloads
  • About