2022 Developer Survey is open! Take 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 ...
user avatar
  • 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], ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 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?...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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.
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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. ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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. ...
user avatar
  • 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 ...
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 69.2k

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