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
3k 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 ...
7
votes
3answers
6k 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 ...
7
votes
3answers
194 views

Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been ...
7
votes
2answers
4k 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 ...
7
votes
2answers
942 views

What can I add to a server to make SQL restores faster?

I have a 2.8TB SQL database (mostly data files, some 400GB of log files) that currently takes around 9 hours to restore. This database is used for testing purposes and must be deleted and restored ...
7
votes
2answers
13k views

SQL Server gets slow 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
4k 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
3answers
5k views

Multiple Oracle Instances — is this a good practice?

One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're ...
7
votes
4answers
6k 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
2answers
1k views

PostgreSQL: reuse of complex intermediate result in same query

Using PostgreSQL (8.4), I'm creating a view that summarizes various results from a few tables (e.g creating columns a, b, c in the view), and then I need to combine some of these results together in ...
7
votes
1answer
273 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
4k 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
250 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
2k views

Postgresql 8.3: Limit resource consumption per query

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy. This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes ...
7
votes
1answer
15k 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 ...
7
votes
1answer
4k 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 ...
7
votes
2answers
268 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
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
1answer
1k views

Database design for handling 1 billion rows and counting

We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes ...
7
votes
1answer
436 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
111 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
327 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
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
425 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
1answer
188 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 ...
7
votes
1answer
219 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

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
159 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
0answers
1k views

SQL Server 2012 performance issues (maybe due to linked server) [closed]

Summary of the problem We've spit our one database server environment into two servers. We are now having intermittent performance issues I am hoping someone can offer clues - perhaps direction ...
6
votes
3answers
6k 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
659 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
975 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
5answers
5k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
6
votes
4answers
43k 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
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
3answers
304 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
2answers
619 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
753 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
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
3k views

Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL? I understand that where platform interoperability and/or backwards compatibility may be ...
6
votes
5answers
6k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
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
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 ...
6
votes
2answers
7k 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
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
4answers
954 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

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 ...