Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

2
votes
0answers
45 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
31 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
84 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
15 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
88 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
164 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
13 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
22 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, ...
2
votes
2answers
76 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
81 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
48 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
68 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
106 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
17 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
277 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
58 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
251 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
0answers
29 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
52 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
423 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
56 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
74 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
51 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. ...
2
votes
1answer
87 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
37 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
323 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 ...
8
votes
0answers
146 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
55 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
126 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
73 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
26 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
45 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
69 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
47 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
17 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
258 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
57 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
38 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 ...
2
votes
1answer
277 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
34 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 (...
2
votes
1answer
60 views

Should SQL Server statistics be included in versioning?

Recently I ran the SQL Server Tuning Advisor. It suggested to add some statistics. Currently we have no statistics checked in our database versioning. My question is: Should these suggested ...
1
vote
2answers
130 views

Improving statistics on a large “WHERE IN” SQL Server query

Currently, I'm trying to run the following example query: SELECT [DATA1], [DATA2] FROM TABLE WHERE [DIMENSION0] IN (1, 5, ... (possibly 10s of numbers)) AND [DIMENSION1] IN (5) AND [...
6
votes
2answers
263 views

Statistics update with automatic sampling messes up density vector and histogram

I have a table that contains 22 millions of records. I have noticed that statistics for a column were way off, even if that column has a constant distribution: in fact, every value is repeated twice....
1
vote
1answer
118 views

Find outlier statistic in SQL server?

I have a stored proc which searches "bad" data in our application database (sql server 2016 enterprise, ~500gb data). "bad" in a business/process sense. It normally takes 10-30s to execute. After ...
0
votes
2answers
427 views

Analyze too slow after postgres upgrade

I'm working on a testing server to upgrade a large database, 700GB with around 20000 tables. The upgrade was fast enough using the --link option, but the analyze only script takes 8 hours, which is ...
4
votes
3answers
577 views

SQL Server - query optimization for highly skewed data distributions

I am trying to optimize a query which looks similar to this: select top(1) t1.Table1ID, t1.Column1, t1.... .... t2.Table2ID, t2.... .... c.FirstName, c.LastName, ...
2
votes
1answer
195 views

Query using a filtered index but wrong estimated number of rows

I have a table with a nonclustered index on a datetime2 field. On that same table i have a field (char[1]) which is used to logically delete records and can have 2 distinct values : A(active) or D(...