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
0answers
13 views

Frequency of table update in postgresql?

Given postgres 11. I'm looking for statistics on how often a particular table gets something inserted/updated/deleted. There's of course this: https://www.postgresql.org/docs/11/monitoring-stats....
1
vote
1answer
31 views

When are statistics created?

I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we ...
1
vote
2answers
49 views

Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

I have a SQL Azure database that's about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in ...
10
votes
1answer
238 views

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
0
votes
1answer
20 views

SQL Server. Do reads on Read-Only Replica in Availability Group fire Auto Update Statistics on Primary?

We have MS SQL Server 2014 Enterprise Edition with an Availability Group with Readable Secondary. Our application reads data only from Secondary. As far as I know, auto update statistics starts when ...
1
vote
2answers
42 views

Db2 REORGCHK returns the SQL2310N (Error “-668”) message

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes. I removed a column: db2 "alter table SCHEMA.TABLE drop ...
1
vote
1answer
20 views

Table not analyzed after partition has been split

On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics. ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO ( SUBPARTITION ...
2
votes
0answers
58 views

Incremental Schema Stats (With partitioned/Subpartition table) gathering is taking too much time in oracle version 12.2.0.1

When I am gathering schema stats with below options, it is taking too much time: EXEC dbms_stats.gather_schema_stats ( ownname => 'SCHEMA', granularity => 'AUTO', CASCADE=> true, ...
1
vote
1answer
37 views

how to override a table's cardinality or statistics histogram

I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query. Can someone explain how ...
2
votes
2answers
129 views

Slow select Queries on Log shipping standby server

We have a database in primary DC around 15 TB with partitioned tables with largest one around 7 TB. To avoid users from reporting a LS set up was done for above database in different DC where the LS ...
0
votes
1answer
18 views

MySQL 5.7 statistics - Change db

I have a MySQL 5.7 database. I was looking in statistics in server status that shows type of queries / commands / tasks executed in the database. It shows that the query command "change db" by far was ...
0
votes
0answers
15 views

SharePoint changing statistics options daily

SharePoint switches OFF and ON options like AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNC, AUTO_CREATE_STATISTICS, INCREMENTAL on SharePoint databases daily at 12 AM and that generates lots of ...
2
votes
4answers
109 views

What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx?

Can anyone point me to the correct documentation or additional permissions I need besides SA to do the following? When I run it, I receive the following error: Cannot find the object "#test" because ...
0
votes
1answer
17 views

Return results of two MySQL query in only one

I am running two seperate queries to get statistics from 3 different tables: contacts campaigns sent_messages The first query counts all contacts that are marked as "stop" in the contacts table (...
3
votes
2answers
172 views

why it is doing index seek

I created a sample table as below CREATE TABLE [dbo].[StatisticsDemo]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL ) ON [PRIMARY] Then I inserted below data as below: ...
2
votes
1answer
44 views

Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException

We have an Excel table that gather data from our datawarehouse db on a SQL Server 2016. It queries a relative simple view to find the top customers of the last few days (including some aggregated ...
7
votes
3answers
1k views

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
1
vote
1answer
38 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, ...
3
votes
2answers
110 views

updating statistics using STATS_STREAM or WITH FULLSCAN?

the syntax for creating statistics is: -- Syntax for SQL Server and Azure SQL Database UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name ...
1
vote
2answers
94 views

sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way?

I have this table in one of my databases: IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL DROP TABLE [dbo].[repl_Gender_Type] GO CREATE TABLE [dbo].[repl_Gender_Type] ( [Gender_TypeID] CHAR(...
0
votes
1answer
50 views

Execution plan for staging In-Memory tables estimate row count =1

We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process. the nature of these staging tables is empty most of the time. The ETL process executed ...
2
votes
1answer
96 views

Auto generated statistics by query optimiser

Can we consider removing the auto generated statistics if the update statistic within a given database is taking too long? We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but often ...
2
votes
1answer
131 views

Stored Procedure: table-Value parameters

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and ...
0
votes
0answers
19 views

Storing summary statistics for a large table

So I have a large table that stores events triggered by a user. These can be anything from logging on to viewing a profile to messaging another user. I want to track how many views a user receives. ...
6
votes
1answer
291 views

When do _WA_Sys_ statistics Get Updated?

I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table) The rule of thumb appears to be in ...
1
vote
1answer
83 views

Azure SQL Database and Database Maintenance

If SQL Agent isn't available for Azure SQL Database then is it possible to maintain (indexes, statistics etc) the databases with Ola Hallengren's maintenance scripts, for example?
4
votes
1answer
259 views

Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?

I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates. I currently have the following result in the execution plan for a very ...
0
votes
1answer
66 views

Postgresql function equivalent to Excel's NORMSINV function?

I need Excel's NORMSINV equivalent function for PostgreSQL, preferably plpgsql function. Can somebody help? For reference, here is Excel's normsinv function explained: https://support.microsoft.com/en-...
0
votes
2answers
69 views

Where does Postgres store all statistics?

I recently ran into a weird problem while migrating from PG 9.4 => 11 I replicated the production server from PG 9.4 to PG 11 using pglogical. Everything worked out well, except we discovered that a ...
7
votes
1answer
507 views

Why is the auto created statistic on this column empty?

Info My question relates to a moderately big table (~40GB data space) that is a heap (Unfortunately, I am not allowed to add a clustered index to the table by the application owners) An auto created ...
0
votes
1answer
62 views

SQL Server indexes Work Sometimes [duplicate]

So I have the following indexes on my table: CREATE NONCLUSTERED INDEX [$5] ON [dbo].[Record Link] ( [Company] ASC, [Record ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ...
0
votes
1answer
81 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
63 views

what is more important on a query execution plan, cardinality or selectivity?

The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. ...
3
votes
2answers
209 views

How often should you update your stats?

How often should you update your stats? What is "too seldom"? How often is "too often"? The answer is "it depends" on your database, users, data, etc. So I've tried to log what our stats look like ...
1
vote
0answers
38 views

Statistics injection for calculating the cost of a query

I want to find out the cost of a query by "EXPLAIN" query in Postgres. As I know, Postgres calculate this cost by using the statistics of the table. (e.g: relpages, reltuples in pg_class or some ...
1
vote
1answer
526 views

incorrect Estimated Number of Rows vs Actual number of rows

I'm seeing a huge difference in the estimated number of rows vs actual number of rows. I'm not sure if i'm reading this right, so does this mean that sql server estimated the number of rows returned ...
9
votes
1answer
165 views

Statistics disappearing/empty randomly throughout the day

I've got a SQL Server 2017 (CU9) database that is exhibiting some performance related issues that I believe have to do with the index statistics. While troubleshooting I discovered that the ...
1
vote
1answer
60 views

Oracle Optimizer refuses to use my index

I have a relatively simple query that SELECT data in a table. SELECT l.columnRecovered FROM schema.TABLE l WHERE (column1 = :a or (:a is null AND column1 is null)) AND (column2 = :b or (:b is null ...
0
votes
1answer
168 views

Generate the statistics of a table after truncating it and then repopulate it?

Is it necessary to generate the statistics of a table after truncating it and then repopulate it? I have a table with several million rows in Oracle.Every week new data is recharged, truncating the ...
1
vote
0answers
76 views

Why are my statistics getting out of date so quickly?

I've got a large table (300 million rows), with about 1 million rows added per day. This is time-series data, so I have the following setup: CREATE TABLE Readings ( Id INT PRIMARY KEY, ...
1
vote
0answers
29 views

How do I see what update statistics is doing while running?

I have a very large table with 500m rows and 150 columns (I did not design the table, im simply trying to figure out what is going on here). I try to update a single statistic for this table. I run ...
1
vote
1answer
46 views

Does number of rows affect Update Statistics performance if you are sampling with n rows?

For example, I have table A with 80 million rows and table B with 200 million rows. Assume both tables have 30+ stat tables (all manually generated by SQL Server). I randomly take the first 10 stat ...
2
votes
1answer
71 views

Build a histogram of road speed based on road length by grouping road segments

A data set look like this: The table doesn't have sum(segLength yet), but can be computed by summing up sum(segLength) from road_table group by roadID A roadID (e.i. Lake Street) belongs to a ...
2
votes
1answer
54 views

Concurrent requests not running in parallel

I am trying to run update statistics in parallel. Say I start with 2 sessions: S1, S2 In S1 I run Update Statistics A with fullscan and time it to take 2 minutes. In S2 I run Update Statistics B ...
1
vote
1answer
18 views

Not Auto and User Generated Stats in Catalog view of SQL Server

While querying select * from sys.stats I get this result In the Books Online it is written But looking at results I can see that there are also some stats which are not created neither ...
7
votes
0answers
280 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 ...
2
votes
1answer
61 views

Optimizer failing to load statistic when creating plan in SQL Server 2017 “Calculator failed. Replanning.”

This morning, the statistics on one of our filtered indexes updated, and when a plan that uses that statistics recompiled it stopped using that index (and started scanning the entire clustered index). ...
1
vote
3answers
40 views

Correcting a database that has had indexes and statistics created on it willy-nilly

I've inherited a DB and I believe it was the victim of DBAs who encountered random slow operations, ran the tuning adviser for individual queries and blindly created the recommend indexes and ...
3
votes
1answer
337 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
0
votes
1answer
41 views

Is there a way to restrict automatic statistics updates to a certain timeframe?

I have an Azure SQL Database that runs on average at < 10% DTU capacity. It seems that at random times during the day large statistics update queries will execute on the database, causing DTU (...