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
0
votes
0answers
27 views

update stats on specific statistic not working?

I am using Ola Hallengren's maintenance solution and have come across something really strange. I grabbed the command from the log table as below: UPDATE STATISTICS [dbo].[APIActivityLog] [...
1
vote
0answers
35 views

Badly-formed histogram causes bad estimates on Nested Loop

We have a query on a SQL Server 2016 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loop operator, in reality 104989 rows came back. This caused that the ...
0
votes
2answers
43 views

Why does TempDB spill happen even though statistics are correct?

I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get ...
2
votes
1answer
61 views

Auto Update Statistics threshold SQL Server 2016

since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update ...
1
vote
1answer
28 views

Query performance degraded after upgrading Postgres

I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I ...
0
votes
1answer
61 views

why are estimated rows exponentially increasing with each join?

summary We have an Object-Oriented database schema that we query with Hibernate with: 1 parent table (NAL_Actions) 7 child tables (NAL_*) If I want to query a couple of these "action"-...
1
vote
0answers
33 views

How statistics play a major role in legacy CE vs new CE

We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100. Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
0
votes
1answer
24 views

can't find column stats for newly created table

I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached. database current settings are: I started with ...
0
votes
0answers
20 views

Statistics collector: query in a date range and other questions

I want to use the Statistics Collector (https://www.postgresql.org/docs/current/monitoring-stats.html) in order to obtain the total database times during a certain period. Once enabled, I can ...
1
vote
1answer
29 views

SQL Server: How to get total number of backups done in a month?

I need to gather monthly statistics for my boss, on how many FULL backups were done in a month and which number Succeeded and which failed if any. Is there a simple query that could give me this, ...
3
votes
2answers
57 views

Why is creating and dropping a random index fixing this performance problem?

I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/...
0
votes
1answer
37 views

Range Optimizer in Mysql: What is an Index dive during Query Optimization?

On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer). Quoting his point, For as long ...
1
vote
0answers
27 views

Index Exists, data is present, but statistics are empty. Why? [duplicate]

I’ve created a dump of all statistics on all tables in a Production database (based on the three sets returned by DBCC SHOW_STATISTICS). The database has been up and running for several months, and is ...
0
votes
0answers
14 views

do statistics need to be calculated each time or stored?

I have a "user" table in a video-game, which have these properties: Money Experience Number of friends Each of these properties can be either stored in the table, and updated when something happen (...
0
votes
0answers
19 views

Multidimensional histogram analysis for similarity

Okay, I have a weird case. I need to compare rows of a database for "similarity". By, this I don't mean text based. I mean I need to partition statistics into buckets (each bucket group is a different ...
-3
votes
2answers
67 views

Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?

I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table. The query only involves 3 tables (each ...
1
vote
1answer
25 views

How to do Incremental statistics on auto_created stats(_WA_) in SQL server

How to do Incremental statistics on auto_created stats(WA) in SQL server? I know I can do this for the non auto_created stats (indexed column) either by rebuilding index on the table or at DB level. ...
0
votes
1answer
30 views

SSMS execution time queries besides SET STATISTICS [closed]

When I run my query, it prints multiple execution times instead of just one. I only want one so what should I use instead of SET STATISTICS? SET STATISTICS TIME ON DECLARE @firstNum INT, @secondNum ...
8
votes
1answer
154 views

Improve the poor performance of the sys.dm_db_stats_properties DMV

We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL ...
0
votes
1answer
84 views

in what all scenarios SQL Server update statistics

I know SQL Server update stats when 20% of data is changed in a table. It keeps tracks of it by using modification counter which we can see in [modification_counter] column of sys.stats table. But I ...
1
vote
2answers
83 views

Is it normal to UPDATE STATISTICS this frequently? [duplicate]

I have a stored procedure that processes data from a source table and is executed many times (about 20,000 executions) throughout the day. I believe it's getting choked up sometimes, due to parameter ...
0
votes
0answers
69 views

Can you update statistics on a sys change tracking table

I'm tracking down some poor query times doing the typical query to get changed records: select StoreItemId from changetable(changes [dbo].[StoreItem], @clientChangeTrackingVs) as ChangedRow where [...
0
votes
0answers
32 views

How to get statistics by query in a range of dates

I have the following query: select SUBSTRING(VARIABLE_NAME, 5) as tipo_consulta, VARIABLE_VALUE as numero_total_operaciones, round(VARIABLE_VALUE / ( select VARIABLE_VALUE ...
5
votes
2answers
144 views

Does the command SET STATISTICS…ON turn statistics information on for all queries running throughout the server or just the current connection?

In regards to SET STATISTICS TIME ON and SET STATISTICS IO ON I noticed in the remarks of the Microsoft doc, it says the following: When STATISTICS IO is ON, statistical information is displayed, ...
4
votes
2answers
655 views

Query execution plan is horrible until Statistics are Updated

I hope you guys can help me here. Our application polls a message table every 3 seconds looking for notifications to send out. This works well on all our Customers (single-tenant DB) except one. They ...
3
votes
1answer
94 views

Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)

Assume that a query in question has a very wide set of columns. The query looks like the following: set statistics io,time on; Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5 From FirstWideTable a ...
2
votes
1answer
120 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, run on comparable hardware, contain the same databases and (mostly) the same tables. Various Nagios checks using ...
1
vote
0answers
30 views

Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error [duplicate]

I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX) Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a ...
1
vote
1answer
44 views

Understand PostgreSQL's statistics about rows and times

I'm trying to find out how much database load an aplication causes. Therefore I'm looking at the statistics of a single database (there's only a single user working with it). The numbers I get are ...
7
votes
1answer
477 views

Is there a reliable way to determine how long it took the optimizer to generate a query plan?

I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
0
votes
2answers
656 views

Postgres extremely slow after upgrade from Version 10 to 11

Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4 but trying to understand better... My queries was slow after upgrading to Postgres Version 11.5 from 10.7 I first ran ANALYZE ...
-1
votes
2answers
55 views

Query Execution Details [closed]

We need to find out the list of queries which were executed between a from time and to time, which user had executed the query and what was the execution time of that query.
0
votes
0answers
39 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....
1
vote
1answer
37 views

When are statistics created?

I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we ...
1
vote
2answers
195 views

Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

I have a SQL Azure database that's about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in ...
15
votes
1answer
422 views

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
0
votes
1answer
75 views

SQL Server. Do reads on Read-Only Replica in Availability Group fire Auto Update Statistics on Primary?

We have MS SQL Server 2014 Enterprise Edition with an Availability Group with Readable Secondary. Our application reads data only from Secondary. As far as I know, auto update statistics starts when ...
2
votes
2answers
359 views

Db2 REORGCHK returns the SQL2310N (Error “-668”) message

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes. I removed a column: db2 "alter table SCHEMA.TABLE drop ...
1
vote
1answer
136 views

Table not analyzed after partition has been split

On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics. ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO ( SUBPARTITION ...
2
votes
0answers
180 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
41 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
418 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
35 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
30 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
733 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
21 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
212 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
176 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 ...
8
votes
3answers
2k 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
165 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, ...

1
2 3 4 5
7