Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

The query optimizer uses statistics to create query plans that improve query performance. For most queries, the query optimizer already generates the necessary statistics for a high quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results. This topic discusses statistics concepts and provides guidelines for using query optimization statistics effectively.

More information can be found on Books online: Statistics

416 questions
53
votes
2 answers

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 arguing both that it does and that it doesn't.
Thor Erik
  • 645
  • 1
  • 5
  • 6
48
votes
2 answers

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 backups and Maintenance Plan files Of the 23…
Onion-Knight
  • 1,089
  • 2
  • 10
  • 15
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
39
votes
3 answers

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 will see that there is a statistic that has been…
Artashes Khachatryan
  • 1,533
  • 1
  • 12
  • 23
29
votes
1 answer

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 the primary key columns listed in the opposite…
James Lupolt
  • 4,278
  • 5
  • 31
  • 46
28
votes
1 answer

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 statistics using DMVs and/or DBCC? I own both SQL Server…
ivanmp
  • 2,323
  • 3
  • 24
  • 36
25
votes
3 answers

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 some circumstances - even with the same set of data.…
Matthew McGiffen
  • 552
  • 6
  • 11
25
votes
4 answers

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?
DForck42
  • 3,068
  • 3
  • 38
  • 67
25
votes
2 answers

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 the query optimiser doesn't have to resort to…
Fza
  • 652
  • 1
  • 9
  • 19
22
votes
2 answers

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 primary key as opposed to a statistics histogram on…
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
21
votes
1 answer

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 statistics disappear after the index is rebuilt. Below…
JasonR
  • 313
  • 1
  • 7
20
votes
2 answers

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?
goric
  • 1,646
  • 3
  • 18
  • 25
20
votes
3 answers

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?
jrara
  • 5,393
  • 20
  • 58
  • 65
17
votes
1 answer

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 a lot of queries. UPDATE STATISTICS on all tables…
Professional Sounding Name
  • 617
  • 5
  • 13
16
votes
1 answer

SQL Server Index vs Statistic

What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
Scott
  • 667
  • 4
  • 8
  • 15
1
2 3
…
27 28