When do _WA_Sys_ statistics Get Updated?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table)
The rule of thumb appears to be in tables >500 rows that statistics are updated at a 20% + 500 row change of data.
However, I can see using the following query
SELECT t.name,
i.name,
i.rowcnt,
i.rowmodctr,
p.last_updated
FROM sys.sysindexes i
JOIN sys.tables t
ON i.id = t.object_id
JOIN sys.stats s
ON s.object_id = t.object_id AND i.name = s.name
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
WHERE rowmodctr > 0
ORDER BY i.rowmodctr DESC
That there is a table with a number of _WA_Sys_ statistics that are way out of date (and have a rowmodctr that is higher than 20% + 500)
If a run a query against the table and add one of the columns associated with the out of date _WA_Sys_ statistics in the WHERE clause and check the updated date of the statistic, I can see it has updated.
If i run the same query with the WHERE clause again, the statistic doesn't update
It seems like the _WA_Sys statistics update when a query is run that will use them and they are out of date?
sql-server-2014 statistics cardinality-estimates
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
add a comment |
I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table)
The rule of thumb appears to be in tables >500 rows that statistics are updated at a 20% + 500 row change of data.
However, I can see using the following query
SELECT t.name,
i.name,
i.rowcnt,
i.rowmodctr,
p.last_updated
FROM sys.sysindexes i
JOIN sys.tables t
ON i.id = t.object_id
JOIN sys.stats s
ON s.object_id = t.object_id AND i.name = s.name
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
WHERE rowmodctr > 0
ORDER BY i.rowmodctr DESC
That there is a table with a number of _WA_Sys_ statistics that are way out of date (and have a rowmodctr that is higher than 20% + 500)
If a run a query against the table and add one of the columns associated with the out of date _WA_Sys_ statistics in the WHERE clause and check the updated date of the statistic, I can see it has updated.
If i run the same query with the WHERE clause again, the statistic doesn't update
It seems like the _WA_Sys statistics update when a query is run that will use them and they are out of date?
sql-server-2014 statistics cardinality-estimates
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
add a comment |
I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table)
The rule of thumb appears to be in tables >500 rows that statistics are updated at a 20% + 500 row change of data.
However, I can see using the following query
SELECT t.name,
i.name,
i.rowcnt,
i.rowmodctr,
p.last_updated
FROM sys.sysindexes i
JOIN sys.tables t
ON i.id = t.object_id
JOIN sys.stats s
ON s.object_id = t.object_id AND i.name = s.name
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
WHERE rowmodctr > 0
ORDER BY i.rowmodctr DESC
That there is a table with a number of _WA_Sys_ statistics that are way out of date (and have a rowmodctr that is higher than 20% + 500)
If a run a query against the table and add one of the columns associated with the out of date _WA_Sys_ statistics in the WHERE clause and check the updated date of the statistic, I can see it has updated.
If i run the same query with the WHERE clause again, the statistic doesn't update
It seems like the _WA_Sys statistics update when a query is run that will use them and they are out of date?
sql-server-2014 statistics cardinality-estimates
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table)
The rule of thumb appears to be in tables >500 rows that statistics are updated at a 20% + 500 row change of data.
However, I can see using the following query
SELECT t.name,
i.name,
i.rowcnt,
i.rowmodctr,
p.last_updated
FROM sys.sysindexes i
JOIN sys.tables t
ON i.id = t.object_id
JOIN sys.stats s
ON s.object_id = t.object_id AND i.name = s.name
CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
WHERE rowmodctr > 0
ORDER BY i.rowmodctr DESC
That there is a table with a number of _WA_Sys_ statistics that are way out of date (and have a rowmodctr that is higher than 20% + 500)
If a run a query against the table and add one of the columns associated with the out of date _WA_Sys_ statistics in the WHERE clause and check the updated date of the statistic, I can see it has updated.
If i run the same query with the WHERE clause again, the statistic doesn't update
It seems like the _WA_Sys statistics update when a query is run that will use them and they are out of date?
sql-server-2014 statistics cardinality-estimates
sql-server-2014 statistics cardinality-estimates
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
asked Mar 6 at 15:04
SEarle1986SEarle1986
527417
527417
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision.
To see which statistics are being used for your query, add this to the end of your query, and look in the Messages tab:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);
If your database is in a compat level prior to 2014, you'll need these:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);
These won't tell you why it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.
Keep in mind that statistics updates don't occur on modification, only when queries run that use them
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231466%2fwhen-do-wa-sys-statistics-get-updated%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision.
To see which statistics are being used for your query, add this to the end of your query, and look in the Messages tab:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);
If your database is in a compat level prior to 2014, you'll need these:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);
These won't tell you why it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.
Keep in mind that statistics updates don't occur on modification, only when queries run that use them
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
add a comment |
SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision.
To see which statistics are being used for your query, add this to the end of your query, and look in the Messages tab:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);
If your database is in a compat level prior to 2014, you'll need these:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);
These won't tell you why it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.
Keep in mind that statistics updates don't occur on modification, only when queries run that use them
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
add a comment |
SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision.
To see which statistics are being used for your query, add this to the end of your query, and look in the Messages tab:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);
If your database is in a compat level prior to 2014, you'll need these:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);
These won't tell you why it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.
Keep in mind that statistics updates don't occur on modification, only when queries run that use them
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision.
To see which statistics are being used for your query, add this to the end of your query, and look in the Messages tab:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);
If your database is in a compat level prior to 2014, you'll need these:
OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);
These won't tell you why it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.
Keep in mind that statistics updates don't occur on modification, only when queries run that use them
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
edited Mar 6 at 15:38
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
answered Mar 6 at 15:25
Erik DarlingErik Darling
22.8k1269113
22.8k1269113
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231466%2fwhen-do-wa-sys-statistics-get-updated%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
