We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [statistics]

Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view.

Filter by
Sorted by
Tagged with
5
votes
2answers
1k views

Update statistics on sys.objects

I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on. However, this question in ...
1
vote
1answer
162 views

Index Reorgs Blocking Statistics Updates Blocking Inserts

After upgrading from SQL Server 2014 to SQL Server 2016 last month, we are seeing statistics updates on a table being blocked for hours while a table’s indexes are being reorganized. Inserts are in ...
15
votes
1answer
6k views

SQL Server 2016 Bad Query Plan locks up DB once a week

Once a week, for the past 5 weeks, around the same time of day (early morning, may be based on user activity when people are starting to use it), SQL Server 2016 (AWS RDS, mirrored) starts timing out ...
-1
votes
1answer
315 views

How to get user Insert statistics in MySQL

I'm trying to get statistics on the write load on my MySQL servers. Something like what show global status like "Com_%"; gives me. but, here comes the catch - we're using Master-Master replication. ...
8
votes
1answer
215 views

Bizarre density results in sampled statistics

A NC-index gets a totally different statistical distribution when estimated with sampling vs fullscan; the sampled one having a bizarre density vector. This results in poor execution plans. I have a ...
6
votes
1answer
1k views

Inconsistent statistics on jsonb column with btree index

I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly ...
0
votes
1answer
88 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 ...
5
votes
3answers
176 views

Oracle and MS-SQL-like row counts

My applications often need an accurate but perhaps imperfect row count. Billion-row tables often get frequent writes, so "perfect" is a matter of definition and timing. Microsoft SQL keeps very good ...
8
votes
1answer
2k views

Updating statistics on 18 TB database

I am looking here for experts' advice on how to manage updating stats for very large database approx 18 TB. We have recently started facing performance issues and believe it is due to old stats. ...
2
votes
0answers
72 views

Index statistics are requiring constant updates due to timeouts

We have been experiencing an odd behavior in our application where various modules will begin to timeout in SQL Server 2012. Each time we stumble across this issue, we find that the statistics require ...
1
vote
0answers
1k views

Gather stats - Partitioned Table

Database: Oracle 12c I have a process that selects data from Fact Table, summarizes it and pushes it to Summary Table. Summary table is Range Partitioned (Trade Date) and List Partitioned (File Id). ...
1
vote
1answer
129 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 ...
0
votes
2answers
151 views

How to manipulate the query plan so that I can have more control on memory grants?

Let's say I have a complex query and for this query there are huge Differences between the estimated and actual execution plans. These differences are causing spillage into tempDb as described here. ...
0
votes
2answers
100 views

Statistics impact on db2 optimizer plan

Has the creation of statistics any impact on the estimated costs and the optimizer plan given by db2 optimizer. Or will (in the best case) only the execution time of a query change? I ran something ...
3
votes
1answer
4k views

Wrong estimated number of rows

I'm trying to identify an issue I sometime experience on a stored procedure. I have to say that the stored procedure uses an UDF in the select clause (perhaps that's the cause of the problem) but ...
3
votes
3answers
1k views

Is there a faster way to count JSONB tags?

I'm trying to squeeze some more performance out of this query in Postgres 9.5. I'm running it over 400,000 rows. In playing around with it, I've noticed that the CASE statements are adding quite a ...
10
votes
1answer
2k views

How are the number of Histogram steps decided in Statistics

How are the number of histogram steps decided in Statistics in SQL Server? Why is it restricted to 200 steps even though my key column has more than 200 distinct values? Is there any deciding factor? ...
1
vote
1answer
231 views

Incorrect Statistics EQ_ROWS Causing Parallel Table Scans and Deadlocks

On one customer's system, one of our database's tables has about 123 million rows. This table is a transaction table that is grouped by a batch key. The clustered index is 4 columns: batch key, ...
8
votes
1answer
2k views

Auto Update Stats not updating Statistics

Am using SQL SERVER 2012 I have my Auto Update Stats ON in my database. From the below link I learned that, the Auto Update stats will fire for every SQRT(1000 * Table rows) change in table rows. ...
0
votes
1answer
268 views

When is dm_exec_procedure_stats updated?

I just created a new proc and ran it, but it is not showing up in my query: SELECT schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time ...
6
votes
2answers
470 views

How do I effectively handle very skewed data? Statistics up to date but don't seem to help

I have a table, dbo.ClaimBilling, with 130,000 rows. In this table, the column OperatorID is a varchar(max) and is heavily skewed. 125,000 rows are 'user1', and the remaining 5000 rows are split ...
2
votes
1answer
322 views

AlwaysOn Statistics getting updated on Readonly node immediately after updating primary node with Fullscan

Version: Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on ...
2
votes
1answer
89 views

SQL Server - Do automatically created column statistics get dropped automatically after time?

The retired SQL Server 2000 documentation https://www.microsoft.com/en-us/download/details.aspx?id=51958 states: "The statistics generated for a column can be deleted if you no longer want to ...
5
votes
3answers
322 views

Quickest Method To Invalidate Statistics

I'm doing some testing on sync vs async auto statistics updates. I'd like to quickly invalidate all statistic objects (headers, density vectors, and histograms) to ensure that next time the statistic ...
2
votes
1answer
512 views

What locks are taken during statistic operations in SQL Server?

What locks are taken during the various statistic operations below in SQL Server assuming the default options of auto create stats and auto update stats are set to true? Automatic creation of a ...
18
votes
1answer
607 views

How to prevent statistics creation on a column?

