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.
6
votes
3answers
276 views
Query performance
Why
SELECT Barraportfolioname
FROM portfolio
WHERE id IN (SELECT DISTINCT i1.portfolioid
FROM Import i1
LEFT OUTER JOIN Import i2
ON ...
6
votes
2answers
309 views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
6
votes
3answers
2k views
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
6
votes
4answers
408 views
Four db servers all identical, one is much slower than the others
The setup
We have an application loading a lot of transaction data for later querying. It's sharded on 4 SQL Server 2008R2 Enterprise Edition on Windows Server 2008 64 bits. All servers are ...
6
votes
3answers
808 views
Testing stored procedure scalability
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
6
votes
2answers
675 views
Displaying query plans in Server Management Studio
Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset:
select top 10
sum(qs.total_worker_time) as total_cpu_time,
...
6
votes
4answers
528 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 ...
6
votes
3answers
872 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 ...
6
votes
4answers
277 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 ...
6
votes
4answers
422 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 ...
6
votes
2answers
267 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% ...
6
votes
2answers
2k views
Performance Hit using CAST() in TSQL (SQL Server)?
We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField).
So, if myField is of type NVARCHAR, we ...
6
votes
4answers
877 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 ...
6
votes
1answer
162 views
Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?
The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000.
How can ...
6
votes
2answers
301 views
Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting
While writing a query other day a thought came to me and have stuck in my mind.
What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise ...
6
votes
2answers
179 views
Row padding to get more performance
I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance:
Because rows are small (many fit a page) multiple locks may compete for one ...
6
votes
2answers
4k views
Fastest way to check if InnoDB table has changed
My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.
My ...
6
votes
5answers
2k views
Tools to monitor Oracle Database Performance on Standard Edition
Here is what I am looking for:
Works on Standard Edition i.e. No Diagnostic Pack.
Provides both current and historical performance data.
Good visuals for easy spotting of trends and presentation ...
6
votes
2answers
1k views
SQL Server Join/where processing order
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
6
votes
1answer
544 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
1k 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
480 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
2answers
7k views
MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load
i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
6
votes
1answer
52 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
760 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
2k views
Performance of a Trigger vs Stored Procedure in MySQL
A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
6
votes
2answers
235 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
180 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
86 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
514 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
1answer
224 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
413 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
2answers
397 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 ...
6
votes
1answer
319 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
72 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
272 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
973 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
3answers
368 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
4answers
3k views
Aggressive Autovacuum on PostgreSQL
I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:
autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
...
5
votes
2answers
2k 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
2answers
193 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
287 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
200 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
619 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
609 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
3answers
112 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
105 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
3answers
134 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
2answers
181 views
Why you want to avoid Dynamic SQL in stored procedure?
I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
