Sfdwhf

Does an unused member variable take up memory?

Do I need a return ticket to Canada if I'm a Japanese National?

Tabular environment - text vertically positions itself by bottom of tikz picture in adjacent cell

Draw this image in the TIKZ package

Unidentified signals on FT8 frequencies

What does it take to become a wilderness skills guide as a business?

I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?

What is the oldest European royal house?

Has a sovereign Communist government ever run, and conceded loss, on a fair election?

What do you call someone who likes to pick fights?

Giving a talk in my old university, how prominently should I tell students my salary?

Rationale to prefer local variables over instance variables?

Is "cogitate" used appropriately in "I cogitate that success relies on hard work"?

Why do we say 'Pairwise Disjoint', rather than 'Disjoint'?

Having the player face themselves after the mid-game

How to write a chaotic neutral protagonist and prevent my readers from thinking they are evil?

Can multiple states demand income tax from an LLC?

Can I challenge the interviewer to give me a proper technical feedback?

Book where society has been split into 2 with a wall down the middle where one side embraced high tech whereas other side were totally against tech

Help! My Character is too much for her story!

How to recover against Snake as a heavyweight character?

Vector-transposing function

What is the purpose of a disclaimer like "this is not legal advice"?

Is there a math expression equivalent to the conditional ternary operator?

What is 'Log Memory' in Query Store 2017

Production SQL Server High CPU for a Single QueryTroubleshooting SOS_SCHEDULER_YIELD waitWhy does removing a RID by creating a clustered index lose parallelism and slow down execution by 2XAggregation Operations on View Ignores IndexQuery against sys.schemas and sys.synonyms runs very slow for one userRunning query against DMVs for Query Stats and Execution Plans joined to sys.databasesWhat interval does SQL Server Profiler's “duration” refer to?high writelog wait on OLTP system and fast SSD storage, log flush is slowSQL Server's “Total Server Memory” consumption stagnant for months with 64GB+ more availableWhy is selecting all resulting columns of this query faster than selecting the one column I care about?

2

1

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'?

sql-server sql-server-2017 query-store

share|improve this question

asked 9 hours ago

James JenkinsJames Jenkins

1,81121941

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I found a similar unanswered question on SO MSSQL - Log Memory Used - Query Store
  
  – James Jenkins
  8 hours ago
  

  
  
  
  

add a comment | 

2

1

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'?

sql-server sql-server-2017 query-store

share|improve this question

asked 9 hours ago

James JenkinsJames Jenkins

1,81121941

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I found a similar unanswered question on SO MSSQL - Log Memory Used - Query Store
  
  – James Jenkins
  8 hours ago
  

  
  
  
  

add a comment | 

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'?

sql-server sql-server-2017 query-store

share|improve this question

asked 9 hours ago

James JenkinsJames Jenkins

1,81121941

share|improve this question

asked 9 hours ago

James JenkinsJames Jenkins

1,81121941

share|improve this question

asked 9 hours ago

James JenkinsJames Jenkins

1,81121941

asked 9 hours ago

James JenkinsJames Jenkins

1,81121941

asked 9 hours ago

James JenkinsJames Jenkins

1,81121941

2 Answers
2

active

oldest

votes

4

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).

share|improve this answer

answered 7 hours ago

LowlyDBALowlyDBA

7,12252542

add a comment | 

1

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;

share|improve this answer

answered 2 hours ago

jadarnel27jadarnel27

6,27012038

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231682%2fwhat-is-log-memory-in-query-store-2017%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4

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).

share|improve this answer

answered 7 hours ago

LowlyDBALowlyDBA

7,12252542

add a comment | 

4

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).

share|improve this answer

answered 7 hours ago

LowlyDBALowlyDBA

7,12252542

add a comment | 

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).

share|improve this answer

answered 7 hours ago

LowlyDBALowlyDBA

7,12252542

share|improve this answer

answered 7 hours ago

LowlyDBALowlyDBA

7,12252542

answered 7 hours ago

LowlyDBALowlyDBA

7,12252542

answered 7 hours ago

LowlyDBALowlyDBA

7,12252542

1

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;

share|improve this answer

answered 2 hours ago

jadarnel27jadarnel27

6,27012038

add a comment | 

1

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;

share|improve this answer

answered 2 hours ago

jadarnel27jadarnel27

6,27012038

add a comment | 

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;

share|improve this answer

answered 2 hours ago

jadarnel27jadarnel27

6,27012038

USE [master];

GO



CREATE DATABASE [231682];

GO

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

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;

EXEC sp_query_store_flush_db;

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;

share|improve this answer

answered 2 hours ago

jadarnel27jadarnel27

6,27012038

answered 2 hours ago

jadarnel27jadarnel27

6,27012038

answered 2 hours ago

jadarnel27jadarnel27

6,27012038