I have a table with a column that I do not want statistics to be created or updated on. I get a better join cardinality estimate if I force the query optimizer to use density of statistics on the ...
1
vote
1answer
183 views

Histogram Skew on Clustered Index

Problem I've been attempting to optimize a query generated from Entity Framework, and noticed that the execution plan shows wildly inaccurate estimations. After a little digging, I noticed that the ...
2
votes
1answer
2k views

MSSQL 2014 “The provided statistics stream is corrupt” Error 9105

I have a database that prevent to see user properties due to this error. I've begin investigating and the problem comes from sys.asymmetric_keys and sys.symmetric_keys system views inside the database....
3
votes
1answer
355 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 ...
1
vote
1answer
99 views

Filtered Stats and Incremental Stats on Partitioned table

We have just implemented table partition on a very large table in SQL Server 2014 , we used have filtered stats on the same table before partition not after the table partition we enabled Incremental ...
1
vote
1answer
43 views

using statistics in order to decide whether or not to add a filtered index

I have many queries on table dbo.tblSAPDespatchItem, some of them have the following condition in the where clause: WHERE b.lngDespatchItemStatusID = 2 AND b.decvalue > 0 for example ...
0
votes
2answers
502 views

Enable Auto Update Stats in Async mode

We have a table with 10 billion records, where daily there are hundreds of thousands of updates/inserts/selects happening. As the auto-update stats was ON there were a few performance issues; hence, ...
5
votes
1answer
2k views

Find which functions are used

I have a legacy PostgreSQL database schema with hundreds of functions. I know that some of them aren't used anymore, and I want to delete them. What is the easiest way to find which procedures are ...
2
votes
1answer
590 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 ...
8
votes
2answers
1k views

Ascending Key Problem - Leading column branded “Stationary” - SQL Server

I've been researching slow running queries in our database, and have concluded that this is a classic Ascending Key Problem. As new rows are inserted almost constantly, and a given piece of SQL to ...
2
votes
1answer
383 views

AUTO_CREATE_STATISTICS set to OFF in tempdb

It has come to my attention that a number of my databases (2008 & 2012) do not have the setting AUTO_CREATE_STATISTICS set to ON for the tempDB. In the Microsoft documentation it is set to ON as ...
24
votes
2answers
2k views

SQL Server - Cardinality Estimate for LIKE operator (Local Variables)

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and ...
2
votes
1answer
118 views

How can I update statistics adding the data of the last day only?

I have been tuning some queries that had a warning on the sort operator, which is generally an expensive operator: Operator used tempdb to spill data during execution with spill level 2 The way to ...
0
votes
1answer
2k views

RUNSTATS vs REORG

What is the major differences between: RUNSTATS ON TABLE 'mytable' WITH DISTRIBUTION AND DETAILED INDEXES ALL REORG INDEXES FOR 'MYTABLE'
6
votes
3answers
6k views

How to script statistics in Sql Server? (using T-SQL)

there is a very similar question here: Create SQL Script for Statistics But that is not what I want. I want to get back exactly what I have put in. For example: CREATE STATISTICS [...
2
votes
2answers
671 views

Why would I need to update stats manually? [duplicate]

I have often found that when I am having performance problems on my MSSQL database, I can resolve them by running sp_UpdateStats. I learned this because that's always the first line of defense from ...
4
votes
4answers
222 views

Considerations for Adding Indexes to a SQL Server Database

I'm a .NET developer. So, I know enough about databases to be dangerous, but do not have the expertise of a proper DBA. We have a slow running stored proc and one of the devs here ran a query ...
0
votes
1answer
32 views

What is the impact of SHOW STATUS, and how often is acceptable to call it?

I am setting up a monitoring thread in my application that should use the SHOW STATUS call of mysql to periodically calculate system / db runtime statistics. Being relatively unfamiliar with datbase ...
1
vote
1answer
120 views

Table statistic vs estimated rows query plan

Can anyone explain why despite having inaccurate table statistics, the actual query plan "estimated number of rows" is correct? E.g table has 30 rows but all table statistics show 50 rows. However a ...
2
votes
1answer
1k views

Despite STATISTICS is updated, Estimated and Actual Row count is not same

I am executing a SELECT statement to retrieve 2 columns with filter on datetime column from a table having 27451373 rows. declare @LastProcessedDate datetime='08-15-2016' select Bill_Code,...
1
vote
1answer
238 views

Update stats takes significantly longer with a 50% sample rates versus a full scan

We have a home grown update stats job for large tables that essentially issues an UPDATE STATS command. Historically we had been defaulting to FULL SCAN, but recently we switched to SAMPLE 50 PERCENT. ...
0
votes
1answer
290 views

Capturing the result(print statements) of SP_UPDATESTATS

I am creating a SQL Server Job to update the statistics once in a week. To create a Job I came up with the below code. USE msdb ; GO EXEC dbo.sp_add_job @job_name = N'Weekly Statistics ...
0
votes
1answer
221 views

How Sql server 2012 join cardinality is calculated?

We have data warehouse based on SQL Server 2012. Often we get poor cardinality estimates for joins impacting downstream operators and slow execution, memory spills, etc. Is there a good article or ...
6
votes
4answers
21k views

sp_updatestats vs Update statistics

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE ...
0
votes
1answer
39 views

Why would SQL Server stop excluding partitions appropriately?

I have a Fact Table that will run with an appropriate plan during our morning processing. Occasionally, later in the day, if I go to query it, it will have a really strange plan. When querying a ...