Questions tagged [index-statistics]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
211 views

Performance issue after migration to SQL Server 2019

I am troubleshooting a performance issue on a job that is taking 5h longer to complete on SQL Server 2019 compared to when it ran on 2012. One of the problematic steps of the job is during a batch ...
Andre's user avatar
  • 13
7 votes
1 answer
172 views

SQL Server Primary Key Column Statistics Histogram Suggests Duplicate Values

I have a statistic on a Primary Key column in a table. When I update the statistic with the default options: UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350 I get a histogram as follows (...
SE1986's user avatar
  • 1,950
1 vote
1 answer
157 views

Can SQL server not update statistics because of being too busy to do that?

We've noticed on one of our servers, that after some time queries are falling to full table scan, seems due the outdated statistics. SQL Server is quite busy, receiving hundreds of queries/row ...
Giedrius's user avatar
  • 113
0 votes
0 answers
255 views

When to use sp_updatestats and when to use sp_updatestats with the resample option?

I am confused about the usage of RESAMPLE option and when to use it. We had problems with a customer DB. One/some queries took longer as expected and the CPU usage on the machine was higher than in ...
xMRi's user avatar
  • 230
0 votes
1 answer
90 views

PostgreSQL replication and index usage counts

Postgres collects index usage statistics in pg_catalog.pg_stat_all_indexes. Does anyone know if those statistics are identical across master and replica nodes when using “physical” wal replication?
Marcin's user avatar
  • 121
-1 votes
1 answer
104 views

DB2 Cardinality

In my tables, I have many statistics with -1 and I want to know how can I enforce DB2 to calculate these values. Select FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD ...
DB2fan's user avatar
  • 1
6 votes
2 answers
2k views

SQL Server: What's the Difference Between a 'Singleton Lookup' and a 'User Lookup'?

Microsoft describes the [user_lookup] counter in dm_db_index_usage_stats as the 'Number of bookmark lookups by user queries.' It describes the [singleton_lookup_count] in ...
AccidentalDBA_CO's user avatar
-4 votes
1 answer
139 views

Is the effect of statistics update immediate or does sql server need to be restarted?

Index statistics are utilized by sql server to derive the best plan for query execution. Suppose I update the index statistics then can existing queries use the effect of updated statistics or does ...
variable's user avatar
  • 3,541
-1 votes
1 answer
80 views

Why is the estimate (out of Stream Aggregate) so low?

I have a table like this: create TABLE dbo.MyTable ( TMyTableID bigint NOT NULL IDENTITY(1, 1), LogArgumentID bigint NOT null, LogID bigint NOT NULL, LogTextID int NOT NULL, FloatValue float NULL, ...
Henrik Staun Poulsen's user avatar
1 vote
2 answers
731 views

Disable AUTO_UPDATE_STATISTICS

We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the ...
KrishnP92's user avatar
1 vote
2 answers
853 views

Do frequent DML operations on a table with indexes and huge data affect SELECT query performance?

If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where ...
Mahen's user avatar
  • 13
3 votes
1 answer
113 views

Query behavior - with regard to statistics

A quick background of the issue: We have an application, and we have many instances of this application running for clients. While they may be on slightly different versions, they are fundamentally ...
Keith's user avatar
  • 163
0 votes
2 answers
642 views

How to update the statistics of an index that has just been reorganized, both with IndexOptimize script

I would like to know if we can update the statistics of an index that has been reorganized, using the IndexOptimize script. Indeed, an index that is reorganized needs to have its statistics updated. ...
Nathalie's user avatar
1 vote
0 answers
31 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 ...
Philip Kelley's user avatar
8 votes
5 answers
15k views

MySQL :: How to run ANALYZE TABLE for all tables in a database

I'm using MySQL 5.7 How can I run ANALYZE TABLE for all tables in a database. It should work if new tables are added to the database in future. This is to refresh the statistics of table indexes.
Satish Gadhave's user avatar
3 votes
2 answers
2k 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, ...
Gabriel's user avatar
  • 153
2 votes
0 answers
65 views

How to set STATISTICS_INCREMENTAL option ON for an index in SQL Server using c#?

I need help with programmatically setting the STATISTICS_INCREMENTAL option ON for an index in a table in SQL Server (2016 SP2). I am using Microsoft.SqlServer.Smo, Version=13.0.0.0. I do not see any ...
DBK's user avatar
  • 378
0 votes
1 answer
2k views

Index vs Statistics [duplicate]

In SQL Server, when to create statistics only on a table column instead of index and when create to index (which creates statistics as well automatically)? I mean both improve query performance then ...
Ahmed Mustafa's user avatar
1 vote
0 answers
501 views

Make use of PostgreSQL 10 multi-column statistics when planning index scans

I'm using PostgreSQL 10 and am trying to optimize query performance on a large table (~70M rows). The query looks like: SELECT * FROM large_table -- many joins -- WHERE col_3 = 'particular date'::...
Justinas Marozas's user avatar
3 votes
1 answer
2k views

Updating statistics, what about existing execution plans

If you update the statistics on tables that have never been updated (don't ask why they never have), does SQL Server automatically refresh the execution plans based on the outdated statistics? If not, ...
Mazhar's user avatar
  • 793
3 votes
1 answer
127 views

Does SQL Server use (unused)index stats for optimizer

I am wondering about unused index in MS SQL Server. By the Index usage DMV I can identify an index which has not been used for seeks, scans or lookups. However I know from Oracle that an index might ...
eckes's user avatar
  • 1,456
3 votes
2 answers
1k views

Query Performance | Update Statistics | Ola Hallengren SPs

I recently read or listened to something Tara Kizer said about developers coming to her on timeouts or slow running queries, and the resolution was usually updating stats. I think she went on to say ...
user avatar
0 votes
1 answer
184 views

Reclaiming unused indexes disk space

Are there any hints, stats or alike available to check thoroughly which indexes can be safely deleted from a PostgreSQL 9.4.9 database? Our database keeps growing and growing and, we think there might ...
Gonzalo Vasquez's user avatar
1 vote
1 answer
163 views

Wrong no of actual rows and why did statistics update help here?

The following simple query ran into timeouts several times during the last days: SELECT Object1.*, Object2.Column1, Object2.Column2 AS Column3 ...
Magier's user avatar
  • 4,777
4 votes
1 answer
930 views

Multi-expression indexes in PostgreSQL not using being used as expected

I have a PostgreSQL 9.3 table with multiple expression indexes defined. When I use explain to see which indexes are being used for my query, I am surprised to see that PostgreSQL is not using the ...
Jake Feasel's user avatar
2 votes
1 answer
1k views

stats_date is null [duplicate]

If I run this query on our system: SELECT STATS_DATE(t.object_id, s.stats_id) AS stdate, * from sys.stats s WITH (NOLOCK) INNER JOIN sys.tables t WITH (NOLOCK) ON t.object_id = s.object_id WHERE ...
Henrik Staun Poulsen's user avatar
7 votes
1 answer
191 views

Understanding SQL Server statistics better

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have ...
SQL Hammer's user avatar
3 votes
1 answer
957 views

Auto created Statistics vs Manually created Statistics

Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement. Will the manually created statistics get ...
Pரதீப்'s user avatar
3 votes
1 answer
74 views

Wrong estimated number of rows using TOP condition

I guess this is a very basic question but I can't get my head around it.. I have perfect statistics in place for a column but while using a TOP condition the estimated number of rows are always a ...
Tiago's user avatar
  • 279
14 votes
4 answers
16k views

Why set Auto Update Statistics to False?

I've just inherited about 20 instances of SQL Server, as part of a wider acquisition project. I'm in the process of assessing performance and I don't like the way maintenance plans have been ...
Molenpad's user avatar
  • 1,784
11 votes
1 answer
3k views

How to reset statistics after UPDATE STATISTICS ... WITH ROWCOUNT

For query tuning and testing purposes, you can manually assign a rowcount and pagecount to a table's index statistics by running UPDATE STATISTICS. But how do you recompute/reset the statistics to the ...
Daniel Hutmacher's user avatar
3 votes
1 answer
106 views

calculating the total number of rows in a table - fails when it is a heap - is there any fix?

I have a procedure that I use to find out how big is a table. select * from sys.objects where name = 'WMS_SLALoad' -- to get the object_id of my table -- in this case it is 2041058307 SELECT o....
Marcello Miorelli's user avatar
3 votes
2 answers
288 views

What data type are the numbers in a SQL Server statistics object?

Specifically I'm most interested in: The 'All density' number in the density vector Numbers in the histogram E.g. are they floats / decimals? What precision / scale / length do they have? The ...
alksdjg's user avatar
  • 173
44 votes
3 answers
25k views

When is it better to create STATISTICS instead of creating an Index?

I have found plenty of information on what STATISTICS are: how they are maintained, how they can be created manually or automatically from queries or indexes, and so on. But, I have been unable to ...
Solomon Rutzky's user avatar
1 vote
1 answer
3k views

Is my postgres index being used?

I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used. select * from pg_stat_all_indexes where ...
Amandasaurus's user avatar
0 votes
1 answer
164 views

Can inserting new rows make the RDB not use an index?

Say I have a table with 10000 records. I then delete them and create them again. This is done with "normal" sql, row by row (not some sort of bulk insert (BCP in sqlserver lingua)). Does the RDB(...
LosManos's user avatar
  • 115
7 votes
1 answer
2k views

Ola Hallengren Index Script not Reindexing

First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on. I have Ola's script set as @Databases nvarchar(max) ,@...
jbkelly's user avatar
  • 93
3 votes
1 answer
157 views

Why do Statistic recommendations disappear from DB Tuning Advisor after implementing recommended indexes?

Why is it, when analyzing a query through Database Engine Tuning Advisor, statistic recommendations disappear after some or all of the suggested indexes are implemented? For example, Query X suggests ...
A Fisher's user avatar
2 votes
2 answers
434 views

Statistics that are neither auto_created nor user_created

So far, I was under the impression that there are two types of statistics objects in SQL Server: automatically-created statistics, which are created by SQL Server on index columns or single columns ...
Heinzi's user avatar
  • 3,155
2 votes
1 answer
367 views

Auto update stats turned off for a user database on SQL Server

While trying to improve the performance of few queries, we found via execution plan that there were lot of index / clustered index seeks. Therefore: First thing we did, was to check our Re-indexing ...
KASQLDBA's user avatar
  • 7,149
12 votes
1 answer
2k views

statistics are up to date, but estimate is incorrect

When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698: For this query: SELECT * FROM Reports_Documents WHERE ReportID = 18698 ...
user1151923's user avatar
1 vote
0 answers
722 views

Rebuild/Re-organize index and Update statistics in SQL server

For many of the sql servers we have seen that the SQL agent Maintenance job first runs the re-indexing and later as a second step it update stats with full scan. I want to figure out if auto update ...
KASQLDBA's user avatar
  • 7,149
3 votes
3 answers
8k views

Update statistics for all databases all tables in a server

Hi I have been working on a script to update statistics of all tables on all my DBs. The idea is to parametrise it later on, but just as a quick fix, and not wanting to implement Ola's scripts today, ...
Marcello Miorelli's user avatar
13 votes
2 answers
12k views

How to tell which indexes are not being used

Postgresql 9.3 Debian 7 I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they ...
Ivan De Sousa Paz's user avatar
1 vote
0 answers
30 views

MySQL: index statistics dropped when table dropped?

When a table is dropped, are its index statistics also removed? The reason I ask is that I was hoping to 'prep' the stats by running log playback via the percona-playback tool prior to cutting over ...
hoffmanc's user avatar
  • 111
10 votes
1 answer
699 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
Mark Wilkinson's user avatar
11 votes
1 answer
13k views

What's the impact of running sp_updatestats in production server?

Is it safe to run sp_updatestats on an SQL Server in the production environment? Or rather, what is the impact of updating all statistics on a sql server? Can it "choke" the sql server while it runs ...
Torbjörn Hansson's user avatar
1 vote
2 answers
348 views

For merge replication, do I need to schedule index maintenance on all sites or just publisher?

I have a very fragmented database that uses merge replication with remote sites. Do I need to schedule index maintenance (reorganize / rebuild) for all sites? Or just on publisher copy?
Dan Sorensen's user avatar
2 votes
3 answers
1k views

how to trigger a statistics update

I have a certain table in my OLTP database, which is bulk-updated by several users. There is no way to know when they will update the table (somewhere between 5 times a day and once in a week). The ...
Roi Gavish's user avatar
  • 1,330
2 votes
1 answer
864 views

Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

We are using Oracle 11g, 11.2.0.3. We know a record exists in a table but a select is not returning it for some odd reason. The execution plans the general query, not forcing any index, shows that ...
Jean-Pierre Bélanger's user avatar