An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.

learn more… | top users | synonyms

7
votes
3answers
3k views

Query performance degrades with time and use

I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
7
votes
2answers
903 views

What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?

My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month ...
7
votes
4answers
10k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes --------+-----------...
7
votes
2answers
13k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
7
votes
4answers
1k views

When to use MySQL query_cache?

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a ...
7
votes
6answers
931 views

Fast reads without paying the price on writes

We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, ...
7
votes
4answers
371 views

What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.] Question 1 What ...
7
votes
4answers
3k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
7
votes
2answers
14k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor (...
7
votes
2answers
20k views

SQL Server becomes slower over time until we have to restart it

We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
7
votes
2answers
5k views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...
7
votes
4answers
1k views

How can I tell if my SQL Server DB performance is hardware-limited?

Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test ...
7
votes
5answers
2k views

How to update 10 million+ rows in MySQL single table as Fast as possible?

Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64. I have ...
7
votes
4answers
8k views

SQL Server - Separating data, log, and TempDB files on a SAN

I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
7
votes
2answers
5k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
7
votes
3answers
3k views

SQL Server 2008 R2 High CPU

We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3. Our database works very hard (we run around 600,000 queries in 1 minute) and ...
7
votes
2answers
1k views

Is it possible to give the optimizer more or all time it needs?

Given that the optimizer cannot take al the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off. I was ...
7
votes
1answer
10k views

How can I monitor the call count per stored procedure per timespan?

In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get ...
7
votes
2answers
3k views

Explanation requested for slow DELETE with SQL Server

I would like to get some extra insights/reasoning for SQL Server delete behavior. We have a fairly large database of over 1800 GB. In there are some very shallow tables (only a few integer columns) ...
7
votes
2answers
1k views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
7
votes
4answers
8k views

Select statement takes long remotely due to long “client processing time”, but short locally

While connected to our production server (SQL2008. very powerful machine), this select statement takes 2 seconds, spitting back all fields (4 MB of data in total). select top 30000 * from person ...
7
votes
4answers
15k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
7
votes
2answers
438 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
7
votes
1answer
515 views

T-SQL - OPTION (FAST x) and trace flag 8722

I have searched a long time and still haven't found the answer to my problem. Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to ...
7
votes
1answer
5k views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
7
votes
1answer
6k views

Why does that query cause lock wait timeouts?

From time to time, I find a lot of these errors in my PHP error log: MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL The problem persists for about 2 or 3 minutes. ...
7
votes
1answer
322 views

Auto Update Statistics Duration and Interruptions

Our production SQL Server 2008 R2 sometimes begins to perform slowly: all queries begin to work too slow (and sometimes all queries just hang and wait something then continue). This usually lasts ...
7
votes
2answers
38k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
7
votes
2answers
6k views

Oracle's Merge v. Select->Update/Insert

What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value? I'm trying to optimize a database, but I haven't found any speed tests comparing the two. I found ...
7
votes
2answers
1k views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
7
votes
1answer
3k views

MySQL table creation insanely slow

A simple table creation on one of my MySQL databases takes forever: mysql> CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY); Query OK, 0 rows affected (16.58 sec) The machine is quite ...
7
votes
1answer
266 views

How should we handle rows which won't be queried once they are old in PostgreSQL?

We have a table in a PostgreSQL database which is growing on the order of millions of rows per day. Each row consists of: ID Foreign user ID Date and time Other data The date and time aren't ...
7
votes
1answer
6k views

Why is DELETE so much slower than SELECT, then DELETE by id?

I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
7
votes
2answers
1k views

Advantage of using a database snapshot for reporting purposes

What is the performance advantage of using a snapshot of a database for reporting purposes? The way I see it, it would probably downgrade performance since for every write in the original database, ...
7
votes
2answers
2k views

Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
7
votes
1answer
1k views

Performance impact of sp_configure blocked process threshold change

I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use EXECUTE sp_configure 'blocked process threshold', 5 In combination with a server ...
7
votes
2answers
298 views

High rise in SQL Server CPU after adding a DB

I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005). My average CPU was on 48% ...
7
votes
2answers
16k views

How to get CPU usage by database for particular instance?

I have found the following queries to detect the CPU usage by database, but they are showing different results: WITH DB_CPU_Stats AS ( SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], ...
7
votes
2answers
14k views

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): +---------+-------------+------+-----+-------------------+-----...
7
votes
1answer
502 views

Strange behaviour DBCC Shrinkfile

I am attempting to run a dbcc shrinkfile in chunks of 1GB against a database where 95% of the data has been archived and deleted. I'm leaft with a 235GB file where 9GB is data/indexes. I want to ...
7
votes
1answer
133 views

Is there a performance hit by breaking out tables into different schemas in SQL Server?

Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server. For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...
7
votes
1answer
3k views

PostgreSQL concurrent increment of counter

I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
7
votes
3answers
3k views

Performance issue with xpath in SQL Server 2008

I have a table with lots of large xml-documents. When I run xpath expressions to select data from those documents I run into a peculiar performance issue. My query is SELECT p.n.value('.', 'int') ...
7
votes
1answer
288 views

What is the expected performance impact when dropping a large table

I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service. The table is no longer being written to, and needs to be ...
7
votes
2answers
1k 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 ...
7
votes
1answer
827 views

Low CPU Utilization but High Signal Waits

I have a server with 16 CPUs that is configured with a max degree of parallelism of 8 and a max worker threads setting of zero. For a given hour, my signal waits were 20% yet my OS CPU utilization ...
7
votes
1answer
119 views

Two servers, different execution plans. Several orders of magnitude difference in run time

Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my ...
7
votes
1answer
128 views

Instant File Initialization on Shared drive

I asked something similar to this before, but previously I had asked about moving backups to a shared location. This time I am curious: if I want to restore a database on a shared drive, do I need to ...
7
votes
2answers
1k views

Investigate peak in MySQL throughput

Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the ...
7
votes
0answers
358 views

Performance inner join vs left join involving parallelism

I have a query which takes about 8 hours to finish. Simplified, the query looks something like this: SELECT [Some attributes, inconsequential] FROM RBM.Financial_Events RFE INNER JOIN ...