The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
0answers
7 views

outdated stats or missing records?

I'm using the following query go gather index usage statistics: SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS ...
5
votes
3answers
144 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 ...
0
votes
0answers
6 views

How to derive allowable threshold limits in Netezza

Our vendor sends us a daily extract containing customer data and we load these into our Netezza database daily. Each day, the row counts typically get slightly bigger however it is possible for some ...
0
votes
0answers
20 views

Is there a way to get alerted for stats going stale?

It came to our notice that applications running slow when there is bulk update/insert operation on few tables, randomly on AD-HOC basis. NO fixed date when data will be updated. Thus we see the ...
9
votes
2answers
225 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
22 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 ...
0
votes
0answers
30 views

Group by periods in SQL

Each register is an event in a location with an id, but events take place "in clusters" (e.g zero to two events every day from 31/02/2014 to 10/03/2014, all with the same id). I need to detect the ...
0
votes
0answers
13 views

innotop “Command Summary”

We provide DBaaS. This is innotop C output: We have only 8% read workload? The rest is write? I don't understand this is very uncommon workload for DBaaS? Statistics since last restart? What is ...
3
votes
1answer
56 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 ...
3
votes
1answer
53 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 ...
0
votes
0answers
28 views

Resetting deadlock count statistics

It seems that just restarting the PostgreSQL service doesn't clear / reset the detected deadlock count. How can it be restarted to zero? select version(); PostgreSQL 9.4.5 on ...
3
votes
1answer
94 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 ...
5
votes
1answer
30 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
145 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 ...
9
votes
1answer
137 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 ...
0
votes
1answer
38 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 ...
9
votes
1answer
173 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
138 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
69 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
55 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 ...
2
votes
2answers
67 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 ...
1
vote
1answer
39 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 ...
0
votes
0answers
36 views

Generate N x M random sample table from normal distribution in MySQL

Is there an easy way to produce a table of, say 10000 (rows) by 500 (columns) of numbers from normal distribution with parameters mu = 1, std.dev = 5 ?
0
votes
1answer
73 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 ...
19
votes
3answers
623 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 ...
0
votes
0answers
10 views

is there a way to see statistics on when and how often a view is being used in SQL Server? [duplicate]

I was recently handed a request to produce a report on all the views in a given database along with an indication of how frequently they are used, presumably so that the developers can remove views ...
0
votes
0answers
65 views

Is it good to gather stats in Oracle?

I have a very new database, and I have a request to gather stats for some schemas.. is it okay to gather stats without any transactions processed in the database? What are the impacts it could have if ...
2
votes
1answer
27 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: ...
1
vote
0answers
80 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: ...
1
vote
2answers
85 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 ...
0
votes
0answers
20 views

Howto avoid queries to get considered in statistics [duplicate]

Is it possible to run my (administrative) queries in a way that they do not get considered in Sql Server statistics like e.g. Cpu intensive queries, long running queries etc.? Is there an option like ...
18
votes
1answer
378 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
2answers
1k 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
47 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 ...
1
vote
2answers
61 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 ...
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 ...
1
vote
1answer
43 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 ...
1
vote
1answer
201 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
105 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
48 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 ...
7
votes
2answers
1k 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, ...
2
votes
1answer
450 views

How MySQL Queries and Questions status variables are calculated?

I have a mysql server running on 5.6.19 version and I got confused with the status variables Queries and Questions. show global status like 'Queries%'; | Queries | 28058738 | ...
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 ...
6
votes
1answer
145 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: ...
0
votes
0answers
72 views

Columnstore Index Scan Cardinality Estimates

In SQL Server 2012, I am seeing large cardinality estimate discrepancies for a columnstore index scan. It looks like statistics have been generated for the index, but I don't see any details in DBCC ...
3
votes
2answers
84 views

What happens with statistics when reverting to a database snapshot

Lets say I have a database FOO for which I created a snapshot (named FOO_snap). Now I'll run thousands of queries on this DB. After that I will revert DB to FOO_snap. Will I lose all of the statistics ...
3
votes
1answer
844 views

Get IO stats request sql like activity monitor

Is it possible to get the same value as in the activity monitor (SSMS) for the "Database IO" as a result of a SQL request? click to enlarge
0
votes
2answers
119 views

SQL storage sizing - How to get statistics of what data is being accessed

How can I monitor which data is being accessed and which frequency? I'm in need to migrate several (very) small SQL Server instances, each which several small databases. Current configuration is ...
3
votes
1answer
433 views

dbms_datapump API exclude statistics during import

We are using dbms_datapump to copy data between different servers or to setup new schemas quickly. However importing statistics usually takes ages and has the additional disadvantage that schema ...
2
votes
1answer
77 views

Can statistics affect the query logic instead of performance?

Is it possible that the frequency of rebuilding indexes (which automatically triggers statistics update) can affect the query logic? One of the developers I work with claims that a stored procedure ...