Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

12
votes
2answers
573 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 ...
10
votes
4answers
470 views

Should the index on an identity column be nonclustered?

For a table with a primary key (PK) on an identity column, it will be clustered by default. Could it be better as nonclustered? The reason is other indexes will be created for queries. A query which ...
8
votes
2answers
6k 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
163 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
118 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
276 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
725 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
3answers
87 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
4
votes
1answer
199 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
118 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
501 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
0answers
45 views
+100

Does RSS Profile has an effect on SQL Server with NUMA setup?

I'm curious whether the RSS Profile which is default NUMAStatic on Windows 2012 R2 could/should be changed for SQL Server with a NUMA setup. Has anyone done performance benchmarks on high load SQL ...
4
votes
1answer
43 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
1k 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
372 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
169 views

Latin1_General_BIN performance impact when changing the database default collation

I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the ...
3
votes
2answers
517 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
2answers
11k 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
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
244 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
7k 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
59 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 @ ...
3
votes
1answer
62 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 ...
3
votes
1answer
92 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
242 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
115 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
100 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
341 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
4k 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
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
127 views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...
2
votes
1answer
135 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
53 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
7k 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
1answer
89 views

PostgreSQL doesn't use all the RAM it could

I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config: # <snip> the default config ...
2
votes
1answer
178 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
1k 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
664 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
44 views

Associate SQL Server Queries with Databases

Is there a way via T-SQL and DMVs to show which database a query was run against? The goal is to look at CPU and I/O by query by database so I can redistribute the databases as I reconfigure the ...
2
votes
1answer
13 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
76 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
548 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
54 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 ...