Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

37
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 ...
17
votes
2answers
11k 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 ...
15
votes
4answers
1k 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 ...
13
votes
2answers
308 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
242 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
186 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
2answers
3k 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 ...
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
224 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
167 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
292 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
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
1answer
4k 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 ...
5
votes
2answers
142 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 ...
5
votes
1answer
592 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
914 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
4k 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
11k 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 ...
4
votes
2answers
308 views

Are scripts like sp_BlitzIndex and the others accurate on restored backups?

I am a developer trying to get a better grasp of things. I am wondering if using the scripts from BrentOzar.com will work on restored backups? For example if I have the DBA give a backup of ...
4
votes
1answer
46 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
596 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
448 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
263 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
9k 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
164 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
2answers
46 views

A lot of ad-hoc queries impact on server

We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc ...
4
votes
1answer
100 views

How do I know that the high number of compilation/sec is because of memory pressure not bad queries?

My server is having a high compilation/sec compared to batches/sec (more than 10%). How do I know that this is because of memory pressure that forces removing cached plans from memory, or because of ...
4
votes
3answers
173 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
52 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
436 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
194 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
960 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
257 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
2answers
420 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 ...
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
69 views

Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate. Blocking/non-blocking aggregate operators The ...
3
votes
1answer
201 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
2answers
7k views

Optimizing queries for 25+ million rows

I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). ...
3
votes
1answer
2k 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
17k 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
167 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
324 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
352 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
155 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
269 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
0answers
113 views

Best procedure to measure query optimization effects?

I could not find valuable answers or explanations to the following question: How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps. ...
3
votes
1answer
89 views

Can performance be improved by breaking databases up?

Performance is my over-riding concern, as I have a heavily recursive CTE that is the engine room of my application. This CTE may have to work on 4 columns of up to 7 million rows. Queries can take ...