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
47
votes
2answers
48k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
47
votes
2answers
103k views

When To Update Statistics?

I've inherited a Maintenance Plans that does the following: Cleanup old data Checks DB integrity Performs Database and Transaction Log Backups Reorganizes Our indexes Updates Statistics Delete old ...
41
votes
3answers
20k 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 ...
28
votes
1answer
4k views

Where are Statistics physically stored in SQL Server?

Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool? More specifically, is there a way to figure out the pages used by ...
28
votes
1answer
860 views

Is sys.stats_columns incorrect?

Let's say I have a table Foo with columns ID1, ID2 and a composite primary key defined over ID2, ID1. (I'm currently working with a System Center product that has several tables defined this way with ...
27
votes
3answers
24k views

Warning for missing statistics in execution plan

I have a situation that I can't understand. My SQL Server execution plan tells me that I have missing statistics on the table, but the statistics are already created: But if we look at the table, we ...
25
votes
3answers
624 views

Strange behaviour with sample sizes for statistics updates

I've been playing around investigating sampling thresholds with statistics updates on SQL Server (2012) and noticed some curious behaviour. Basically the number of rows sampled seems to vary under ...
24
votes
2answers
2k views

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 ...
21
votes
1answer
1k views

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the ...
20
votes
2answers
46k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
19
votes
3answers
4k views

Is there a reason to update statistics manually?

In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?
18
votes
2answers
35k views

What are database statistics, and how can I benefit from them?

I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?
18
votes
1answer
663 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 ...
17
votes
1answer
8k 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 ...
15
votes
1answer
2k views

How does SQL Server know predicates are correlated?

While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article: ...
15
votes
1answer
437 views

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
14
votes
1answer
11k views

SQL Server Index vs Statistic

What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
14
votes
3answers
5k views

Parallel Statistics Update

In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don'...
14
votes
1answer
660 views

Unique index updates and statistics row modification counters

Given the following table, unique clustered index, and statistics: CREATE TABLE dbo.Banana ( pk integer NOT NULL, c1 char(1) NOT NULL, c2 char(1) NOT NULL ); CREATE UNIQUE CLUSTERED ...
14
votes
1answer
363 views

SQL Server recreating plans each day

We have this problem in our production environment. Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) - Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1). SQL Server ...
14
votes
2answers
648 views

stats_column_id and index_column_id do not update with physical order of clustered index is changed

Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the ...
13
votes
1answer
1k views

Statistics. Are multicolumn histograms possible?

I am thinking of a situation where I have two columns with high density but these columns are not independent. Definition Here it is the definition of the table that I have created for testing ...
12
votes
1answer
9k views

What is the default sample size of statistics in SQL Server?

From MSDN: When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimiser samples the data and computes the sample size by default. How to identify default ...
12
votes
1answer
3k views

Should I disable “auto update statistics” in a data warehousing scenario?

I have 200 GB data warehouse in SQL Server. I have been experiencing really slow execution times for some queries; for example 12 hours for a simple delete query with an inner join. After doing ...
12
votes
1answer
927 views

Why does SQL Server refuse to update these statistics with anything but fullscan?

I noticed a relatively long running (20 min+) auto update statistics operation in a daily datawarehouse build. The table involved is CREATE TABLE [dbo].[factWebAnalytics]( [WebAnalyticsId] [...
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 ...
11
votes
1answer
2k views

How does sampling work when updating statistics?

I have several massive tables. I'd like to ensure their statistics are up to date via a weekly maintenance plan. However, doing so is taking too much time. If I specify WITH SAMPLE 50 PERCENT ...
11
votes
1answer
3k 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? ...
11
votes
2answers
516 views

SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates. We have a large table partitioned by date with a couple of ...
11
votes
2answers
936 views

Never ending Query Store search

I'll say from the beginning that my question/problem looks similar to this previous one, but since I'm not sure if the cause or the starting info is the same, I decided to post my question with some ...
11
votes
2answers
3k views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
11
votes
1answer
11k 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 ...
11
votes
1answer
372 views

Why can my index seek estimate the right number of rows and the sort operator can't?

I've a query that uses a function on the predicate, something like this: commentType = 'EL' AND commentDateTime >= DATEADD(month,datediff(month,0,getdate()) - 13,0) I have a filtered index on ...
10
votes
3answers
3k views

Are SQL Server statistics stored in database or buffer pool?

Just wondering are statistics kept in the database but not in the memory? If I backup/restore the database from a prod server to a development server, would it keep the same statistics so that ...
10
votes
3answers
12k 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 ...
10
votes
2answers
3k views

DTA Recommends to CREATE STATISTICS

I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file. My question is, how exactly ...
10
votes
1answer
1k views

Why does SQL Server not do compound column statistics histograms?

SQL Server has a thing called "multi-column statistics", but it's not what one think it would mean. Let's take a look at the following sample table: CREATE TABLE BadStatistics ( IsArchived BIT ...
10
votes
1answer
497 views

Cardinality estimation on tables with large cardinality variance between keys

Context I have stumbled on an issue in SQL Azure where the Sort-operator is spilling over into TempDB due to poor row estimates. We are querying a master table that is joined with a number of detail ...
9
votes
5answers
54k views

SQL Select taking too much time to execute

It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table. In code: SELECT TempTable.Col1, ...
9
votes
1answer
7k views

Fast general method to calculate percentiles

I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile. An obvious solution is to count and sort the column and then do a ...
9
votes
1answer
802 views

Why is the auto created statistic on this column empty?

Info My question relates to a moderately big table (~40GB data space) that is a heap (Unfortunately, I am not allowed to add a clustered index to the table by the application owners) An auto created ...
9
votes
1answer
3k 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. ...
9
votes
1answer
4k views

Does VACUUM FULL need ANALYZE

I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this. I'm ...
9
votes
1answer
2k views

Medians, Modes, Percentiles and OLAP

I'm newbie trying to wrap my head around OLAP, and I have a few questions. Question 1: Can an OLAP cube store medians, modes, percentiles? Question 2: Can an user-written MDX query return a summary ...
9
votes
1answer
7k views

Advisability of using STATISTICS_NORECOMPUTE

I've recently become involved in maintaining a set of databases with some interesting index problems. One of the ones that aggravates me the most is the differences in the indexes between development,...
9
votes
1answer
212 views

Statistics disappearing/empty randomly throughout the day

I've got a SQL Server 2017 (CU9) database that is exhibiting some performance related issues that I believe have to do with the index statistics. While troubleshooting I discovered that the ...
9
votes
1answer
4k views

Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time? [closed]

I am running an Azure SQL database under the S2 edition (50 DTUs). Normal use of the server usually hangs around 10% DTU. However, this server regularly gets into a state where it will send DTU usage ...
8
votes
3answers
3k views

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
8
votes
2answers
265 views

Inability to query underlying table of a schema-bound view

I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE ...
8
votes
1answer
3k views

Bad execution plan after stats update due to temp table

A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters. Issue seems to come from a ...

1
2 3 4 5
7