52
You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez)
By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.
By default, the UPDATE STATISTICS statement updates both ...
30
Found them.
Create a table with a simple stats object.
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE TABLE dbo.foo(bar INT, munge INT);
GO
CREATE STATISTICS x ON dbo.foo(bar);
CREATE STATISTICS y ON dbo.foo(munge);
GO
INSERT dbo.foo SELECT s1.[object_id], s2.[object_id]
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2;
GO
UPDATE STATISTICS dbo....
28
If you don't have the maintenance window for it, updating statistics daily is probably a little overkill. Especially if you have Auto Update Statistics turned on for the database. In your original post, you said that users are seeing a performance degredation due to this maintenance plan. Is there no other time to run this maintenance plan? No other ...
28
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 integer
Specifically, sqllang!CCardUtilSQL7::ProbLikeGuess uses:
Selectivity (S) = G / M * LOG(D)
Notes:
The LOG(D) term is omitted if D is between 1 and 2....
answered Oct 28 '16 at 16:50
26
When To Update Statistics?
if and only if auto update statistics feature is not good enough for your requirements.
i mean if auto create and auto update statistics are ON and you are getting a bad query plan because the statistics are not accurate or current then it might be a good idea to have control over statistics creation and update.
but if you are ...
26
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], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
FROM
(
SELECT
[TextValue] AS [SC0],
[Id] AS [SC1]
FROM [dbo].[...
answered Jul 25 '17 at 17:03
22
Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005":
Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form:
SELECT
StatMan([SC0])
FROM
(
...
answered Mar 10 '13 at 1:53
22
I'm afraid your script is littered with misconceptions about how databases work, at least in SQL Server.
First, you shrink the database as much as possible, using the generic DBCC SHRINKDATABASE command. Why? If the database is going to grow again, what have you gained by shrinking the file? Are you going to temporarily lease out that space in the meantime, ...
21
Sure, if your data is changing more frequently than the rate of auto stats (or, say, you are updating < 20% of the rows frequently, like updating statuses or date/time stamps). Or if your table is huge and it is not changing enough to trigger auto stats updates. Or if you have filtered indexes (since the auto stats threshold is still based on the % of ...
20
Consider the simple AdventureWorks query and execution plan shown below. The query contains predicates connected with AND. The optimizer's cardinality estimate is 41,211 rows:
-- Estimate 41,211 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
TH.TransactionID BETWEEN 100000 AND 168336
AND TH.TransactionDate BETWEEN '2007-09-...
answered Jul 30 '13 at 8:08
20
How to identify default sample size of statistics?
I would quote from Blogs.msdn article
Auto Update stats Algorithm:
So the Auto Update stats will fire for every 500 + 20% change in table
rows. Of course, we have an improved algorithm in SQL 2012 which is
SQRT(1000 * Table rows) which is much better.
When it fires it will use the ...
19
Indexes store actual data (data pages or index pages depending on the type of index we are talking about), and Statistics store data distribution. Therefore, CREATE INDEX will be the DDL to create an index (clustered, nonclustered, etc.) and CREATE STATISTICS is the DDL to create the statistics on columns within the table.
I recommend you read about these ...
19
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 internals book, below is my limited understanding :
SQL Server statistics are nothing but system objects that contain vital information about the index key ...
17
You can look in the plan cache to get a pretty good idea of Stored Procedure usage. Take this query, for instance:
select
db_name(st.dbid) as database_name,
object_name(st.objectid) as name,
p.size_in_bytes / 1024 as size_in_kb,
p.usecounts,
st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where ...
17
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 best explanation for you. Incremental statistics absolutely require that all partitions be sampled at the same rate so that when the engine merges the stats ...
16
For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.
Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted
SELECT StatMan([SC0], [SB0000])
FROM (SELECT ...
16
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 problem. SQL Server 2012 uses the legacy CE and you didn't experience your problem on that version. The new cardinality estimator makes different assumptions ...
15
The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool.
As for the question: statistics are in the database so a backup/restore will preserve the statistics.
Note though that ...
15
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 with filtering by two or more not independent columns?
SQL Server does support "multi-column" statistics, but they only capture average density (correlation) ...
answered Dec 9 '14 at 22:36
15
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.physical_name,
[read b/ms] = num_of_bytes_read * 1.0/sample_ms,
[avg read latency] =
(1.0*s.io_stall_read_ms / (COALESCE(NULLIF(s.num_of_reads,0),1))),
[...
15
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 cases, but it is almost always a multi-column statistic that would provide some correlation information across multiple equality predicates in the query.
Perhaps ...
answered Apr 12 '16 at 7:41
15
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 the test cases that I tried, but I can't be sure that it's complete.
In the model that I found, the estimate is derived from the number of rows in the table, ...
15
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 example in the question, we could write the update as a separate single-row update for each of the four rows present:
-- Per row updates
UPDATE dbo.Banana ...
answered Sep 10 '17 at 20:12
15
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? Yes.
Table/index data? Yes.
Statistics? No.
(Query plans, which use statistics, will have to be recompiled, but statistics won't have to get re-created ...
13
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 average rows per value.
In your case, 1,934.99 = SQRT(3744192)
Testing setup below:
--setup
USE TestDB
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS OFF
...
12
I have come up with the following:
select cume, max(var) AS max_var
from (
select var
, ntile(5) over (order by var) as cume
from table
) as tmp
group by cume
order by cume;
It selects the maximum of each group that is divided using ntile().
12
This is a big "It Depends..." but generally speaking you don't want to have statistics updating asynchronously for most workloads because it can mean that you generate a less-than ideal execution plan based on the existing out-of-date statistics.
If you have a scenario where the auto update stats execution takes an excessive amount of time and causes ...
12
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 column statistics the histogram is only created on the leading column.
When SQL Server determines that a statistics update is needed it kicks off a hidden query ...
12
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. This might, for example, occur when the task fails to get the memory resources it needs, or when statistics creation is being throttled (too many concurrent ...
answered Oct 25 '17 at 1:06
12
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.
Only top voted, non community-wiki answers of a minimum length are eligible
