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
1
vote
2answers
74 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
41 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
28 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
113 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
649 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
84 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
0answers
54 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
34 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
473 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
140 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
45 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
26 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
36 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
103 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 ...
14
votes
1answer
381 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
32 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 ...
1
vote
2answers
182 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
75 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
146 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
39 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
277 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
25 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
24 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
384 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
19 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
194 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
111 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
84 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, ...
3
votes
2answers
269 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
126 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(...
0
votes
1answer
60 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
161 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
195 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
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. ...
6
votes
1answer
360 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
182 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
291 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
1answer
95 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
179 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 ...
8
votes
1answer
619 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
81 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
99 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
105 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. ...
3
votes
2answers
237 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
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
1answer
1k 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 ...
9
votes
1answer
174 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
64 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 ...