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 ...
- 69.2k
26
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], ...
- 69.2k
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 ...
- 69.2k
21
votes
When is it better to create STATISTICS instead of creating an Index?
You question revolves around - When is it a good thing to just create statistics vs create index (which create stats).
From my sql server internals notes (SQLSkills class- IE1 and IE2) and SQL Server ...
- 61.1k
21
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 ...
- 18.3k
18
votes
Accepted
Statistics disappear after incremental update
Not sure if it's a bug, per se but it's definitely an interesting occurrence. Online partition rebuilds are new in SQL Server 2014 so there may be some internals to sort through with this.
Here's my ...
- 10.4k
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 ...
- 30.3k
16
votes
Accepted
Statistics. Are multicolumn histograms possible?
Are multicolumn histograms possible?
Not true multi-dimensional histograms, no.
Is this artificial and inelegant solution the only available option to achieve accurate estimations when dealing ...
- 69.2k
16
votes
Accepted
Get IO stats request sql like activity monitor
You can use a query like this to calculate read/write rates and latency (though due to my laziness these figures are not in units that match Activity Monitor).
SELECT d.name, f.name, f.type_desc, f....
- 176k
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 ...
- 69.2k
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?...
- 176k
15
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 ...
- 30.3k
15
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 ...
- 30.3k
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.
- 1,988
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 ...
- 4,010
13
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 ...
- 69.2k
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 ...
- 69.2k
12
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 ...
- 24.2k
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. ...
- 69.2k
11
votes
When is it better to create STATISTICS instead of creating an Index?
I'd say you need an index when you need to be able limit the amount of data / get to the correct data quickly based on the field(s).
You need statistics when you need the optimizer to understand the ...
- 2,199
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 ...
- 69.2k
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 ...
- 30.3k
11
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 ...
- 151k
10
votes
Accepted
statistics are up to date, but estimate is incorrect
There is a simple solution to this:
Drop all of the _dta_... statistics and stop blindly applying DTA recommendations.
More information
The particular problem was that there were multiple sets of ...
- 176k
10
votes
Accepted
Why does SQL Server refuse to update these statistics with anything but fullscan?
The first thing I would try is updating the SQL Server instance from the SP1 CU16 with QFE that you have right now, to SP3 CU1 (current 2012 build) then retesting to see if the behaviour is the same.
...
- 69.2k
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 ...
- 151k
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 ...
- 69.2k
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 ...
- 10.2k
10
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 ...
- 151k
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 ...
- 69.2k
Only top scored, non community-wiki answers of a minimum length are eligible