Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

12
votes
2answers
565 views

SQL Server 2008 Performance issue with very strange resolution

I'm a long time DBA (12 years in SQL). I'm running SQL Server 2008 Enterprise in a Win 2008 clustered environment. 128gb memory and 32cpu in each cluster node. Not wimpy. I've got the weirdest ...
8
votes
2answers
5k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
7
votes
3answers
2k views

Dealing with CXPACKET waits - setting cost threshold for parallelism

As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits. I know the knee-jerk solution is to turn off all ...
7
votes
1answer
157 views

Lower performance on newest servers

We have several db servers on production, 4 of them with very similar hardware configuration. Dell PowerEdge R620, only difference is that 2 newest ones (bought and configured 3 months ago) have RAID ...
6
votes
1answer
110 views

Disk I/O and PAGEIOLATCH_XX

We have been having CPU issues with one of our servers lately and while we have been looking into this we have also noticed queries running slowly with waits of PAGEIOLATCH_XX. In particular, a ...
5
votes
1answer
2k views

Performance tuning a cascading delete with many foreign keys

I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data ...
5
votes
2answers
274 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
5
votes
1answer
696 views

How does order of index affect performance in SQL Server?

I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
4
votes
5answers
2k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
4
votes
2answers
3k views

Postgresql 9 speeding up indexed inserts (JPA)

I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
4
votes
1answer
188 views

Table with clustered Index consuming max memory causing low PLE alerts

For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day: Server: SQL server 2008R2 Databases : ...
4
votes
2answers
111 views

Using Database Engine Tuning Advisor in production

I have a SQL Server 2008R2 OLTP database which is experiencing performance issues during work hours. I would like to run Database Engine Tuning Advisor on this instance but I don't know how it will ...
4
votes
1answer
467 views

SQL Server 2008: Measure tps / select statements per second for a specific table?

There is a discussion how to measure activity against an entire DB: http://stackoverflow.com/questions/5019593/sql-server-2008-transaction-per-second-for-specific-database My question is narrower. ...
4
votes
1answer
40 views

How to increase performance in Cross Database operations?

We currently have a SQL Server 2008R2 being accessed by a legacy ASP application. Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into ...
4
votes
1answer
998 views

Database under heavier load — all SQL queries start timing out after a period of hours/days

I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it. You can see that everything seemed to be OK until about 3AM this ...
4
votes
1answer
359 views

Tips on improving/refactoring slow query?

Let's play lotto! Ok. I have a numbers table with 80 rows (numbers 1-80): create table dbo.numbers (knum tinyint primary key identity); I have a table with the drawid, and each number drawn. ...
3
votes
2answers
419 views

use an Index correctly to optimize queries

I have an SQL database with millions of records and when I'm querying the data like select * from ActCosts where ScenarioID= 456 The tables has 1,323,718 rows and it gives me like 50,000+ rows ...
3
votes
3answers
1k views

Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is ...
3
votes
1answer
1k views

How to troubleshoot db/app latency during transaction log backup

I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups. ...
3
votes
1answer
1k views

Profiling PostgreSQL

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive. Our machine: SO: CentOS 6 HW: 8 ...
3
votes
1answer
2k views

Understanding sys.dm_exec_query_stats execution_count vs creation_time

I've just downloaded the SQL Server 2012 'Performance Dashboard Reports' and am having some issues interpreting the data shown by one of the results and linking it back to the DMV ...
3
votes
2answers
10k views

Index for date column for range query in Oracle?

We have a lot: select count(*) from TBL where XDATE between ? and ? and FLD = ?; We expect to boost performance by tuning index on XDATE column (which have DATE type). What type of indexes more ...
3
votes
2answers
237 views

Tuning advisor with Extended events?

With SQL traces I was able to analyze them with Database Engine Tuning Advisor to obtain basic recommendations for perf. tuning(missing indexes, statistics,...). Now, with SQL 2012 and Extended ...
3
votes
1answer
6k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
3
votes
1answer
84 views

reporting space consumption to files in filegroup - how to reverse-engineer the algorithm spreading data across files

I would like to know how to calculate the distribution of data consumed per file in a filegroup, back to the index (HEAP, CLUSTERED, NONCLUSTERED) storing it. My intention is to define which I/O goes ...
3
votes
1answer
238 views

In-memory SQL Server log file

I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services. Its data comes from another SQL Server instance which is ...
3
votes
2answers
111 views

What perfmon counters should I use to get the fastest restore possible on an idle system?

I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk. I want to restore database as ...
3
votes
2answers
96 views

How can I monitor my server’s status before changing the “Cost threshold for parallelism” settings

