Questions tagged [index-statistics]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-4 votes
1 answer
26 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 ...
user avatar
  • 2,116
-1 votes
1 answer
24 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, ...
user avatar
1 vote
2 answers
135 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 ...
user avatar
1 vote
2 answers
348 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 ...
user avatar
  • 13
3 votes
1 answer
88 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 ...
user avatar
  • 163
0 votes
2 answers
181 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. ...
user avatar
1 vote
0 answers
29 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 ...
user avatar
5 votes
5 answers
3k 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.
user avatar
1 vote
2 answers
670 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, ...
user avatar
  • 133
2 votes
0 answers
53 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 ...
user avatar
  • 358
0 votes
1 answer
993 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 ...
user avatar
1 vote
0 answers
423 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'::...
user avatar
3 votes
1 answer
1k 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, ...
user avatar
  • 773
3 votes
1 answer
107 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 ...
user avatar
  • 1,408
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
118 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 ...
user avatar
1 vote
1 answer
151 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 ...
user avatar
  • 4,595
3 votes
1 answer
606 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 ...
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 ...
user avatar
7 votes
1 answer
180 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 ...
user avatar
3 votes
1 answer
708 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 ...
user avatar
3 votes
1 answer
68 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 ...
user avatar
  • 279
12 votes
4 answers
14k 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 ...
user avatar
  • 1,714
11 votes
1 answer
2k 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 ...
user avatar
3 votes
1 answer
98 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....
user avatar
3 votes
2 answers
263 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 ...
user avatar
  • 173
43 votes
3 answers
22k 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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
0 votes
1 answer
144 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(...
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) ,@...
user avatar
  • 93
3 votes
1 answer
112 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 ...
user avatar
2 votes
2 answers
351 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 ...
user avatar
  • 3,135
2 votes
1 answer
293 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 ...
user avatar
  • 6,950
12 votes
1 answer
1k 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 ...
user avatar
1 vote
0 answers
450 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 ...
user avatar
  • 6,950
3 votes
3 answers
7k 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, ...
user avatar
7 votes
2 answers
9k 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 ...
user avatar
1 vote
0 answers
27 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 ...
user avatar
  • 111
10 votes
1 answer
682 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 ...
user avatar
11 votes
1 answer
12k 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 ...
user avatar
1 vote
2 answers
332 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?
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 ...
user avatar
  • 1,272
2 votes
1 answer
850 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 ...
user avatar
3 votes
1 answer
59 views

SP with Encryption Read/Writes Stats

If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am ...
user avatar
  • 195
3 votes
1 answer
1k views

Query requires 'Update statistics' very often in SQL Server

We have a few tables in our database. Since most of our queries are date based, we have constructed clustered index on 'date + primary key (surrogate)' combination and we enforce joins on date as well ...
user avatar
4 votes
1 answer
102 views

Index Evaluation

I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index. The non ...
user avatar
  • 195
1 vote
2 answers
102 views

Indexing is not working

Following is the table and index information for which I have the queries at the end. Create Table: CREATE TABLE `food` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `page_no` int(11) DEFAULT ...
user avatar
4 votes
2 answers
220 views

What is a good guideline for sampling for statistics updates

Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent. Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most ...
user avatar
  • 4,268
4 votes
1 answer
679 views

Check Index Fragmentation on SQL Server Audit Queues

I've read this excellent article, http://rusanu.com/2010/03/09/dealing-with-large-queues/, and seem to be having the same issue, due to a low space issue, the service broker was turned off on a ...
user avatar
  • 93
15 votes
3 answers
8k views

Difference between idx_tup_read and idx_tup_fetch on Postgres

On Postgres 8.4 when you do: select * from pg_stat_all_indexes where relname = 'table_name'; It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?
user avatar
  • 305