The statistics tag has no wiki summary.
6
votes
3answers
189 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
59 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
2answers
91 views
sql server 2008 execution plan different on two production servers
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 ...
2
votes
1answer
87 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
42 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 ...
2
votes
1answer
91 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
108 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
98 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
81 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 = ...
7
votes
1answer
191 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
170 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
194 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, ...
1
vote
1answer
307 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
182 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
80 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
174 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 ...
3
votes
2answers
275 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 ...
7
votes
2answers
3k 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
444 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]
...
5
votes
1answer
1k 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 ...
7
votes
1answer
199 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
190 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
96 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 ...
3
votes
2answers
414 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 ...
7
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
216 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 ...
2
votes
2answers
246 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
451 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 ...
4
votes
1answer
553 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 ...
1
vote
3answers
182 views
PostgreSQL and query planner
I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...
5
votes
2answers
152 views
If a query triggers a statistics update and times out are the statistics still updated?
I have a fulltext query which is usually very fast but may time out when it causes a statistics update since statistics updating is very slow on this database. Usually the query "recovers" to normal ...
3
votes
1answer
263 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 ...
1
vote
1answer
574 views
Estimated vs. Actual rows and multi-column statistics
I'm trying to understand multi column indexes and statistics, here is my real world example:
Table: TaskExecutions, size=~1 million rows.
Query SARGS: TaskExecStatusID = 3 AND TaskExecUpdatedDate IS ...
5
votes
2answers
278 views
Get NULL statistics for a column in MS SQL Server
In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column?
Same information is available in Oracle and PostgreSQL, and I heard that MS ...
6
votes
1answer
698 views
Medians, Modes, Percentiles and OLAP
I'm newbie trying to wrap my head around OLAP, and I have a few questions.
Question 1: Can an OLAP cube store medians, modes, percentiles?
Question 2: Can an user-written MDX query return a summary ...
5
votes
2answers
489 views
DTA Recommends to CREATE STATISTICS
I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file.
My question is, how exactly ...
4
votes
2answers
867 views
Estimate average and median efficiently in Postgres?
I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which ...
6
votes
5answers
2k views
SQL Select taking too much time to execute
It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table.
In code:
SELECT
TempTable.Col1,
...
5
votes
2answers
286 views
Estimating distribution of row access in mysql
I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can ...
11
votes
2answers
7k 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?
