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
1
vote
2answers
51 views

Disable AUTO_UPDATE_STATISTICS

We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the ...
1
vote
2answers
31 views

Postgres date_trunc quarter with a custom start month

I'm trying to create quarterly average for player scores, however the default behaviour of postgres date_trunc('quarter', source) is that it starts first quarter with YYYY-01-01. Is there any way ...
12
votes
1answer
10k views

What is the default sample size of statistics in SQL Server?

From MSDN: When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimiser samples the data and computes the sample size by default. How to identify default ...
2
votes
1answer
36 views

Why do SQL Server auto-created statistics sometimes include the primary key?

I had always understood that SQL Server auto-created statistics only include one column. In other words, they are not multi-column statistics. However, today I came across some auto-created ...
1
vote
2answers
265 views

Are idx_scan statistics reset automatically (default)?

I was looking at the tables (pg_stat_user_indexes and pg_stat_user_tables) and discovered many indices that are not being used. But before I think about doing any operations to remove these indices, ...
0
votes
1answer
34 views

How do I make PostgreSQL include all the months where I did not buy anything? [duplicate]

I have this query: SELECT avg(sum) FROM ( SELECT date_trunc('month', "timestamp"), sum(amount) as sum FROM bookkeeping WHERE summary LIKE 'Bitcoin purchase:%' GROUP BY date_trunc(...
1
vote
1answer
26 views

Row estimate more accurate for primary key column than for others?

Let's create two test tables in a PostgreSQL 13 database: CREATE TABLE foo ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, value int NOT NULL ); CREATE TABLE bar ( id bigint PRIMARY ...
0
votes
1answer
126 views

Bad query plan only on first execution, not in SSMS

We have a very customizable report query system where the user can pick and chose which columns to include in the results as well as a lot of filters. I'll be mapping what we're doing onto an example ...
0
votes
1answer
33 views

Using Postgres to return a selection based on frequency distribution

I am not even sure if this is a possibility so my apologies if I'm barking up the wrong tree; I am ignorant of Postgres's limitations as I am self-taught. This is hypothetical, but if I had a table ...
2
votes
0answers
54 views

How do I measure the overhead of a Postgres extended statistics object?

Some solutions to poor query performance suggest creating extended statistics objects to help the query planner better understand the shape of the data. Because these statistics objects are not ...
1
vote
1answer
44 views

Questions on updating statistics and index maintenance jobs

Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions: For ...
0
votes
1answer
390 views

When is dm_exec_procedure_stats updated?

I just created a new proc and ran it, but it is not showing up in my query: SELECT schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time ...
0
votes
2answers
32 views

Person unique identification

I am working on a sports database. I have table for persons(players), teams, leagues etc. The problem is, how do I identify a person uniqely? I was thinking that a new person could be added by either ...
3
votes
3answers
6k views

Analyze, Vacuum equivalent for SQL Server?

In Postgres, ANALYZE collects statistics about the contents of tables in the database, and store it. These statistics are used to determine the most efficient query plan, in PG. Running analyze ...
3
votes
1answer
184 views

Data impact on analyzing mysql table stats

I have a MySQL database that appears to be getting slower over time. After some googling, I determine that the database probably needs to be analyzed. This is because innodb_stats_on_metadata is set ...
8
votes
2answers
285 views

Inability to query underlying table of a schema-bound view

I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE ...
3
votes
1answer
105 views

Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)

Assume that a query in question has a very wide set of columns. The query looks like the following: set statistics io,time on; Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5 From FirstWideTable a ...
3
votes
1answer
85 views

Query behavior - with regard to statistics

A quick background of the issue: We have an application, and we have many instances of this application running for clients. While they may be on slightly different versions, they are fundamentally ...
0
votes
1answer
49 views

How to set statistics level of a table in postgres?

I know how to set statistics level for a particular column i.e alter table table_name alter column column_name set statistics 100; However, if I wish to set statistics level for a particular table or ...
0
votes
0answers
12 views

How to find the statistics level of a column in postgres?

I have altered the statistics level of column using below command. edbstore=> alter table orders alter column orderdate set statistics 100; From which table, i can find the statics level of all the ...
0
votes
1answer
23 views

Statistics not collected on Insert-Select

Based on the information here, I expected the statistics to be automatically collected during an Insert-Select operation on a new table in Oracle. However, I don't see any table-level statistics ...
0
votes
0answers
26 views

PostgreSQL JSONB Execution Plan with high Offset

How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance? Considering the below "Test Query", help me to clarify the ...
0
votes
1answer
32 views

index used when query process on oracle database

I a new one using and learn about oracle database. currently i have sql query like this select attend_code from attadanstatusdetail where attend_id = 'attendidcode001' and company_id = 168 currently ...
5
votes
1answer
159 views

Statistics histogram AVG_RANGE_ROWS discrepancy

According to MS docs, the description for AVG_RANGE_ROWS is: Average number of rows with duplicate column values within a histogram step, excluding the upper bound. When DISTINCT_RANGE_ROWS is ...
0
votes
1answer
43 views

Query optimizer triggering statistics update seems to be slowing down queries

We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around ...
0
votes
0answers
19 views

Sampled rows is very less for auto update stats in SQL Server 2016 [duplicate]

I have SQL Server 2016. One of the table is having 9.24 million records. One of the stats (no filter on stats) on it is getting auto updated by SQL Server with sampling rate of 1 % (120023 rows). As ...
3
votes
1answer
108 views

Badly-formed histogram causes bad estimates on Nested Loop

We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This ...
7
votes
3answers
511 views

Consistent query performance over time

We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of ...
0
votes
2answers
89 views

why are estimated rows exponentially increasing with each join?

summary We have an Object-Oriented database schema that we query with Hibernate with: 1 parent table (NAL_Actions) 7 child tables (NAL_*) If I want to query a couple of these "action"-...
2
votes
1answer
30 views

Select query slow in the big table because of expired stats in Microsoft SQL Server

I have an application to select/search records from the database (application and database in separate servers). In the database, one table only is fast growing table (more than 2 Millions per day). ...
2
votes
0answers
41 views

Incorrect EQ_ROWS from low % sampled statistic (but not on all columns)

On a particular nullable guid column I am getting EQ_ROWS that are substantially incorrect (Table has 977 million rows). Default sample size is to blame - currently about .1% Here is a single row out ...
0
votes
1answer
25 views

Statistics Update Time is missing in Actual execution plan

I remember that I have seen somewhere in the past that when I saw the XML of the actual execution plan, it had the details about when the stats were updated on the indexes on a table and what was the ...
10
votes
1answer
518 views

Cardinality estimation on tables with large cardinality variance between keys

Context I have stumbled on an issue in SQL Azure where the Sort-operator is spilling over into TempDB due to poor row estimates. We are querying a master table that is joined with a number of detail ...
0
votes
0answers
34 views

update stats on specific statistic not working?

I am using Ola Hallengren's maintenance solution and have come across something really strange. I grabbed the command from the log table as below: UPDATE STATISTICS [dbo].[APIActivityLog] [...
1
vote
2answers
52 views

Why does TempDB spill happen even though statistics are correct?

I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get ...
2
votes
1answer
170 views

Auto Update Statistics threshold SQL Server 2016

since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update ...
1
vote
1answer
165 views

Query performance degraded after upgrading Postgres

I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I ...
1
vote
0answers
34 views

How statistics play a major role in legacy CE vs new CE

We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100. Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
0
votes
1answer
26 views

can't find column stats for newly created table

I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached. database current settings are: I started with ...
0
votes
0answers
60 views

Statistics collector: query in a date range and other questions

I want to use the Statistics Collector (https://www.postgresql.org/docs/current/monitoring-stats.html) in order to obtain the total database times during a certain period. Once enabled, I can ...
1
vote
1answer
33 views

SQL Server: How to get total number of backups done in a month?

I need to gather monthly statistics for my boss, on how many FULL backups were done in a month and which number Succeeded and which failed if any. Is there a simple query that could give me this, ...
3
votes
2answers
60 views

Why is creating and dropping a random index fixing this performance problem?

I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...
1
vote
1answer
81 views

Range Optimizer in Mysql: What is an Index dive during Query Optimization?

On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer). Quoting his point, For as long ...
0
votes
1answer
73 views

Find all stats that were sampled

I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We ...
1
vote
0answers
27 views

Index Exists, data is present, but statistics are empty. Why? [duplicate]

I’ve created a dump of all statistics on all tables in a Production database (based on the three sets returned by DBCC SHOW_STATISTICS). The database has been up and running for several months, and is ...
3
votes
0answers
47 views

how to tell which statistics SQL server references in query [duplicate]

Is there a way to check in SQL Server 2016 which statistics the optimizer referenced when compiling a particular query? My question is similar to Check which statistics were used in query ...
6
votes
1answer
954 views

What is more appropriate sp_updatestats or UPDATE STATISTICS?

In a system there is a nightly data load process where all the foreign keys are dropped, all tables are truncated, repopulated from an external source, and the foreign keys are put back in place. At ...
0
votes
1answer
40 views

HowTo parsed Stored Procs to get a list of tables and columns referenced in WHERE, GROUP BY, AND JOIN clauses

Is there a tool, script, utility that can parse sys.sql_modules and return a list of tables and columns referenced in the WHERE, GROUP BY, and JOIN clauses of Stored Procedures? Example: CREATE PROC ...
0
votes
0answers
18 views

do statistics need to be calculated each time or stored?

I have a "user" table in a video-game, which have these properties: Money Experience Number of friends Each of these properties can be either stored in the table, and updated when something happen (...
28
votes
1answer
5k views

Where are Statistics physically stored in SQL Server?

Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool? More specifically, is there a way to figure out the pages used by ...

1
2 3 4 5
8