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

6
votes
1answer
5k views

High Buffer I/O SQL Server 2008 R2 causes?

Am trying to troubleshoot performance on an SQL server that we have for our document management server and today performance has hit an all time low. Looking at the MDW I setup recently I can see a ...
6
votes
1answer
147 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 ...
6
votes
1answer
868 views

What should every DBA know? [closed]

As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like ...
6
votes
3answers
2k views

Why performance degradation with query hint FIRST_ROWS(1000)? [closed]

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input. In case the user supplies very unselective arguments, the query takes a lot of ...
6
votes
2answers
796 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 ...
6
votes
1answer
203 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 ...
6
votes
1answer
69 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 ...
6
votes
3answers
1k 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') ...
6
votes
2answers
336 views

MySQL: Does the order of the fields in a table make a difference in performance?

Specifically I am wondering if I should place all my fixed length fields like int, timestamp, char at the beginning of the CREATE TABLE and place all the variable length like varchar at the end. ...
6
votes
1answer
149 views

Start/Stop MySQL

I'm seeking help to understand what happens when the following command line is executed: root@prodn$ service mysqld stop Yes, it shuts down the MySQL server so access to it is no longer available ...
6
votes
1answer
272 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
6
votes
1answer
128 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
6
votes
1answer
857 views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
6
votes
2answers
170 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...
6
votes
1answer
326 views

Backup to three drives suddenly slow

The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit ...
6
votes
3answers
508 views

Single query sending and retreiving duplicate data on MS SQL Server?

Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
6
votes
1answer
431 views

Using an RDBMS for querying tens of Terabytes of time-series data?

I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote: (...) opinions regarding best of breed data structures to be used for indexing time-series (aka ...
6
votes
1answer
158 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 ...
6
votes
2answers
161 views

Minimizing Indexed Reads with Complex Criteria

I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such: CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 ...
5
votes
3answers
324 views

Reducing the size of a database

SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff ...
5
votes
2answers
1k views

Compare Microsoft SQL server performance between virtual and dedicated server

I have a dedicated windows 2008 r2 server at the moment and I am thinking about migrating to a virtual server because of the costs. Now I know that SQL should be on a dedicated server, but the ...
5
votes
2answers
379 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
5
votes
3answers
486 views

SQL Server 2005 uses 1GB of RAM

Is this normal? To be exact it uses 1,068,000 KB. It occasionally shoots up to 1,400,000 if query takes too long. My total system memory is 3GB and I am running WinXP. Is there a specific amount of ...
5
votes
3answers
931 views

Is the ADO.NET Entity Framework known for generating poorly performing queries?

This question is inspired by the comment posted to the latest ServerFault blog post: Are you guys still using LINQ to SQL? I know I can use SQL Server Profiler and/or the ToTraceString method to ...
5
votes
2answers
4k views

Which performance counters can you look at on a SQL Server Instance to determine it's performance and over all health?

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
5
votes
3answers
123 views

Dynamic temporary index creation

In my undergrad databases course, my professor mentioned that some DBMS software is so advanced that it can detect when a long-running query would benefit from an index. The DBMS can then create that ...
5
votes
2answers
303 views

Index fragmentation and SSD disks

I'm new to performance problems, but I saw some discussion that SSD disks solve performance problems caused by index fragmentation. Has anyone experienced this? I'm thinking of changing my hardware ...
5
votes
3answers
626 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 ...
5
votes
2answers
253 views

Create function in central database or repeat in each database?

One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
5
votes
5answers
729 views

Advice with date column SQL Server 2005

I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example): WITH Dates AS ( ...
5
votes
1answer
690 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
5
votes
2answers
117 views

Are existing entries immediately included into a newly created index?

Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index: CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ...
5
votes
3answers
3k views

MySQL general query log performance effects

One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours. I wouldn't know how much data it will actually write. The amount ...
5
votes
2answers
1k views

How to optimization database for heavy I/O from updates (software and hardware)

The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some ...
5
votes
1answer
775 views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN ...
5
votes
2answers
1k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
5
votes
3answers
209 views

When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
5
votes
1answer
565 views

Optimum configuration for 4 SSD drives

I would like to move a SQL Server database from a shared configuration with the web-server to it's own dedicated box. My current budget will allow me to put 4 disks together in an array with a single ...
5
votes
3answers
197 views

SQL Server how to test filegroup IO

We have a customer who is switching from SAN storage to directly attached storage. Our SQL Server database has several filegroups all of which has been deployed on the same LUN. In order to determine ...
5
votes
4answers
4k 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 ...
5
votes
1answer
164 views

Is my function nondeterministic one

I have two functions: fn_Without_Param and fn_With_Param CREATE FUNCTION [dbo].[fn_Without_Param] ( ) ... INNER JOIN .. ON .. AND SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 ) and /* ...
5
votes
2answers
492 views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
5
votes
2answers
3k views

Optimizing bulk update performance in Postgresql

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
5
votes
2answers
527 views

SQL Server performing slow

I have a Microsoft SQL Server running on an IBM server with 10 cores and 64 GB RAM. I have from 100 to 300 users working concurrently on the server. Users complain about slowness of the application. ...
5
votes
2answers
4k views

SQL query slow-down from 1 second to 11 minutes - why?

Question: I'm porting the below query (listing tables by foreign key dependencies) to PostGreSql. WITH Fkeys AS ( SELECT DISTINCT OnTable = OnTable.name ,AgainstTable = ...
5
votes
1answer
358 views

how to improve performance by using or not using table variables

I have a table variable: DECLARE @to_process TABLE ( [Id] [bigint] NOT NULL, [SequenceId] [bigint] NOT NULL, ... ) INSERT INTO @to_process ( Id , SequenceId ... ) SELECT ...
5
votes
4answers
3k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
5
votes
1answer
309 views

IO_STALL question and understanding

I am collecting IO_STALLS from sys.dm_io_virtual_file_stats every 5minutes and then doing a delta to see which files are being affected most by IO. In one 5min period I get a delta of 5826331 ms ...
5
votes
2answers
628 views

Performance impact of waiting on a service broker message

I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
5
votes
2answers
767 views

Speed up INSERTs

I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is : Have no index on the table whatsoever Switch to simple logging (from full logging) ...