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
10
votes
1answer
7k 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 ...
6
votes
3answers
2k views

How to update statistics for a database's system tables

We recently upgraded many of our instances to 2016. As a result, a SELECT statement from sqlpackage.exe is timing out on some instances. After some testing, I found that by updating the statistics on ...
2
votes
1answer
293 views

Recompile not working for DELETE statement

I want to optimize a deletion from my tables. I have a table-variable input for my usp of following type: CREATE TYPE [AddressInfoParts] AS TABLE( ( [TcpInfoId] [BIGINT] NULL INDEX ...
1
vote
0answers
138 views

Logical read when use Offset in SQL

When I execute this script SET STATISTICS IO ON SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] FROM vwTblTest WHERE ([Factor] >= 139501 AND [Factor] < ...
5
votes
1answer
95 views

Retain column statistics on paragraph-style columns?

Specific to Microsoft SQL Server... If a query has a search predicate against a paragraph-style text column, it will create a system-created statistic to answer the query. Got it. My question is if ...
1
vote
0answers
61 views

NuoDB Statistics

I'm currently doing a research using a TPC-H table and I'm trying to get the running time for 3 queries in NuoDB: SELECT * FROM LINEITEM SELECT * FROM LINEITEM WHERE L_PARTKEY BETWEEN 1 AND 80000 ...
7
votes
1answer
154 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 ...
3
votes
1answer
526 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 ...
3
votes
1answer
1k views

Disable auto stats in SQL Server

I have couple questions about auto stats in SQL Server: I know I can use the following statement to check if the database auto stats is disabled, but is there a statement to check if a specific index'...
2
votes
1answer
113 views

Is it worth to optimize queries with EarlyTermination Timeout?

Right after running Ola's IndexOptimize to rebuild all indexes and statistics, I cleared the data and plan cache and ran a very complex ugly query to get a baseline of it's performance. The query ...
1
vote
1answer
141 views

are overlapping multi column statistics redundant?

I have run a database engine tuning advisor session, DTA, to optimize my database. The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example ...
1
vote
1answer
310 views

In what circumstances I am more likely to benefit from auto update stats async on?

I update statistics on a weekly basis using Ola Hallengren's solution. Based on the article below, I am considering enabling the Auto Stats Async and turning on trace flag 2371. Databases With ...
0
votes
1answer
415 views

How to list the missing multicolumn statistics?

I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases. It produced a list of recommendations, mostly multicolumn statistics. These ...
-2
votes
1answer
49 views

Generate and fetch table statistics [closed]

I am trying to generate statistics for my tables. I am doing it with the following command: exec dbms_stats.gather_table_stats(USER, 'tab_name', cascade => TRUE); Then I am trying to fetch these ...
2
votes
2answers
839 views

Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases. I have then passed this .trc trace file as a parameter to the database ...
4
votes
1answer
97 views

Verify the importance of statistics? Database with auto-create stats works?

Working on SQL Server 2005, I want to use the database engine tuning advisor to help me tune my webserver. First thing I do is to create a server side profile trace and run it for about an hour. ...
4
votes
2answers
1k views

Statistics for AlwaysOn Availability DBs

Can you please help me understand why there is a need for SQL Server to maintain statistics in tempdb for DBs that are part of an Always On Availability Group? Isn't "everything" that happens in the ...
10
votes
3answers
10k 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 ...
5
votes
3answers
706 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 ...
10
votes
2answers
424 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 ...
0
votes
1answer
68 views

Find all stats that were sampled

I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We ...
5
votes
1answer
2k views

SELECT very slow when no results and a LIMIT is specified

I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified. If the number of results is ...
4
votes
1answer
688 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 ...
3
votes
1answer
420 views

Optimizer gets the wrong number of estimated rows even with updated stats [closed]

I have a procedure running in SQL Server 2008 that contains a query similar to the one below; SELECT /* various columns from table 1 and table 2 */ FROM dbo.tbl_1 AS a INNER JOIN dbo.tbl_2 AS b ON a....
5
votes
1answer
144 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 ...
3
votes
2answers
2k views

How inaccurate is sys.dm_db_partition_stats?

I am looking at ways to count rows in several tables on several databases. In this particular case I am looking for unused and abandoned items. Several sources source1, source2 recommend using sys....
1
vote
1answer
1k views

How do I get Azure SQL query history/statistics/performance?

I'm migrating away from Azure SQL and my new cloud hosting service wants stats like "estimated inserts per hour on average", traffic/bandwidth usage per month, or "average read/writes on table x per ...
11
votes
1answer
342 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 ...
2
votes
1answer
149 views

Data impact on analyzing mysql table stats

I have a MySQL database that appears to be getting slower over time. After some googling, I determine that the database probably needs to be analyzed. This is because innodb_stats_on_metadata is set ...
13
votes
1answer
846 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] [...
6
votes
1answer
743 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 ...
1
vote
3answers
312 views

What procedure can be used to change system statistics in a production environment

System statistics aren't required but highly recommended by Oracle. From my own experience, it produced a huge improvement on the total user IO waits of our production environment when first ...
0
votes
0answers
918 views

Large differences between estimated and actual row counts but stats are OK

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators ...
4
votes
2answers
1k views

What parts make up pg_database_size?

I'm trying to write a munin plugin to graph DB sizes. Alongside using pg_database_size I want to graph the components thereof as well. So far, I've come up with the following: SELECT SUM(...
1
vote
1answer
53 views

Understanding rows count computation

PostgreSQL 9.4 I have a table called customers which has a column income integer. After runnig ANALYZE against it I got the following statistic for the column: most_common_vals {20000,80000,...
0
votes
1answer
366 views

Database modelling : Creating a model for restaurant application which gives statistics

I am working on a server-side application in which users will be able to give ratings and comments to different factors of a 'Restaurant'. What the webapp does is takes all the ratings and creates ...
38
votes
3answers
17k 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 ...
2
votes
1answer
32 views

what monitor before applying changes on the server? - looking for a basic answer - starting point only

these settings were decision of our development team: there are 2 settings: 1 - max degree of parallelism 2 - optimize for ad hoc workloads On OUR_DEV_SERVER: 1. Run the following script: --//...
3
votes
0answers
906 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: http://www.sqlservercentral....
2
votes
2answers
122 views

Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records. Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were ...
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 ...
3
votes
4answers
3k views

Which version of SQL Server 2016 supports R

Microsoft buying REvolution results (amongst other things) in the integration of R in SQL Server 2016. Looking through the features supported by the SQL Server 2016 I cannot find R-Support. Does ...
1
vote
0answers
66 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
2
votes
2answers
266 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 ...
8
votes
0answers
136 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 ...
1
vote
1answer
59 views

Statistics needs to be captured for query analysis

What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for: Query elapsed time Logical read Logical write Physical read Physical ...
7
votes
1answer
3k 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 ...
1
vote
1answer
2k views

Count number of duplicate rows, sort from most to least (statistic) [closed]

I'm trying to make statistic of top-10 most visited URL. I have one simple table. For each URL visit, one row added with that URL as content. Most visited URL has the most number of duplicate. I've ...
1
vote
1answer
66 views

SQL Server statistics

I have run the Database Tuning Advisor and it has suggested that some statistics are created. Whilst this is great and I understand the purpose of statistics, when I have reviewed them it has created ...
22
votes
3answers
18k 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 ...