Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [index-statistics]

65 questions
47
votes
3 answers

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 find any guidance or "best practices" information…
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
19
votes
3 answers

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?
Simon
  • 345
  • 1
  • 2
  • 7
16
votes
3 answers

From where does the MySQL Query Optimizer read index statistics?

I'm trying to determine from where the MySQL optimizer obtains the list of indexes that are available for a table when it estimates the cost of (prepares) a query from.
ivotron
  • 533
  • 3
  • 6
  • 11
15
votes
2 answers

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 are not used at all. Is there any usage statistics…
Ivan De Sousa Paz
  • 561
  • 2
  • 9
  • 16
15
votes
4 answers

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 implemented. I'm seeing daily blanket index rebuilds (I…
Molenpad
  • 1,814
  • 2
  • 23
  • 41
13
votes
7 answers

Reasons for disabling statistics auto update?

I just learned that a client company I work for has decided to keep the auto update statistics options off for some of their SQL Servers, and the DBAs manually troubleshooting performance issues when they arise. However, this kind of does of not…
Alpha
  • 295
  • 3
  • 10
12
votes
1 answer

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 option (recompile) I get a query plan that makes a…
user1151923
  • 223
  • 1
  • 6
12
votes
1 answer

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 table's actual contents? --- Create a…
Daniel Hutmacher
  • 9,173
  • 1
  • 27
  • 52
12
votes
3 answers

MySQL status variable Handler_read_rnd_next is growing a lot

In MYSQL status, Handler_read_rnd_next value is very high. I am aware that, this value will be incremented when a query is executed which is not having proper indexes. But, even when we execute show status like 'Handler_read_rnd_next', this value…
Phanindra
  • 1,007
  • 5
  • 15
  • 31
11
votes
1 answer

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 and cause timeouts or other issues for the users?
Torbjörn Hansson
  • 255
  • 1
  • 2
  • 9
10
votes
1 answer

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 a statistics update with FULLSCAN. When it slows…
Mark Wilkinson
  • 1,245
  • 8
  • 17
10
votes
5 answers

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
  • 203
  • 1
  • 2
  • 6
7
votes
1 answer

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 (abridged) RANGE_HI_KEY …
SE1986
  • 2,142
  • 4
  • 30
  • 61
7
votes
1 answer

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 more buckets given the quantity of records and…
SQL Hammer
  • 925
  • 11
  • 20
7
votes
1 answer

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) ,@FragmentationLow nvarchar(max) = null ,@FragmentationMedium…
jbkelly
  • 93
  • 7
1
2 3 4 5