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
2answers
40 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
57 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(...
-1
votes
0answers
41 views

SQL Server: how to get statistics sizes? [closed]

I'd like to query the sizes of all results created by CREATE STATISTICS: the histograms and the density vectors. I tried the following snippet: SELECT t.Name AS TableName, p.rows AS RowCounts, CAST(...
0
votes
1answer
45 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
51 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
92 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
14 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
263 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
43 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
235 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
22 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
47 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
333 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
54 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
65 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
43 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
80 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 ...
0
votes
1answer
153 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 ...
7
votes
0answers
132 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
52 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
82 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
65 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
25 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
44 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
67 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
43 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 ...
6
votes
0answers
250 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
53 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
215 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
33 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
123 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
232 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
106 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
340 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
523 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
168 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(...
1
vote
1answer
64 views

Cardinality discrepancy

I have two, almost identical, databases and I am trying to figure out what is causing queries to perform that much slower in one of them. While investigating using the information_schema.statistics ...
0
votes
1answer
50 views

Should I clean up my old table statistics?

I've been cleaning up my indexes but now realized I have over 150 statistics that remain that are not used. Should I drop them? I'm not noticing any performance issues from them. I have 9 indexes ...
0
votes
1answer
122 views

User created index question - sp_Blitz 5/17/18 - Incorrect output?

I have SQL Server 2014 CU11 with sp_Blitz 5/17/18 8:44AM release. sp_Blitz is reporting that I have user created statistics on some of my databases. I've executed a script that deletes all user ...
0
votes
0answers
30 views

Oracle 12c - getting IMPORT_TABLE_STATS from view

I have some code running on Oracle 11g, we are migrating to 12c (12.2.0.1.0). In one of the processing procedure DBMS_STATS.IMPORT_TABLE_STATS is invoked and in stattab parameter name of the view is ...
2
votes
1answer
285 views

Query plan do not get invalidated after update statistics

I'm running some tests to find out when query plans get invalidated after an update statistics. The machine I'm using for testing is a SQL Server Developer 2016 SP1 CU7. I found an article on ...
10
votes
2answers
368 views

Never ending Query Store search

I'll say from the beginning that my question/problem looks similar to this previous one, but since I'm not sure if the cause or the starting info is the same, I decided to post my question with some ...
7
votes
1answer
1k views

Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time?

I am running an Azure SQL database under the S2 edition (50 DTUs). Normal use of the server usually hangs around 10% DTU. However, this server regularly gets into a state where it will send DTU usage ...
0
votes
1answer
71 views

Logical Reads vs. Records Returned

I have a very basic query against a large table that roughly follows this form: SELECT [Extent1].[A] AS [A], [Extent1].[B] AS [B] FROM [dbo].[SomeTable] AS [Extent1] WHERE ([Extent1].[A] &...
1
vote
2answers
203 views

How to query time specific performance in postgres?

The data in the pg_stat_statements table is really useful for me. I need to know the queries and the time they take. How can I perform a query to pg_stat_statements to get the "total_time" over the ...
1
vote
1answer
819 views

How to ensure pg_stat_activity shows all my queries?

I am working with pg_stat_activity in postgres. It is working and I can see queries in this view after clicking the play button in datagrip. However, some queries I definitely executed (see screenshot,...