Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

32
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
16
votes
2answers
9k 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 ...
14
votes
4answers
779 views

Should the index on an identity column be nonclustered?

For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column? The reason is other indexes will be created for queries. A query which uses a ...
11
votes
2answers
281 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
8
votes
1answer
201 views

Does RSS Profile have 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 ...
8
votes
1answer
177 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 ...
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
2answers
156 views

How to measure Key Lookups per Second on SQL Server

I am working on reducing the amount of transactions doing key lookups on our SQL Servers. First I want to define a baseline for the normal or tolerable number on that metric and as we constantly ...
6
votes
1answer
148 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
2answers
2k 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 ...
5
votes
1answer
3k 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
284 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
567 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. ...
5
votes
1answer
818 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
38 views

Is it possible to connect MS SQL Server Express 2012 through DAC Connection?

Is it possible to Connect MS SQL Server 2012 Express Through DAC. After moving from SQL Server 2012 Enterprise to MS SQL Server 2012 Express, some performance problem were there. When i have checked ...
4
votes
1answer
559 views

Do I have a memory issue?

I ran sp_Blitz that pointed me to a memory issue saying: Memory Dangerously Low The server has 32755 megabytes of physical memory, but only 235 megabytes are available. As the server runs ...
4
votes
3answers
258 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
223 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
1answer
3k 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 ...
4
votes
1answer
8k 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 ...
4
votes
2answers
140 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
3answers
75 views

Make queries and BCP faster on a very large table

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that ...
4
votes
1answer
51 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
408 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
3answers
190 views

Simple yet problematic update query

I have a rather simple update/query, that has been causing me a lot of grief over the years. in simplest form it is : update VillageSemaphore set TimeStamp = getdate() where VillageID in ...
3
votes
2answers
785 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
253 views

Can I find out which database is queried on the most?

We have a number of database which all have the same schema for each customer. I was wondering if there was a query which could select count(*),db_name from somesysview group by db_name; From a ...
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
9k 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 ...
3
votes
1answer
162 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 ...
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
2answers
14k 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

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
92 views

Release MySQL Memory and Performance Tunning

I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file. The server is a dedicated server for MySQL database and the ...
3
votes
2answers
288 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
167 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
113 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
109 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
264 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
1answer
151 views

What does “cleaning up” mean in MySQL InnoDB transaction status and how to prevent it?

In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load: > SHOW ENGINE innodb STATUS; ... ------------ TRANSACTIONS ------------ ... ...
3
votes
2answers
136 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
0answers
40 views

Why does retrieving fewer rows take longer? [duplicate]

I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on: SQL Server ...
3
votes
2answers
129 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
5answers
855 views

MySQL: filter date column using LIKE or MONTH?

I have a CRON task that needs to extract customers with birthdays in a given month, from a MySQL InnoDB table. The birthday field is indexed and of type DATE. Filtering for April, I can query the ...
2
votes
2answers
416 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
5k 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
503 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 ...