Questions tagged [statistics]

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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
38 views

Outliers produce a bad query plan

I have table document with ~2.5 millions rows and more than 100 columns. I provide only used in query columns: CREATE TABLE document ( id serial PRIMARY KEY, organizationid INTEGER, status_1 ...
Bunny Boss's user avatar
1 vote
1 answer
37 views

Wrong cardinality estimation after gathering statistics ORACLE

We have a large table range partitioned by month. Incremental statistics turned on. After scheduled statistics gathering cardinality estimation become weird, like select count(*) from my_table where ...
Andy DB Analyst's user avatar
0 votes
2 answers
108 views

Use extended events to track autoupdate statistics on a specific table

I'm trying to find a way to track a table that has auto update statistics on, I'm trying to track how often it auto updates and at what intervals. The table in question is used quite often and because ...
user avatar
0 votes
0 answers
17 views

Mysql system tables showing incorrect cardinality for columns

I’m new to mysql. I have encountered a weird problem on our prod database. We have a table with primary key a and an index on some other b column. Below’s the query: select b from my_table where a ...
shiwanginio's user avatar
1 vote
1 answer
103 views

Performance issue after migration to SQL Server 2019

I am troubleshooting a performance issue on a job that is taking 5h longer to complete on SQL Server 2019 compared to when it ran on 2012. One of the problematic steps of the job is during a batch ...
Andre's user avatar
  • 13
1 vote
1 answer
621 views

Do statistics have an impact on delete performance?

Background: I am a developer working on an application that has a relatively large table (in my experience at least) of 1.7 billion records. The table is a history table, and the application imports ...
ldam's user avatar
  • 113
0 votes
1 answer
132 views

Why does a SELECT query (Sch-S) get blocked due to an existing Sch-S lock held by index reorganize command?

SELECT query requires a SCH-S lock. This can be shared with other read queries. UPDATE query requires a SCH-M lock. This cannot be shared with other read/update queries. Link: https://www.mssqltips....
variable's user avatar
  • 3,440
1 vote
1 answer
63 views

Should statistics be updated manually?

I'm currently considering using Ola Hallengren's Index Maintenance solution to update statistics once a day. What is the benefit of this solution if a database has AUTO_UPDATE_STATISTICS set to on? I'...
Marcus's user avatar
  • 47
0 votes
2 answers
149 views

Improving stats for a join to avoid Nested Loops with LIMIT

I have a main fact table t1 with ~6.9m rows, a second dimension table t2 with 1329 rows, and a third table t3 with ~6.9m rows (1-1 with t1, separate because it's produced by a different batch process)....
MZS's user avatar
  • 103
0 votes
0 answers
55 views

DBA_TAB_MODIFICATIONS out of whack in Oracle when moving partition or subpartition segments

I've observed that when moving a partition or subpartition of a table (ALTER TABLE xyz MOVE PARTITION abc), the row count of the moved segment gets reflected in DBA_TAB_MODIFICATIONS at higher levels (...
Paul W's user avatar
  • 520
3 votes
1 answer
163 views

How is a statistics collection different to normal table stats?

I have a small table with few rows create table dbo.p(i int); insert dbo.p(i) values (1), (2), (3), (4), (4); I created statistics; no index, just the stats. create statistics p_c on dbo.p(i) with ...
Michael Green's user avatar
7 votes
1 answer
155 views

SQL Server Primary Key Column Statistics Histogram Suggests Duplicate Values

I have a statistic on a Primary Key column in a table. When I update the statistic with the default options: UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350 I get a histogram as follows (...
SE1986's user avatar
  • 1,928
0 votes
0 answers
209 views

When to use sp_updatestats and when to use sp_updatestats with the resample option?

I am confused about the usage of RESAMPLE option and when to use it. We had problems with a customer DB. One/some queries took longer as expected and the CPU usage on the machine was higher than in ...
xMRi's user avatar
  • 230
3 votes
0 answers
216 views

Possible missing step in sampled statistics generation?

I've searched far and wide for information on why sampled statistics are less accurate, and broadly speaking every question has always been explained with the following generalizations: sampled are ...
Chris's user avatar
  • 39
3 votes
1 answer
1k views

Update statistics on a 4 billion record table

On a SQL Server 2016 SP3 database, we have several (under 10) tables with billions of records. It's a 24/7 system with limited maintenance windows so it's essential that user impact is limited while ...
Rolann's user avatar
  • 41
-1 votes
1 answer
97 views

DB2 Cardinality

In my tables, I have many statistics with -1 and I want to know how can I enforce DB2 to calculate these values. Select FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD ...
DB2fan's user avatar
  • 1
2 votes
2 answers
856 views

What exactly is "only a read lock on the target table"

I'm trying to find information on whether it's safe to run ANALYZE on a large table that has stale statistics. The table is accessed in an "append-only" fashion and is constantly written to ...
Boris B.'s user avatar
  • 133
0 votes
2 answers
109 views

IndexOptimize procedure doesn't obey @MaxNumberOfPages when only updating statistics

I'd like to use EXECUTE [master].dbo.IndexOptimize @Databases = 'mydatabase', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @...
Endrju's user avatar
  • 126
0 votes
1 answer
115 views

Operational stats mention the same table twice, what does this mean?

While investigating why my application mentions deadlocking, I fell upon this question, whose answer mentions an SQL query, showing operational logs. However, the results of that query contain the ...
Dominique's user avatar
  • 523
0 votes
3 answers
583 views

Running sp_updatestats probably overrides manually done UPDATE STATISTICS WITH FULLSCAN and ruins performance

We experience this on both SQL Server 2014 and SQL Server 2019. We have an application, which loads some data into our database. At the end of this load process, the application runs sp_updatestats ...
SQL_Guy's user avatar
  • 645
8 votes
1 answer
462 views

Why are statistics slower to build on clustered columnstore?

Building statistics on a clustered columnstore table always seems to read the whole table, even if I ask for a small sample. Why is this?
Paul White's user avatar
  • 83k
-1 votes
1 answer
112 views

queries with significant difference of estimated and actual number of rows - skew data - in the query plan

I am using dbcc show_statistics looking for skew data in my histogram and improving the quality of my statistics. OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector. If ...
Marcello Miorelli's user avatar
1 vote
2 answers
232 views

How can I make queries on skewed datasets more performant on Postgres

I have a seemingly simple query that's unfortunately extremely slow. I believe that I know why it is slow, but not how to make it fast, so I'd like to see how this could be improved. let's call the ...
Alessandro Jeanteur's user avatar
0 votes
0 answers
47 views

How to optimize execution plan for lopsided table

I've got a table with an index that's pretty lopsided, where a small number of values has a much higher count of rows than most of them. In the table there are a few hundred million rows - my index is ...
Joe Enos's user avatar
  • 137
1 vote
0 answers
185 views

Postgres UUID correlation workaround

I have a table with a UUIDv4 field owner. The table has 10s of millions of rows, but only a few thousand unique owners. Owners typically create/update their rows in one burst, so their tuples are ...
Felipe's user avatar
  • 283
-3 votes
1 answer
530 views

Why do indexes, statistics, and full text need to be rebuilt/updated/repopulated after migration?

I have created a new server, restored the system dbs and presented a copy of the data/log disks from the previous machine (or alternatively maybe I restore the user dbs using the .bak files). Post SQL ...
variable's user avatar
  • 3,440
0 votes
2 answers
150 views

Bad PostgreSQL planner decisions on column with uneven distribution

Facts I have following simple table that represents financial transactions: CREATE TABLE transaction ( "user_id" text not null, "offset" serial not null, "tx_data&...
jdziwbc's user avatar
0 votes
1 answer
165 views

Diagnose the exact cause of WAIT_ON_SYNC_STATISTICS_REFRESH

We are currently facing query timeout issues with our online API . The related request is an ad hoc select query referencing two tables. In Redgate, we see that each time the request is blocked, it's ...
Nicolas Gicquel's user avatar
4 votes
1 answer
237 views

How does SQL estimate the number of rows in a less than < Predicate

I have been doing some testing to try to better understand how SQL Server uses a histogram to estimate the number of rows that will match an equality predicate and also a < or > predicate Given ...
SE1986's user avatar
  • 1,928
3 votes
1 answer
174 views

Why Does SQL Server not have 200 buckets in the statistics histogram when there are >100k distinct values in the table

Given I am using the AdventureWorks2016 OLTP database why does the statistics histogram for the index PK_TransactionHistory_TransactionID on table Production.TransactionHistory only contain 3 ...
SE1986's user avatar
  • 1,928
-4 votes
1 answer
119 views

Is the effect of statistics update immediate or does sql server need to be restarted?

Index statistics are utilized by sql server to derive the best plan for query execution. Suppose I update the index statistics then can existing queries use the effect of updated statistics or does ...
variable's user avatar
  • 3,440
1 vote
1 answer
387 views

Mysql.innodb_table_stats & mysql.innodb_index_stats not updating

I believe I have persitent stats configured correctly and there are records in INFORMATION_SCHEMA.STATISTICS table but the following tables have no data for many of the tables/indexes. mysql....
8adger's user avatar
  • 51
1 vote
1 answer
3k views

SQL Update statistics with Fullscan and normal update without fullscan

What is the difference between SQL Server Update statistics with Fullscan and without Fullscan. Is the Fullscan will improve the performance better that without fullscan option. How can we get the see ...
user avatar
0 votes
3 answers
237 views

Do I need statistics in addition to an index?

Suppose I have a table with images that are supposed to go through several steps: CREATE TABLE images (filename text, extracted bool, cropped bool, resized bool); INSERT INTO images (filename, ...
AndreKR's user avatar
  • 557
0 votes
1 answer
493 views

statistics on what queries were executed and how many times

I'd like to monitor how many times certain queries are executed. Take a look at the 3 queries below, the second and third are written as prepared statement. SELECT COUNT(*) FROM TABLE1 UPDATE TABLE2 ...
sanjihan's user avatar
  • 253
3 votes
2 answers
2k views

Dealing with suboptimal postgres query plans related to nested loops

I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13. Some of the queries are very slow because they use nested-loops but the query planner ...
valenterry's user avatar
7 votes
1 answer
1k views

Why is statistics update slower on a clustered columnstore than on a rowstore?

I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that ...
Andrej Kuklin's user avatar
0 votes
1 answer
236 views

What do these weird errors logged mean?

Today, I got these errors logged for the first time: stats_timestamp 2022-01-16 10:58:25.625142+01 is later than collector's time 2022-01-16 10:58:23.356175+01 for database 0 statistics collector's ...
Klappoklang's user avatar
0 votes
1 answer
331 views

Order of execution in Ola Index maintenance and Stats collection

I was thinking of performing The index maintenance and stats collection jobs in the below order for all databases in an Instance. step 1.Perform all index maintenance activity for all required Indexes(...
abi ram's user avatar
0 votes
2 answers
196 views

Postgres 12 - under which cirumstances should I activate the query and index statistics collector

TL;DR - I don't have the collector activated (yet) and it seems to not be the default; how bad is this and should I activate it? I've just created a new index on a table where a query was slow. I've ...
Sebastian Riemer's user avatar
0 votes
1 answer
164 views

Does the Query Optimizer use Incremental Statistics yet?

I was considering using incremental statistics in my data warehouse but I read an article by Erin Stellato that says the query optimizer doesn't use incremental statistics. The article was written in ...
Mike Lemay's user avatar
1 vote
1 answer
360 views

Postgres creating 'histogram_bounds' statistics on JSONB column

I have two database tables (A and B), each containing roughly 1.6m rows, and each having the following structure: CREATE TABLE tablename ( identifier text NOT NULL, geometry geometry(GeometryZ,...
JoeTea's user avatar
  • 11
0 votes
2 answers
74 views

Data structure and design for statistics and reporting [closed]

I hope I will get luck describing what I am looking for, because This is some thing new for me and I am not sure if I will use the correct names and terms. I have tables which contains a million of ...
Thudner's user avatar
  • 101
-1 votes
1 answer
75 views

Why is the estimate (out of Stream Aggregate) so low?

I have a table like this: create TABLE dbo.MyTable ( TMyTableID bigint NOT NULL IDENTITY(1, 1), LogArgumentID bigint NOT null, LogID bigint NOT NULL, LogTextID int NOT NULL, FloatValue float NULL, ...
Henrik Staun Poulsen's user avatar
0 votes
1 answer
150 views

Oracle gather stats autotask being interrupted before maintenance window ends

a few months ago I managed to enlarge the maintenance windows for autotask on a pluggable database, this way: BEGIN dbms_scheduler.disable( name => 'WINDOW_NAME'); dbms_scheduler....
trustno1's user avatar
1 vote
0 answers
57 views

What is the best way to have actual execution plan and IO stats as values to insert?

We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ([dbo].[TestHistory]. We ...
grochu's user avatar
  • 45
0 votes
1 answer
629 views

Wait state: Statistics

We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics. According ...
Lionel Enkaoua's user avatar
0 votes
2 answers
662 views

When to analyze columns?

I have a table with millions of records. Before select, I run Analyze table (col1, col2). We want to reduce cost, since a lot of IO operations happen in AWS Aurora. Should I run analyze only when we ...
Venkat's user avatar
  • 1
-1 votes
1 answer
88 views

ALTER INDEX ALL did not fix the problem, but deleting and recreating a non-clustered index does the trick. Why?

My SQL Server version is 2014. The table has over 21 million rows. One of its indexes is a non-clustered index on an integer field that is referenced a lot. I did a mass update that affected about 20%...
user79284's user avatar
3 votes
1 answer
1k views

Autotask 'auto optimizer stats collection' and scheduler jobs logging

We have a DB hosting Peoplesoft schema, with more than 33.000 tables. Since december Oracle has left more than 18 thousand of them with no statistics renewed: SQL> select to_char(last_analyzed,'...
Seb's user avatar
  • 31

1
2 3 4 5
8