The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
0answers
37 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
34 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 ...
3
votes
0answers
48 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently?

This answer motivated think again my following tables but I am still uncertain how to store statistic data for quick selection. I think it is well done with partial indexes because only a subset of ...
1
vote
1answer
37 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
52 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
40 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
37 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 ...
5
votes
1answer
79 views

Warning for missing statistics in execution plan

I have a situation that I cant understand. My SQL SERVER execution plan tells me that I have missing statistics on the table, but the statistics is already created. Please see the screens. But if ...
2
votes
1answer
87 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
294 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 ...
4
votes
1answer
76 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
32 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
71 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
275 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
77 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
125 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
70 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 ...
0
votes
1answer
51 views

Why/when does the SQL Server query optimizer use EQ_ROWS?

I'm trying to understand how exactly the statistics work. I've got a clustered index on my date. I can see a convert_implicit is executed when I run the following query. Don't know why it's doing ...
1
vote
1answer
192 views

MDX Max, Average and StDev functions not giving correct results

I have a scenario where I have Date dimension and Time dimension along with some other dimensions. The time dimension contains only Hours since there was no need to save data at minute or second ...
13
votes
0answers
358 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 ...
1
vote
0answers
71 views

Why wouldn't statistics update if all rows in a table were replaced?

We have several "reporting" tables in a SQL Server 2012 SP1 instance. Every hour, a job runs procedures that delete all the rows from these tables and then insert current data (involving lots of ...
1
vote
1answer
42 views

Why didn't this statistics object on a filtered index auto update?

In a SQL Server 2012 SP1 instance, I have a filtered index on the PK column of a table that looks like this: CREATE INDEX [RF_IXF_Orders_OrderNumber] ON [dbo].[Orders] ( [OrderNumber] ASC ) WHERE ...
3
votes
1answer
87 views

Where has STATMAN gone in SQL Server 2014?

So we've recently upgraded our environment to SQL Server 2014 (Enterprise on CU5) and are having some problems diagnosing performance issues, compounded by the problem that we no longer seem to be ...
5
votes
0answers
75 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 ...
0
votes
0answers
63 views

Sleeping queries blocking rebuild index and update statistics job in Always on

At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is ...
1
vote
2answers
106 views

pg_stat_activity - is it possible to decompose a stored procedure further?

I got a long running query in pg_stat_activity: datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | ...
0
votes
1answer
164 views

Update Statistics full scan hung up?

We have a weekly maintenance plan that does your typical Integrity Checks and then goes and does an Update Statistics on all the databases on our server. Over the weekend it appears that our job was ...
10
votes
2answers
336 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 ...
7
votes
1answer
271 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 ...
1
vote
1answer
127 views

Estimated rows very wrong when using declared variables

I am getting very, very wrong estimates for the clustered index seek step of this query (estimated rows is 8,637,530; actual is 74,723): Declare @StartDate as date = '10/1/2014'; Declare @EndDate as ...
1
vote
3answers
586 views

Update statistics for all databases all tables in a server

Hi I have been working on a script to update statistics of all tables on all my DBs. The idea is to parametrise it later on, but just as a quick fix, and not wanting to implement Ola's scripts today, ...
0
votes
1answer
23 views

Getting information on users payments via subscriptions

My current table db design looks like this : - User has one subscription - One subscription has many payments Table subscription has a user_id, which is FK to user table. Table payment has a ...
0
votes
2answers
72 views

Duplicate Statistics?

I have been looking at poorly performing stored procedure that performs well for larger customer databases. I believe the reason is that SQL Server has not had time to properly create good statistics ...
3
votes
0answers
430 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
0
votes
1answer
115 views

Oracle: index vs. table stats [closed]

In Oracle, when would I want to gather index stats, instead of just gathering stats on the table? In which use case would index stats make a difference?
2
votes
0answers
126 views

Oracle: separate column histograms for partitions

I have a partitioned table like MY_TABLE(PART_KEY, DATE_KEY, ...) with a local partitioned index on (PART_KEY, DATE_KEY). When I collect stats on the table, I end up with a HEIGHT BALANCED histogram ...
4
votes
1answer
138 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 ...
2
votes
2answers
523 views

How to improve row estimate of 1 row in join for newly inserted data

A custom statistic exists for the CacheId column of a table. After an overnight statistics update: Statistics for INDEX 'ST_TableName_CacheId'. ...
5
votes
1answer
379 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 ...
0
votes
1answer
382 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 ...
1
vote
1answer
185 views

how to force oracle 12c to create histograms

I installed TPC-H on Oracle 12c and my goal is to get histograms on column groups (if anyone knows for sure that this is not possible anyway, please prove and I'll stop testing...). So I used select ...
0
votes
1answer
150 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 ...
3
votes
2answers
339 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
2
votes
1answer
682 views

Strange things with Avg_row_length

The data from one tables is dumped to file and inserted into calls_old table with load data infile it shows Avg_row_length 7366. Later I dumped table with mysqldump, droped table, then created again ...
9
votes
1answer
513 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 ...
1
vote
2answers
2k views

Difference between show status and show global status in mysql

When I execute "show status" and "show global status" in MySQL at interval of 5 seconds , I got set of 291 results each time. But, value of some parameters are different. Below are the list of those ...
4
votes
2answers
518 views

SQL Server 2012 autoupdate statistics default behaviour

I found, that since SQL Server 2008 R2 there is a trace flag 2317 available. It turns on dynamical autoupdate statistics threshold, instead of default threshold of 20%. So, it greatly improves ...
4
votes
1answer
4k 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
1k 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 ...
2
votes
3answers
247 views

how to trigger a statistics update

I have a certain table in my OLTP database, which is bulk-updated by several users. There is no way to know when they will update the table (somewhere between 5 times a day and once in a week). The ...