Ggthjy

In SQL 2017 there is a new execution metric, 'Log memory' other than that it was added in 2017 I am not finding anything about it.

Execution metric: (SQL 2017)

CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, Physical Reads, CLR time, Degree of Parallelism (DOP), Row count, Log memory, TempDB memory, and Wait times

I believe I understand what all the other metrics are and why I might care.

I ran all the metrics for the top 5 resource consuming queries, during several specific periods. I recorded and now I am examining the results. I know the (very large) values for 'Log memory' are in KB's.

What exactly is the metric 'Log memory'?

If we look at the documentation for the underlying object, sys.query_store_runtime_stats, we'll see it has the following descriptions:

• 
  avg_log_bytes_used - Average number of bytes in the database log used by the query plan, within the aggregation interval. Applies only to Azure SQL Database.
  Note: Azure SQL Data Warehouse will always return zero (0).


• 
  last_log_bytes_used - Number of bytes in the database log used by the last execution of the query plan, within the aggregation interval. Applies only to Azure SQL Database.
  Note: Azure SQL Data Warehouse will always return zero (0).


• 
  min_log_bytes_used - Minimum number of bytes in the database log used by the query plan, within the aggregation interval. Applies only to Azure SQL Database.
  Note: Azure SQL Data Warehouse will always return zero (0).


• 
  max_log_bytes_used - Maximum number of bytes in the database log used by the query plan, within the aggregation interval. Applies only to Azure SQL Database.
  Note: Azure SQL Data Warehouse will always return zero (0).


• 
  stdev_log_bytes_used - Standard deviation of the number of bytes in the database log used by a query plan, within the aggregation interval. Applies only to Azure SQL Database.
  Note: Azure SQL Data Warehouse will always return zero (0).

I think LowlyDBA's answer covers what the metrics actually mean. This answer is just an additional clarifying point.

It appears the documentation is inaccurate with regards to "Applies only to Azure SQL Database". Maybe this is an old note in the docs from before those columns made it into the on-prem product, but it is easy to get data in those columns on SQL Server 2017 Developer Edition on my laptop.

Create a database:

USE [master];

GO



CREATE DATABASE [231682];

GO

Enable Query Store with very impractical settings:

ALTER DATABASE [231682] SET QUERY_STORE = ON (INTERVAL_LENGTH_MINUTES = 1);

Do something that will generate some transaction log usage:

USE [231682];



CREATE TABLE dbo.Junk

(

    Id INT NOT NULL,

    MoreJunk NVARCHAR(MAX) NOT NULL

);



INSERT INTO dbo.Junk

    (Id, MoreJunk)

SELECT TOP 1000

    m.message_id, m.[text]

FROM sys.messages m;

Force flush to disk in case it hasn't happened yet:

EXEC sp_query_store_flush_db;

Voila:

SELECT 

    qsrs.avg_log_bytes_used, 

    qsrs.last_log_bytes_used, 

    qsrs.min_log_bytes_used, 

    qsrs.max_log_bytes_used, 

    qsrs.stdev_log_bytes_used

FROM sys.query_store_runtime_stats qsrs

WHERE qsrs.avg_log_bytes_used > 0;