Stack Exchange Network

Stack Exchange network consists of 174 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.

1
vote
1answer
45 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
0answers
22 views

Do database restart affect create Index suggestion/statistic?

Do database restart affect create Index suggestion? If I restart database then right after running the sp_BlitzIndex @GetAllDatabases = 1 is it going to give an accurate index statistic? If no, how ...
0
votes
1answer
28 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
62 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
23 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
37 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
60 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
37 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
203 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
48 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
34 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
71 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
0answers
15 views

Perfmon like statistics from Azure with quotas

What is the easiest way to extract statistics from Azure SQL databases to on-prem for analysis and graphing, and to be able to accumulate history and do reporting across all Azure databases and ...
0
votes
1answer
20 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
100 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
132 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
73 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
0answers
74 views

measuring transaction processing time in mssql

Is there a way to measure the (avg) time it takes mssql to process a transaction across a set of transactions (or spanning a certain period)? I'm thinking potentially of sys.dm_os_performance_counters)...
0
votes
2answers
171 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
395 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
110 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
50 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 ...
1
vote
0answers
21 views

How do get a better understanding of statistics for performance issues [closed]

Below are some statistics from a query which took 28 minutes compared to its usual 13, it cannot be explained why the sudden jump and will probably revert back tomorrow. Firstly having never read ...
0
votes
1answer
48 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
120 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
23 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 ...
1
vote
1answer
269 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 ...
8
votes
1answer
183 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 ...
6
votes
1answer
537 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
52 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
127 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
499 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,...
1
vote
0answers
19 views

How stat info propagate to slave server in PostgreSQL?

I'm testing upgrade operation in Postgres 9.6 family. I'm using RDS and after upgrade done, I executed ANALYZE operation by vaccumdb --analze-only to Master. After analyzing done, I checked the ...
0
votes
0answers
28 views

How to determine the size of my table per column type in the SQL Server database

I want to see how much mb used in table per column type. For example; TABLE 1 type size(mb) ------- -------- bigint 300mb bit 10mb char .... datetime .... ...
2
votes
1answer
243 views

Ola Hallengren's index rebuiuld script - how to exclude more than one object? [closed]

I'm using Ola's script to rebuild indexes. But after problems with script blocking particular objects that are used by SOLR, I wanted to exclude them and run script for them in another schedule. So, ...
0
votes
0answers
111 views

Calculating lifetime customer value per month

I'm trying to calculate the lifetime customer value (LCV/CLV) for the last 12 month for the date the customer joined. Supposing each customer is buying something more than just in that first month (...
3
votes
1answer
623 views

Analyze needed after a refresh materialized view?

Are we supposed to run ANALYZE myview; on a PostgreSQL 9.6+ materialized view after running a REFRESH MATERIALIZED VIEW CONCURRENTLY myview;? Or is it useless (maybe index statistics are already ...
0
votes
1answer
262 views

MySQL / MariaDB : Many indexes vs. One multi-column index

I need to optimize my MariaDB tables index. I've read a lot of very interesting things on StackExchange, but I'm not sure to have the answer for my case. In my case, I have a table named TableA which ...
1
vote
1answer
334 views

MySQL : DATE_FORMAT() vs. SUBSTR()

I'm currently working on MYSQL DB optimizations. I'm looking around some slow queries and I try to optimize some of them. In my app, for statistics purposes, I'm querying and -very- big table (...
2
votes
0answers
101 views

Howto preserve statistics when switching partitions of partitioned tables

Given are two partitioned tables T1 and T2 with no automatic statistics (their statistics where updated WITH NORECOMPUTE option). T1 has only partition N populated and T2 has partition N empty. Now I ...
0
votes
1answer
205 views

How can I reset the counters from pg_stat_database view?

I know I can reset some counters by running: select pg_stat_reset() but some counters like xact_commit aren't reset. How can I do it? I've tried also: pg_stat_reset_shared('database') but doesn'...
0
votes
0answers
40 views

How to handle frequently outdated statistics dealing with an ORM?

We have a vendor application that uses Hibernate ORM (an object-relational mapping tool). It routinely generates queries returning hundreds of columns out of 10+ tables at a time. By logging the ...
-1
votes
1answer
77 views

SQL Server is creating incorrect plan

I have 200 GB production database. Normally daily prepared query plan count 8K. Most of these plans are non necessary because of the non parametric queries. Software developers are working on this. ...
6
votes
1answer
1k views

Bad execution plan after stats update due to temp table

A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters. Issue seems to come from a ...
2
votes
1answer
177 views

How to best manage Index Builds with Stats Updates?

This is a follow-up to what I read on Is there any reason to stop transaction log backups during a maintenance window? in the answer proposed by sp_BlitzErik. I use Ola Hallengren's indexing script ...
2
votes
2answers
792 views

Update Statistics with full scan for a single table

My application search becomes slow some times in a day.And whenever i update statistics for a particular table,it gets fixed. So i am thinking of creating a job to run update statistics every morning ...
-3
votes
2answers
170 views

SQL Server: How to set time in a human readable format

I found this useful query here and I use it to monitor SQL Server jobs. SELECT sJobHis.[server], sJobStep.database_name, SJob.name, SJob.enabled, CASE WHEN SJob.enabled = 0 THEN '0' WHEN sJobStep....
2
votes
0answers
40 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 How to check what stats were used in query, except ...