Questions tagged [index-statistics]
The index-statistics tag has no usage guidance.
64
questions
2
votes
0
answers
17
views
SQL Server Primary key Column 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 (...
- 1,749
1
vote
1
answer
44
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 ...
- 113
0
votes
0
answers
42
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 ...
- 220
0
votes
1
answer
30
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?
- 121
-1
votes
1
answer
53
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
...
- 1
4
votes
2
answers
843
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 ...
- 127
-4
votes
1
answer
35
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 ...
- 3,153
-1
votes
1
answer
51
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,
...
- 2,153
1
vote
2
answers
267
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 ...
- 11
1
vote
2
answers
610
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 ...
- 13
3
votes
1
answer
109
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 ...
- 163
0
votes
2
answers
377
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
1
vote
0
answers
30
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 ...
- 183
7
votes
5
answers
8k
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.
- 173
2
votes
2
answers
1k
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, ...
- 143
2
votes
0
answers
58
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 ...
- 368
0
votes
1
answer
1k
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
0
answers
458
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'::...
- 111
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, ...
- 783
3
votes
1
answer
120
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 ...
- 1,428
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 ...
user125137
0
votes
1
answer
157
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 ...
- 933
1
vote
1
answer
158
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 ...
- 4,697
3
votes
1
answer
709
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 ...
- 587
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 ...
- 2,153
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 ...
- 915
3
votes
1
answer
790
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 ...
- 1,349
3
votes
1
answer
71
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 ...
- 279
13
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 ...
- 1,774
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 ...
- 8,965
3
votes
1
answer
104
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....
- 15.4k
3
votes
2
answers
272
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 ...
- 173
44
votes
3
answers
23k
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 ...
- 67.3k
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 ...
- 807
0
votes
1
answer
153
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(...
- 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)
,@...
- 93
3
votes
1
answer
136
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 ...
- 31
2
votes
2
answers
383
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 ...
- 3,145
2
votes
1
answer
330
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 ...
- 7,069
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 ...
- 223
1
vote
0
answers
575
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 ...
- 7,069
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, ...
- 15.4k
7
votes
2
answers
11k
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
0
answers
29
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 ...
- 111
10
votes
1
answer
686
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 ...
- 1,225
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 ...
- 255
1
vote
2
answers
334
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?
- 199
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 ...
- 1,310
2
votes
1
answer
856
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
1
answer
60
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 ...
- 195