The tag has no wiki summary.

learn more… | top users | synonyms

7
votes
1answer
3k 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
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?
4
votes
1answer
416 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
172 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
733 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 ...
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?
6
votes
2answers
400 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
591 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
1k 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
835 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
234 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
245 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
530 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
1k 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
387 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
1k 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
989 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
1k 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
5k 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
474 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 ...
15
votes
2answers
15k 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?