The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
1answer
46 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
1answer
26 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 ...
3
votes
2answers
105 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
96 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 ...
1
vote
2answers
278 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
93 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 ...
2
votes
1answer
524 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 ...
3
votes
1answer
178 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 ...
1
vote
3answers
101 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 ...
4
votes
0answers
204 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
2answers
51 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 ...
7
votes
2answers
1k 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 ...
1
vote
0answers
100 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 ...
1
vote
2answers
112 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 ...
7
votes
1answer
124 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 ...
1
vote
1answer
220 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 ...
3
votes
1answer
256 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 ...
2
votes
0answers
186 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 ...
5
votes
2answers
507 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
76 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 ...
6
votes
3answers
341 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
281 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 ...
1
vote
0answers
310 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 ...
3
votes
1answer
206 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 ...
0
votes
2answers
80 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 ...
3
votes
1answer
417 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 ...
7
votes
1answer
357 views

How does sampling work when updating statistics?

I have several massive tables. I'd like to ensure their statistics are up to date via a weekly maintenance plan. However, doing so is taking too much time. If I specify WITH SAMPLE 50 PERCENT ...
0
votes
1answer
369 views

Postgres 9.1 statistics in pg_stat_database

I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and on IRC. I found SOME information for 9.2 but even that doesn't seem to be ...
3
votes
1answer
286 views

Can't understand results of pg_stat_statements view

I configured my postgresql.conf (my version is 9.1) with the normal persistent configuration: shared_preload_libraries = 'pg_stat_statements,plperl' custom_variable_classes = ...
8
votes
1answer
716 views

SQL Server Index vs Statistic

What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
0
votes
1answer
396 views

Oracle not trusting statistics after an import?

We are seing some strange behavior in the way Oracle is handling statistics of an imported schema. We build a schema on the dev system, analyze if and import into the production with the stats. For ...
2
votes
2answers
295 views

Problem with sp_Updatestats

I am having a problem when running sp_updatestats on my database it generates an error when my emp table is updating and disconnects I will paste the error here Updating [dbo].[emp] Msg 0, Level 11, ...
2
votes
1answer
803 views

Why is DBA_SEGMENTS presenting twice the amount of data than the DBA_TABLES?

I tried to compute the audit log table usage using the following steps: Truncate all the audit log table data (i.e. AUD$) Perform test case that inserts and update data, also delete statement but it ...
6
votes
1answer
256 views

AUTO_UPDATE_STATISTICS_ASYNC ON?

I have been advised that setting the AUTO_UPDATE_STATISTICS_ASYNC ON may be a good idea. I have been reading a couple of ideas and still not sure as this is a non default setting. Any further ...
0
votes
1answer
89 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?
1
vote
1answer
221 views

SQL Server 2008 R2 - Weekly / Monthly Statistics

I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server. For instance, how many times they were ...
11
votes
1answer
278 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
2answers
570 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 ...
11
votes
2answers
8k 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 ...
3
votes
1answer
926 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] ...
6
votes
1answer
2k views

Fast general method to calculate percentiles

I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile. An obvious solution is to count and sort the column and then do a ...
8
votes
2answers
431 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?
4
votes
1answer
292 views

Getting ratio of reads to writes on SQL Server

Sorry I am not a DBA so bear with me. I wanted to get a better understanding of the ratio of reads to writes in our archiecture. The best to get this is at the database. We are using SQL Server ...
1
vote
2answers
157 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 ...
4
votes
2answers
608 views

Performance degradation after rebuilding indexes

I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
10
votes
3answers
2k 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?
6
votes
2answers
330 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 ...
3
votes
2answers
437 views

How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

I'm working on a project that must support two database engines; SQL Server and PostgreSQL. We are using NHibernate as the ORM. We are running into performance issues with certain queries. Using ...
2
votes
2answers
766 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 ...
5
votes
1answer
762 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 ...