The tag has no usage guidance.

learn more… | top users | synonyms

5
votes
3answers
135 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
5 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 ...
6
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, ...
0
votes
1answer
254 views

How to configure postgresql-9.2 to know blk_write_time in pg_stat_database

Interested in the procedure for collecting block write statistics of PostgreSQL 9.2. Steps followed for configuration, could get blk_read time but not blk_write_time CREATE EXTENSION adminpack; and ...
2
votes
1answer
624 views

Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

We are using Oracle 11g, 11.2.0.3. We know a record exists in a table but a select is not returning it for some odd reason. The execution plans the general query, not forcing any index, shows that ...
11
votes
1answer
233 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 ...
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 ...
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
1answer
573 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
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 ...
1
vote
0answers
79 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: ...
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
2k views

SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?

I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not ...
0
votes
0answers
12 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
51 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
55 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 ...
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 ...
3
votes
2answers
141 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
136 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 ...
18
votes
1answer
376 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 ...
9
votes
1answer
169 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
137 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 ...
3
votes
1answer
104 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
10
votes
2answers
6k 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?
0
votes
0answers
53 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 ...
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 ...
2
votes
2answers
66 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 ...
26
votes
1answer
673 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 ...
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 ?
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 ...
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
606 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 ...
3
votes
3answers
1k views

SQL Azure dm_exec_query_stats clearing?

I'm doing a lot of work with SQL Azure at the moment, trying to improve performance of a web app. I am using the dm_exec_query_stats view to look for poor performing queries. Does anyone know a way ...
0
votes
0answers
64 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: ...
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
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
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 ...
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 ...
5
votes
1answer
221 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 ...
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 ...
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 ...
3
votes
2answers
3k views

Executing the query “UPDATE STATISTICS [dbo].[Contact] WITH FULLSCAN …” failed

My maintenance plan fails with the following error on one of my databases does anyone know how I can fix this problem? is is a corrupt table? Executing the query UPDATE STATISTICS [dbo].[Contact] ...
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 ...