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
1answer
382 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
100 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
323 views

Could Stack Overflow run on SQL Server Web Edition?

I am starting a new web venture that may need to scale to a high number of users. I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in ...
7
votes
1answer
8k views

Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?

During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions ...
7
votes
2answers
4k 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 ...
7
votes
2answers
12k 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 ...
7
votes
1answer
2k 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 ...
7
votes
3answers
2k 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
2answers
336 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 ...
7
votes
2answers
16k views

SELECTing multiple columns through a subquery

I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them. SELECT DISTINCT petid, userid, (SELECT ...
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
1answer
143 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 ...
6
votes
3answers
4k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column?
6
votes
1answer
486 views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
6
votes
5answers
798 views

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the ...
6
votes
3answers
2k 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 ...
6
votes
2answers
475 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 ...
6
votes
2answers
686 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 ...
6
votes
3answers
299 views

Things to consider with a bigger table

I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently ...
6
votes
3answers
4k views

Too Many Connections

I'm using MATLAB to feed the database but after a time I'm getting a too many connections error. I always using opendb and closedb in pair. Is it to fast the feeding? Do I have to wait after I ...
6
votes
3answers
1k views

Performance of Inline-TVF vs. Views

I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
6
votes
3answers
303 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
4answers
345 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
950 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
4answers
31k views

High CPU usage on SQL server - Slow queries [closed]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
6
votes
2answers
6k 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 = ...
6
votes
4answers
832 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
2answers
2k views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
6
votes
1answer
2k views

How to improve Procedure Cache Hit Ratio?

From what I can tell Procedure Cache Hit Ratio below 95% is a problem. On my box, the values hover from 85 to 95%. How do I fix this issue? The server seems to have plenty of RAM so that ...
6
votes
2answers
2k views

Why use separate databases for high traffic/volume tables?

While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things. The first one contains the ...
6
votes
2answers
2k views

Rebuilding index has impact on mdf/ldf files SQL Server 2008

While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files. What does it exactly means? Is complete process done on TempDB and does ...
6
votes
2answers
3k 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 ...
6
votes
4answers
768 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
749 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
1answer
845 views

Password hashes: Fixed-length binary fields or single string field?

I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are: Storing the hash and the ...
6
votes
2answers
760 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) ...
6
votes
1answer
4k views

Troubleshooting SOS_SCHEDULER_YIELD wait

Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems. After performing the same activities in both the development and ...
6
votes
2answers
564 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 ...
6
votes
1answer
3k 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. ...
6
votes
4answers
5k 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) ...
6
votes
1answer
326 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
276 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
1answer
312 views

Sql server snapshot isolation overhead when read_committed_snapshot is enabled

When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation? Snapshot ...
6
votes
1answer
12k 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
188 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
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) , ...
6
votes
1answer
172 views

Does tempDB disk perf matter if you have plenty of unused memory?

We have a SQL Server 2012 Enterprise setup with a large amount of RAM (256 GB), and at this time fairly small databases (about 17GB). Right now we are experiencing performance issues due to various ...
6
votes
2answers
7k views

What's the difference between POINT(X,Y) and GeomFromText(“POINT(X Y)”)?

I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table. ...
6
votes
1answer
3k views

Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server. Server version: 5.5.24-55 Percona Server (GPL), Release 26.0 I have a 10 cpu box of these characteristics. processor : 0 vendor_id ...
6
votes
1answer
1k 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 ...