Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
3 votes
1 answer
38 views

Sampled Statistics Percentage Internal Calculation

When SQL Server builds sampled statistics, how is the sampling percentage calculated? For example, updating statistics on the PostId column of the dbo.Votes table in the Stack Overflow 2013 database: ...
Paul White's user avatar
  • 85.6k
2 votes
1 answer
92 views

Could not locate statistics in the system catalogs error in SQL Server

I've been trying to access a table on our reporting server (secondary replica) from our warehouse server via a linked server. Everything was working smoothly until last week, but now it's throwing a &...
lifeisajourney's user avatar
1 vote
1 answer
21 views

PostgreSQL - Sum all row that satisfy a condition into a single row

I'm trying to extract some statistics from a Postgres database and I made this query: SELECT city, job_count, TO_CHAR(job_count * 100 / SUM(job_count) OVER (), '90D00%') AS job_share FROM ( ...
Mateja Maric's user avatar
0 votes
1 answer
31 views

Get the statistics_stream on SQL server using SSMS

I have generated statistics for a few columns of a table using the following SQL command: CREATE STATISTICS [STAT_NAME] on [TABLE_NAME] (COLUMN_NAME) WITH FULLSCAN The generated statistics are ...
Mohammad Mosiur's user avatar
0 votes
0 answers
27 views

Statistics update - worthwhile on a high activity table or not?

I have a table with 1.5 billion records which is sampled at 1%. Said table receives over 200-300k insert/update/deletes per hour. Within a couple of hours, the statistics are auto updated as it ...
DB-Ay's user avatar
  • 11
0 votes
1 answer
141 views

Get absolute number and percentage of NULL values for each column of each table in a given schema

I'd like to extract some basic statistics about how tables are populated in a given PostgreSQL 16 schema, such as the absolute number and percentage of null values for each column in all tables in ...
s.k's user avatar
  • 372
0 votes
0 answers
128 views

In the past week, SELECT StatMan [...] has run 150,000 times. Is this normal?

I retain a week's worth of Query Store data. I was inspecting it for one database earlier today and found something surprising. This query SELECT * FROM sys.query_store_query AS qsq INNER JOIN sys....
J. Mini's user avatar
  • 431
0 votes
0 answers
39 views

TSQL query for metadata statistics like granularity, number of rows, indexes, keys, like a broadened MySQL INFORMATION_SCHEMA.STATISTICS, but for TSQL

The MySQL INFORMATION_SCHEMA.STATISTICS table has these columns: TABLE_CATALOG The name of the catalog to which the table containing the index belongs. This value is always def. TABLE_SCHEMA ...
questionto42's user avatar
0 votes
0 answers
60 views

Do Primary Keys On Table Variables Have No Statistics At All? [duplicate]

My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows ...
J. Mini's user avatar
  • 431
0 votes
1 answer
66 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
80 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
200 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 ...
Golosie Emanuel's 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 ...
Shiwangini's user avatar
1 vote
1 answer
372 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
710 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
190 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,634
1 vote
1 answer
78 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
  • 69
0 votes
3 answers
284 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
  • 113
3 votes
1 answer
180 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
187 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,970
3 votes
0 answers
262 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
2k 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
125 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
3 votes
2 answers
1k 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
  • 153
0 votes
2 answers
158 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
139 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
  • 521
0 votes
3 answers
824 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
  • 665
8 votes
1 answer
478 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
  • 85.6k
-1 votes
1 answer
131 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
295 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
48 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
205 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
  • 305
-3 votes
1 answer
694 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,634
0 votes
2 answers
185 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
231 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
270 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,970
3 votes
1 answer
198 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,970
-4 votes
1 answer
162 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,634
1 vote
1 answer
477 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
4k 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
1 vote
3 answers
310 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
  • 587
0 votes
1 answer
605 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
  • 263
3 votes
2 answers
3k 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
255 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
409 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
207 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
228 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
397 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
75 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
2 3 4 5
8