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.

35 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
0answers
318 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 ...
3
votes
0answers
944 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: http://www.sqlservercentral....
3
votes
1answer
408 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 ( ...
2
votes
0answers
117 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, ...
2
votes
0answers
149 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 ...
2
votes
0answers
72 views

Index statistics are requiring constant updates due to timeouts

We have been experiencing an odd behavior in our application where various modules will begin to timeout in SQL Server 2012. Each time we stumble across this issue, we find that the statistics require ...
2
votes
1answer
151 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 ...
2
votes
0answers
568 views

Oracle: separate column histograms for partitions

I have a partitioned table like MY_TABLE(PART_KEY, DATE_KEY, ...) with a local partitioned index on (PART_KEY, DATE_KEY). When I collect stats on the table, I end up with a HEIGHT BALANCED histogram ...
1
vote
0answers
24 views

pg_stats is huge and slowing PostgreSQL server down… What do I do?

I have two servers running PostgreSQL 10.11 that are configured the same, contain the same databases and mostly the same tables. Various Nagios checks using check_postgres check these servers on a ...
1
vote
0answers
40 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
0answers
80 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 ...
1
vote
0answers
49 views

PostgreSQL: optimize Rarity Score calculation

I need to populate a value called "Rarity Score" for collectible items by one query. Here is the data I currently have: Items table +-----+--------+-------+ | id | name | score | +-----+--------+-...
1
vote
0answers
1k views

Gather stats - Partitioned Table

Database: Oracle 12c I have a process that selects data from Fact Table, summarizes it and pushes it to Summary Table. Summary table is Range Partitioned (Trade Date) and List Partitioned (File Id). ...
1
vote
0answers
139 views

Logical read when use Offset in SQL

When I execute this script SET STATISTICS IO ON SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] FROM vwTblTest WHERE ([Factor] >= 139501 AND [Factor] < ...
1
vote
0answers
61 views

NuoDB Statistics

I'm currently doing a research using a TPC-H table and I'm trying to get the running time for 3 queries in NuoDB: SELECT * FROM LINEITEM SELECT * FROM LINEITEM WHERE L_PARTKEY BETWEEN 1 AND 80000 ...
1
vote
0answers
67 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
0
votes
0answers
21 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....
0
votes
0answers
22 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 ...
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. ...
0
votes
1answer
91 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

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
0answers
39 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 .... ...
0
votes
0answers
173 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 (...
0
votes
0answers
53 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 ...
0
votes
1answer
762 views

Locks caused by async stats update

SQL Server has an option for statistics to autoupdate asynchronously (AUTO_UPDATE_STATISTICS_ASYNC). But, even if this option is ON, from time to time I see locks like this on pic below: say, spid ...
0
votes
1answer
282 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
1answer
32 views

What is the impact of SHOW STATUS, and how often is acceptable to call it?

I am setting up a monitoring thread in my application that should use the SHOW STATUS call of mysql to periodically calculate system / db runtime statistics. Being relatively unfamiliar with datbase ...
0
votes
0answers
937 views

Large differences between estimated and actual row counts but stats are OK

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators ...
0
votes
0answers
383 views

Columnstore Index Scan Cardinality Estimates

In SQL Server 2012, I am seeing large cardinality estimate discrepancies for a columnstore index scan. It looks like statistics have been generated for the index, but I don't see any details in DBCC ...
0
votes
0answers
205 views

Replicating Excel's CHIDIST function in Oracle 11g

I'm attempting to replicate the functionality of an Excel workbook in Oracle. One of the formulae on the worksheet uses CHIDIST, which "Returns the one-tailed probability of the chi-squared ...
0
votes
1answer
248 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
votes
1answer
79 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. ...
-1
votes
1answer
326 views

How to get user Insert statistics in MySQL

I'm trying to get statistics on the write load on my MySQL servers. Something like what show global status like "Com_%"; gives me. but, here comes the catch - we're using Master-Master replication. ...