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
2
votes
1answer
59 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
19 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 ...
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 ...
2
votes
1answer
64 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
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 ( ...
0
votes
1answer
50 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
61 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
151 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
402 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
141 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
605 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
771 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
256 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
90 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
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
1answer
126 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 ...
3
votes
1answer
306 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
558 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 ...
9
votes
1answer
2k views

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

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 ...
-1
votes
1answer
105 views

How much Table Statistics Change before Query Execution Plan Update?

If I have table in an environment, and it has not undergone any DDL changes or sp_recompile. How much statistics data has to be inserted/updated before SQL Server 2016 Engine Recompiles a query ...
0
votes
1answer
108 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] &...
2
votes
2answers
286 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
1k 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,...
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 .... ...
2
votes
1answer
482 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
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 (...
4
votes
1answer
1k 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
671 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
1k 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
148 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
562 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
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 ...
-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. ...
7
votes
1answer
2k 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
275 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
2k 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
294 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
43 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 ...
14
votes
1answer
473 views

Unique index updates and statistics row modification counters

Given the following table, unique clustered index, and statistics: CREATE TABLE dbo.Banana ( pk integer NOT NULL, c1 char(1) NOT NULL, c2 char(1) NOT NULL ); CREATE UNIQUE CLUSTERED ...
5
votes
3answers
1k views

Could an update on all statistics separately cause bad performance?

We’ve been utilizing Ola Hallengren's maintenance scripts for our index optimization for years on our OLTP databases. (we are on sql server 2008 R2) 2 weeks ago though one of the DBA’s here has ...
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 | +-----+--------+-...
6
votes
1answer
580 views

Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why?

I use PostgreSQL 9.6. I have a query that joins 17 tables, 9 of those having several million rows. The query was running fine but its performance degraded rapidly this week. EXPLAIN's output didn't ...
10
votes
1answer
825 views

Why does SQL Server not do compound column statistics histograms?

SQL Server has a thing called "multi-column statistics", but it's not what one think it would mean. Let's take a look at the following sample table: CREATE TABLE BadStatistics ( IsArchived BIT ...
25
votes
3answers
572 views

Strange behaviour with sample sizes for statistics updates

I've been playing around investigating sampling thresholds with statistics updates on SQL Server (2012) and noticed some curious behaviour. Basically the number of rows sampled seems to vary under ...
-1
votes
2answers
114 views

Statistics in SQL Server

Does Microsoft SQL Server 2008 create any statistics on its own? Or is it implemented in later versions? I know the way how can we create statistics, how query optimizer uses the statistics in query ...
1
vote
1answer
154 views

Estimated number of rows is inaccurate

I have a table with a field that has a very large variance on a particular field that is often searched. The number of records associated with any value in the field highly differs. My estimated ...
3
votes
1answer
226 views

UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly. Step by step: Assembly: CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH ...
0
votes
1answer
759 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 ...
1
vote
3answers
418 views

Stats for usage of columns in queries

I am using MS SQL Server 2014. Have some kind of DWH. Is there some way to find some kind of stats, which columns from database are used in select clause and how many times, and which columns are used ...