Skip to main content
Share Your Experience: Take the 2024 Developer Survey
28 votes
Accepted

Cardinality Estimate for LIKE operator (Local Variables)

The guess for LIKE in your case is based on: G: The standard 9% guess (sqllang!x_Selectivity_Like) M: A factor of 6 (magic number) D: Average data length in bytes (from statistics), rounded down to ...
Paul White's user avatar
  • 85.7k
28 votes
Accepted

Strange behaviour with sample sizes for statistics updates

Background Data for the statistics object are gathered using a statement of the form: SELECT StatMan([SC0], [SC1], [SB0000]) FROM ( SELECT TOP 100 PERCENT [SC0], [SC1], ...
Paul White's user avatar
  • 85.7k
24 votes

Warning for missing statistics in execution plan

The warning does not always correspond to missing single-column statistics. There is no easy way to precisely determine the exact statistic the optimizer went looking for and did not find in all ...
Paul White's user avatar
  • 85.7k
23 votes
Accepted

What is the default sample size of statistics in SQL Server?

How to identify default sample size of statistics? I would quote from Old Docs article Auto Update stats Algorithm: So the Auto Update stats will fire for every 500 + 20% change in table rows. Of ...
Shanky's user avatar
  • 19k
17 votes
Accepted

How are the number of Histogram steps decided in Statistics

I'm going to limit this post to discussing single column statistics because it'll already be pretty lengthy and you're interested in how SQL Server buckets the data into histogram steps. For multi ...
Joe Obbish's user avatar
  • 32.2k
17 votes
Accepted

SQL Server 2016 Bad Query Plan locks up DB once a week

I'm going to answer your questions in a different order than you asked them. 4. Is this a symptom of a larger problem? The new cardinality estimator in SQL Server 2016 could be contributing to the ...
Joe Obbish's user avatar
  • 32.2k
16 votes

Cardinality Estimate for LIKE operator (Local Variables)

I tested on SQL Server 2014 with the legacy CE and did not get 9% as a cardinality estimate either. I couldn't find anything accurate online so I did some testing and I found a model that fits all of ...
Joe Obbish's user avatar
  • 32.2k
16 votes
Accepted

Unique index updates and statistics row modification counters

SQL Server always uses the Split, Sort, and Collapse combination of operators when maintaining a unique index as part of an update that affects (or might affect) more than one row. Working through the ...
Paul White's user avatar
  • 85.7k
16 votes

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart? Query plans?...
Aaron Bertrand's user avatar
14 votes

Why set Auto Update Statistics to False?

This is too long for a comment so I'll chime in with another case where one might want to turn off auto update stats. I've worked with databases supporting high-volume OLTP workloads and a stringent ...
Dan Guzman's user avatar
  • 28.3k
14 votes
Accepted

Inability to query underlying table of a schema-bound view

From the documentation: A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns ...
Paul White's user avatar
  • 85.7k
13 votes
Accepted

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

Yes it does. Since SQL Server is in charge of maintaining those caches, when you shut down SQL server's services, it releases the memory back to the operating system.
Laughing Vergil's user avatar
13 votes
Accepted

Cardinality estimate outside the histogram

Based on my testing, the out-of-bounds cardinality estimate is simply the square root of the row count, bounded below by the number of added rows since the last stats update, and bounded above by the ...
Forrest's user avatar
  • 4,169
12 votes
Accepted

SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

Should SQL Server not identify the highest value for that key and use that as the maximum RANGE_HI_KEY? Or is this just one of the limits of update without using FULLSCAN? It's a limitation of the ...
Paul White's user avatar
  • 85.7k
12 votes
Accepted

Find which functions are used

Yes, use the statistics collector. The manual: PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. [...] It can also ...
Erwin Brandstetter's user avatar
12 votes
Accepted

Bad execution plan after stats update due to temp table

The bad plan has a warning on the temp table that the join column has no stats. What gives? There may be a more esoteric reason for this, but it is more likely a simple statistics creation failure. ...
Paul White's user avatar
  • 85.7k
12 votes
Accepted

Analyze needed after a refresh materialized view?

Table statistics are not updated automatically. They remain unchanged if refreshed with the CONCURRENTLY option. Without CONCURRENTLY, a new file is written for the table, and the basic counts ...
Erwin Brandstetter's user avatar
12 votes
Accepted

Why are statistics slower to build on clustered columnstore?

Sampled statistics on a heap or b-tree use TABLESAMPLE SYSTEM. This algorithm employs an allocation-ordered scan and chooses pages from the scan to sample. All rows on the selected pages contribute to ...
Paul White's user avatar
  • 85.7k
11 votes
Accepted

Auto Update Stats not updating Statistics

The new calculation is only used if trace flag 2371 is enabled, except on SQL Server 2016 when the context database compatibility level is set to 130, where it is the default behaviour. See Microsoft ...
Paul White's user avatar
  • 85.7k
11 votes

Strange behaviour with sample sizes for statistics updates

This is an excellent question! I'll start with what I know for sure and then move onto speculation. Lots of details about this in my blog post here. Sampled stats updates use TABLESAMPLE behind the ...
Joe Obbish's user avatar
  • 32.2k
11 votes
Accepted

Why does SQL Server not do compound column statistics histograms?

Background The current SQL Server model only uses single-column histograms and multi-column density information. Single column histograms are used to estimate selectivity for suitable predicates e.g. ...
Paul White's user avatar
  • 85.7k
10 votes

Why set Auto Update Statistics to False?

Generally I would say that having auto update statistics on is beneficial. But like any setting, there are reasons you can turn it on or off. One is that some tables have a lot of churn, and perhaps ...
Aaron Bertrand's user avatar
10 votes
Accepted

SELECT very slow when no results and a LIMIT is specified

Postgres does a bad job estimating the frequency of the combination of predicates in your query: SELECT * FROM tbl WHERE related_id = 1 AND platform = 'p2'::platforms LIMIT 20; Each of your ...
Erwin Brandstetter's user avatar
10 votes
Accepted

Is it worth to optimize queries with EarlyTermination Timeout?

I wonder if this imbalance of the number of rows maybe a result of the Timeout Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent ...
Paul White's user avatar
  • 85.7k
10 votes
Accepted

Retain column statistics on paragraph-style columns?

First of all, and perhaps most importantly, albatrosses are not unlucky. They are in fact considered a good omen. In the poem, the young sailor shoots it with his crossbow, and it is this reckless ...
wBob's user avatar
  • 10.4k
10 votes
Accepted

Updating statistics on 18 TB database

Based on your question I can think of four possible issues with statistics that could be causing the issues that you're experiencing. 1. Statistics are not automatically updated often enough. In SQL ...
Joe Obbish's user avatar
  • 32.2k
10 votes
Accepted

Why is the auto created statistic on this column empty?

I was able to reproduce this, both with an empty statistic, and a populated statistic. I arranged for an automatic statistic to be created on an empty table, and the index was created later: IF ...
Paul White's user avatar
  • 85.7k
10 votes
Accepted

When do _WA_Sys_ statistics Get Updated?

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 ...
Erik Darling's user avatar
10 votes
Accepted

Query execution plan is horrible until Statistics are Updated

You could use a manual plan guide to enforce the desired plan. Alternatively, you could use Query Store to enforce plan guides via the GUI. Also, you could run an update statistics job after the ...
Hannah Vernon's user avatar
  • 70.1k
9 votes

Does a re-index update statistics?

The Microsoft Docs page for SQL Server statistics states: Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need ...
Andrii Viazovskyi's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible