Hot answers tagged statistics
15
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 ...
14
Statistics simply are a form of dynamic metadata that assists the query optimizer in making better decisions. For example, if there are only a dozen rows in a table, then there's no point going to an index to do a lookup; you will always be better off doing a full table scan. But if that same table grows to a million rows, then you will probably be better ...
13
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 ...
11
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 ...
11
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 ...
9
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 ...
8
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 ...
8
Addition to what Remus has mentioned, I would suggest you read --
SQL Server Statistics Questions We Were Too Shy to Ask
Aarons answer to - Where are Statistics physically stored in SQL Server?
UNDERSTANDING SQL SERVER STATISTICS
7
Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column.
Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they ...
7
Between sys.stats and DBCC SHOW_STATISTICS you have all the info needed to see the statistics of your statistics. Eg.:
set nocount on;
declare @object nvarchar(256), @stat sysname;
declare crs cursor forward_only read_only static for
select quotename(object_schema_name(object_id)) +
'.' +quotename(object_name(object_id)),
name
from sys.stats;
...
6
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().
6
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 ...
5
statistics is used when query execution plan created.
Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality ...
5
They are used by the query optimiser (whitepaper on MSDN) to track distribution of values in indexes and/or columns.
Your only concern should be to update regularly: just leave the DB engine to do its stuff
5
Its the sub selects in your column selection that is causing the slow return. You should try using your sub-selects in left joins, or use a derived table as I have defined below.
Using Left Joins to two instances of Third Table
SELECT
TempTable.Col1,
TempTable.Col2,
TempTable.Col3,
JoinedTable.Col1,
JoinedTable.Col2,
ThirdTable.Col1 AS ...
5
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 ...
5
Your select on dba_tables doesn't take into account:
Empty blocks in the table (see initial/next, minextents and freelist-related storage parameters among others)
Empty space in the blocks (due to pct_free mainly)
Block headers (initrans influences this size, among others)
I.e. it doesn't take into account the physical storage of the data at all. ...
4
If an estimate is good enough, then statistical sampling is your friend. I'd probably use a sample size calculator to determine how many rows I need, then write some code to randomly insert that many keys into a table. A join, a function, and you're done.
If you've never done anything like this before, you'll probably want to do some background reading. ...
4
The answer is close to your "stats updates automatically cause dependent query plans to be flushed". They don't "stick around"
Flushing a plan from cache is determined by memory pressure.
Statistics updates cause plan recompilations
See MSDN, "Execution Plan Caching and Reuse"
Now, it's unclear what your problem is, but do you have parameter sniffing ...
4
I don't know what CREATE STATISTICS does, but statistics for the optimizer are collected using the ANALZYE command when autovacuum is running - which is turned on by default.
Statistics are always collected for all columns, no need to turn it on specifically.
You can control the level of details collected for the statistics on a per-column basis using ...
4
Yes, remember that auto stats are always generated with a default sampling rate.
That default sampling rate may not accurately produce statistics that represent your data.
http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx
4
It's because when you rebuild indexes, you also necessarily rebuild the statistics on each index as it happens. That means that SQL Server rebuilds internal "tables" of data about the distribution of data in your indexes themselves. The distribution of data indicated by your statistics govern how the cost-based query optimizer chooses to create plans for ...
3
A couple more (superficial) reasons:
Auto-update stats will block the query that triggered the update until the new statistics are ready.
...Unless you also enable auto-update stats asynchronously. Then the query that triggered the update won't wait for the new stats, but will potentially run with the old, incorrect stats.
I also ran into some strange ...
3
That was actually my question at AskSSC. I should have just tested it myself as I accepted an incorrect answer.
With the following test table
CREATE TABLE StatsTest
(
a varchar(max),
b varchar(max)
)
DECLARE @VCM VARCHAR(MAX) = 'A'
INSERT INTO StatsTest
SELECT TOP 20000
REPLICATE(@VCM,10000),
REPLICATE(@VCM,10000)
FROM master..spt_values ...
3
To answer your questions in order:
The cube doesn't store medians, modes (or even averages), but you can write queries that calculate them and embed them as calculated measures in the cube. The ability to embed this sort of computation is one of the main unique selling points of OLAP technology.
If you have a dimension that can identify individual rows ...
3
If DTA recommended a single column statistic, you must have auto create stats turned off? Auto create will build stats on any column used in a predicate, automatically, so generally its a good idea to leave it switched on. Check the status of auto update for this database, which again should usually be on.
One of the best uses of DTA is for identifying ...
3
Create another table and log the inserts, updates, and deletes using DML triggers.
So lets say you have a Table A that you want to track.
Create another table, say, tbl_row_stats.
Create DML triggers on Table A for inserts, updates, and deletes. I.e. whenever any of these three actions happen to Table A, insert a row in tbl_row_stats with the ...
3
If you want that kind of granular statistics per id, it sounds like you may want to try something a little convoluted to collect such info. Let's explore this scenario:
If you have a table with the following layout in the wp database:
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL ...
Only top voted, non community-wiki answers of a minimum length are eligible
