The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
1answer
88 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
237 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 ...
7
votes
1answer
221 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
118 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 ...
0
votes
0answers
60 views

Improving Statistics for Query Plan [closed]

I have a table with over 2.1 million rows. This table has a column rID, which joins to a PK on another table. As you can see from the distribution below, most rows contain a null for rID; which ...
3
votes
2answers
596 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 ...
11
votes
2answers
15k 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 ...
1
vote
3answers
69 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, ...
4
votes
1answer
657 views

Oracle: How to gather stats in a logical standby database?

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
0
votes
1answer
16 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 ...
4
votes
1answer
97 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 ...
12
votes
2answers
5k 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 ...
0
votes
2answers
41 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 ...
6
votes
1answer
630 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: ...
2
votes
1answer
384 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 ...
2
votes
2answers
86 views

Get count of processed queries

I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics ...
0
votes
0answers
13 views

High bandwidth on virtual IP

Description of the environment : An apache server (Drupal) connected to mysql server using a virtual IP. The problem : The (outgoing) bandwidth on VIP has exploded (after a new site version ...
2
votes
0answers
233 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
74 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?
1
vote
0answers
76 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 ...
2
votes
2answers
266 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
253 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 ...
3
votes
2answers
214 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 ...
1
vote
2answers
1k 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
294 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 ...
3
votes
1answer
2k 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
693 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
171 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 ...
9
votes
2answers
1k 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?
15
votes
2answers
16k 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?
1
vote
2answers
127 views

Why doesn't running a full sample *always* improve query performance

Conventional wisdom would suggest that running a full sample of statistics would provide SQL with the optimum information to make the best decisions when forming a query plan for execution and ...
1
vote
0answers
161 views

How to check what stats were used in query

respected DBAs . . . is it possible to tell when statistics were last accessed by a query using T-SQL? I'm trying to ascertain what statistics are involved in a query, I know I can check the visual ...
7
votes
1answer
180 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 ...
3
votes
1answer
732 views

Teradata : Disadvantage of Collect stats

I want to know any disadvantage can occur by doing collect stats on a column that is being widely used in join condition across so many procedures ? (Eg. the column is accessed 300+ times access in 60 ...
1
vote
2answers
176 views

How to retrieve the definition behind statistics added to tables

Is there a way to programmatically retrieve the definition of each STATISTICS added to table columns and indexes. For both user added and system created indexes. There are many STATISTICS like ...
12
votes
1answer
457 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: ...
3
votes
1answer
515 views

Will Ola's default IndexOptimizer job update my stats?

I just installed Ola Hallengren's MaintenanceSolution.sql maintenance scripts. After looking at the output of the log generated by the IndexOptimize - USER_DATABASES job, I'm concerned that it's not ...
2
votes
0answers
324 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 ...
1
vote
0answers
512 views

sql server 2008 execution plan different on two production servers [closed]

We have two servers running SQL server 2008 R2, with identical databases, where we implemented a datawarehousing solution. The cube processing operation was taking a VERY long time in one of the ...
5
votes
2answers
2k 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 ...
0
votes
0answers
103 views

Replicating Excel's CHIDIST function in Oracle 11g

I'm attempting to replicate the functionality of an Excel workbook in Oracle. One of the formulae on the worksheet uses CHIDIST, which "Returns the one-tailed probability of the chi-squared ...
3
votes
1answer
543 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 ...
6
votes
3answers
645 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 ...
3
votes
1answer
378 views

Query requires 'Update statistics' very often in SQL Server

We have a few tables in our database. Since most of our queries are date based, we have constructed clustered index on 'date + primary key (surrogate)' combination and we enforce joins on date as well ...
6
votes
2answers
425 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 ...
0
votes
2answers
121 views

Find the average number of n:m connections of two tables?

I want to know to how many lists a user is assigned in average. User ==== id name Lists_Users =========== user_id list_id List ==== id title Is there a good way of receiving this number via ...
0
votes
1answer
92 views

Getting statistics of a table in Oracle

What is more efficient to gather table statistics: Using dbms_stats.gather_table_stats? Using analyze table?
3
votes
2answers
1k 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 ...
3
votes
1answer
2k 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] ...
11
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?