Questions tagged [index-statistics]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-1
votes
1answer
19 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, ...
0
votes
0answers
25 views

Two identical statistics in MS SQL [duplicate]

Why does the table have two identical statistics? The first is a consequence of the nonclustered index, the second is created by the server. The histograms are exactly the same. Only diference is that ...
1
vote
2answers
74 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 ...
1
vote
2answers
219 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 ...
3
votes
1answer
86 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 ...
0
votes
2answers
114 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. ...
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 ...
5
votes
5answers
794 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.
1
vote
2answers
474 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, ...
2
votes
0answers
50 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 ...
0
votes
1answer
835 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 ...
1
vote
0answers
396 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'::...
3
votes
1answer
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, ...
3
votes
1answer
103 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 ...
3
votes
2answers
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 ...
0
votes
1answer
110 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 ...
1
vote
1answer
145 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 ...
3
votes
1answer
553 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 ...
2
votes
1answer
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 ...
7
votes
1answer
172 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 ...
3
votes
1answer
680 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 ...
3
votes
1answer
65 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 ...
11
votes
4answers
13k 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 ...
11
votes
1answer
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 ...
3
votes
1answer
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....
3
votes
2answers
250 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 ...
41
votes
3answers
21k 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 ...
1
vote
1answer
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 ...
0
votes
1answer
141 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(...
7
votes
1answer
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) ,@...
3
votes
1answer
100 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 ...
2
votes
2answers
337 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 ...
2
votes
1answer
279 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 ...
12
votes
1answer
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 ...
1
vote
0answers
420 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 ...
3
votes
3answers
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, ...
7
votes
2answers
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 ...
1
vote
0answers
26 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 ...
10
votes
1answer
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 ...
11
votes
1answer
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 ...
1
vote
2answers
331 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?
2
votes
3answers
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 ...
2
votes
1answer
846 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 ...
3
votes
1answer
58 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 ...
3
votes
1answer
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 ...
4
votes
1answer
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 ...
1
vote
2answers
99 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 ...
4
votes
2answers
213 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 ...
4
votes
1answer
670 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 ...
14
votes
3answers
7k 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?