The tag has no usage guidance.

learn more… | top users | synonyms

26
votes
1answer
679 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 ...
25
votes
2answers
17k 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 ...
20
votes
3answers
893 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 ...
18
votes
2answers
25k 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
2answers
33k 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 ...
18
votes
1answer
423 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 ...
15
votes
1answer
889 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: ...
14
votes
3answers
3k 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?
13
votes
1answer
310 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 ...
11
votes
1answer
5k views

SQL Server Index vs Statistic

What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
11
votes
2answers
669 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
254 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 ...
10
votes
2answers
7k 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?
9
votes
2answers
2k 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 ...
9
votes
2answers
230 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 ...
9
votes
1answer
5k 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
2answers
2k 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, ...
9
votes
1answer
1k 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 ...
9
votes
1answer
142 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 ...
9
votes
1answer
197 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] [...
8
votes
1answer
1k 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 ...
8
votes
1answer
372 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 ...
8
votes
2answers
2k 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'...
7
votes
3answers
2k 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 ...
7
votes
5answers
11k 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, ...
7
votes
1answer
318 views

Auto Update Statistics Duration and Interruptions

Our production SQL Server 2008 R2 sometimes begins to perform slowly: all queries begin to work too slow (and sometimes all queries just hang and wait something then continue). This usually lasts ...
7
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 ...
7
votes
2answers
1k views

How to calculate cache misses for PostgreSQL

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
7
votes
0answers
129 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently? [duplicate]

This answer motivated think again my following tables but I am still uncertain how to store variables/results from data analysis for quick selection in post-processing. If stored, I think it is well ...
6
votes
1answer
344 views

AUTO_UPDATE_STATISTICS_ASYNC ON?

I have been advised that setting the AUTO_UPDATE_STATISTICS_ASYNC ON may be a good idea. I have been reading a couple of ideas and still not sure as this is a non default setting. Any further advice?...
6
votes
1answer
153 views

What is more appropriate sp_updatestats or UPDATE STATISTICS?

In a system there is a nightly data load process where all the foreign keys are dropped, all tables are truncated, repopulated from an external source, and the foreign keys are put back in place. At ...
6
votes
2answers
607 views

Estimating distribution of row access in mysql

I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can ...
6
votes
1answer
158 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: http://stackoverflow.com/a/...
6
votes
1answer
2k 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,...
6
votes
1answer
1k views

Extremely Slow StatMan query issue with #SP2010 farm

The issue I’m experiencing is explained in a blog-post I created for this, (easy to refer to and to manage the responses / solutions in 1 location) which can be found here: http://schoennie.blogspot....
6
votes
2answers
574 views

What can cause statistics to get out of line?

I've just worked through a problem at a clients site which it turned out was caused by the statistics being wrong which caused the Optimizer to time out. Running exec sp_updatestats fixed the problem ...
5
votes
2answers
2k views

Estimate average and median efficiently in Postgres?

I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which ...
5
votes
2answers
473 views

If a query triggers a statistics update and times out are the statistics still updated?

I have a fulltext query which is usually very fast but may time out when it causes a statistics update since statistics updating is very slow on this database. Usually the query "recovers" to normal ...
5
votes
2answers
590 views

Get NULL statistics for a column in MS SQL Server

In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column? Same information is available in Oracle and PostgreSQL, and I heard that MS ...
5
votes
1answer
232 views

Why is an additional filtered statistic being ignored (EAV schema)?

I'm trying to improve a row estimate for this sub-query (of a larger query). The estimate is showing 1266 rows. The actual is 117k rows. This particular property (EAV schema) only has two values ...
5
votes
2answers
6k views

Frequency distributions segmented by a field

Some background: I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the ...
5
votes
1answer
1k views

Does creating / updating statistics have an impact on the transaction log?

Does creating or updating statistics, assuming on a large table, create significant (or at all) transaction log churn? I would think not as it does not change the underlying data or structure. If it ...
5
votes
3answers
3k views

SQL Server - Varbinary Column - Extremely Slow Statistics Update

I have a table in my database that we use as a filestore, the file itself is stored in a varbinary column, which all seemed to work well until recently, we noticed one of our instances of this table ...
5
votes
3answers
165 views

Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4. After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad ...
5
votes
1answer
41 views

Statistics on filestream column

When I try to create a statistics on a filestream column, I get the following error message: Column 'MyColumn' in table 'MyTable' is of a type that is invalid for use as a key column in an index ...
5
votes
1answer
592 views

New query slower even if it has less logical reads than the old one

I have a query in a stored procedure that must list all dates in a range and JOIN on it date from a table, if it exists for that day. The procedure was created before I was employed as the DBA, so I ...
4
votes
2answers
1k views

Performance degradation after rebuilding indexes

I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
4
votes
2answers
1k views

How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

I'm working on a project that must support two database engines; SQL Server and PostgreSQL. We are using NHibernate as the ORM. We are running into performance issues with certain queries. Using ...
4
votes
1answer
5k 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 ...
4
votes
1answer
68 views

Incremental statistics: find out which partitions have been resampled

TL;DR: Is it possible to find out which partition(s) have been resampled and which have not when incremental statistics are used? Platform is SQL Server 2014 Enterprise edition. The long version with ...