Our production server is running SQL Server 2008 SP3. I’ve read a post on “cost threshold for parallelism” - ...
2
votes
2answers
321 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
2
votes
1answer
3k views

Why isn't oracle using an index for distinct query ?

This is the scenario SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true) PL/SQL procedure successfully completed. SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
2
votes
1answer
125 views

Dropped Index during Database Engine Tuning Advisor

I was analyzing a query using "Database Engine Tuning Advisor". During this time the database started to get blocking, long running queries and ended by restarted the instance. When I checked after ...
2
votes
2answers
9k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
2
votes
1answer
6k views

Optimize PostgreSQL for a lot of INSERTS and bytea updates

What we have (software): PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf) Windows 7 64 bit Hardware: Intel Core i7-3770 3.9 Ghz 32 Gb RAM WDC WD10EZRX-00L4HBAta Drive ...
2
votes
2answers
37 views

Setting shared_buffers in postgresql.conf does not seem to take effect

We're on CentOS release 6.6, PostgreSQL version 8.4.20. (Yes, this is not bleeding edge.) In postgresql.conf, we have: shared_buffers = 4096MB kernel shm values are set nice and high: [root@green ...
2
votes
1answer
162 views

Searching a big table for a few results without scanning the whole table

I need to search a very big PostgreSQL table (500+M rows), I want to limit the returned search results, but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?) ...
2
votes
3answers
990 views

Collecting Wait Stats

We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time. Below are the wait stats by number of tasks waiting and also wait time per ...
2
votes
1answer
640 views

MySQL Performance Issues

Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that ...
2
votes
1answer
12 views

Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Way back when Michael Jackson was still alive, I made this question: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause And now I'm performance-auditing a system in which the ...
2
votes
1answer
63 views

Postgresql performance issues when issuing many small inserts and updates one at a time

I have a production web server with postgresql database. My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in ...
2
votes
1answer
524 views

What database and setup can handle several million inserts per minute? [closed]

We have come to a point where for a few of our customers' systems, at peak times, events are generated at that crazy rate. That is, millions of new events per minute (between 1M and 2M). The ...
2
votes
1answer
48 views

Can I throttle individual databases instances on one SQL Server 2005 box

I am running a very old server that has a database that is being permanently retired within 14 months. My company will not invest anymore in hardware or software for this system as we are moving to a ...
2
votes
2answers
176 views

How to auto assign ordinal ranking in mysql with as minimal queries for new rows?

+----+---------+---------+-----------------+ | id | view_id | user_id | ordinal_ranking | +----+---------+---------+-----------------+ | 1 | 10 | 1 | 1 | | 2 | 10 | ...
2
votes
1answer
74 views

Does SQL Server redistribute data after adding an additional data file?

Imagine a database about 30GB in size using 3 data files on the same disk. Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three ...
2
votes
1answer
49 views

MySQL (MariaDB - 10.0.16-MariaDB-1 (Debian) strange performance issues

I am running version 10.0.16-MariaDB-1 (Debian GNU/Linux 8.1 (jessie)) on Dual Xeon CPU, total 32 cores with 128 GB of RAM. This is a dedicated DB server and has been recently migrated over from ...
2
votes
1answer
136 views

SQL aggregate function performance in a table with many records that is well-indexed

My question is about 3 tables that stores data of a single entity, and there is a report generation feature that refers these tables and calculate COUNT and AVG information. There are three tables: ...
2
votes
2answers
121 views

Need help performance tuning a query

Basically, here is the design of the table (the part that is necessary): id | dt -------- 1 | 2014-05-13 2 | 1900-01-01 3 | 1900-01-01 4 | 2014-05-12 5 | 1900-01-01 So what I am trying to do ...
2
votes
1answer
622 views

SQL Server : How to make “Update Statistics” faster?

We have a table with a 1 column Primary Key on and an UNIQUE Non-Clustered Index with 16 columns. This table has more than 20695780 Rows and on every day new records inserted ...
2
votes
0answers
9 views

Postgres 4x Slower Than It Was

Our Postgres performance has gone down to 1/4 of what it was, and we can't figure out why. We have two machines with identical hardware (let's call them A and B): Intel(R) Xeon(R) CPU E5-4640 0 @ ...
2
votes
0answers
32 views

MySQL memory is keep on increasing , any reason?

On one of our production host MySQL memory is keep on increasing day-by-day. I tried reboot of MySQL instance but still no avail. System has 16Gb of RAM. TOP output top - 13:05:13 up 35 days, ...
2
votes
0answers
46 views

How to identify whether a log backup or job executing a SP is causing High Page Reads/sec

For one of my servers( SQL server 2008R2) we can continuously witness High Page Reads/sec during business hours say: from 7 AM to 9 PM EST. I kept the counter settings to monitor the same and found ...