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

4
votes
3answers
1k views

For every insert statement I see sp_executesql in SQL Server Profiler

Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows. I saw for every insert I called, a call was made to ...
1
vote
2answers
804 views

What is com_set_option?

As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's ...
2
votes
2answers
475 views

Differences between development and live database setups

I was wondering if there are any best practice recommendations on setting up databases in a dev environment versus a live environment with regards to configuration. Should they be as similar as ...
62
votes
4answers
103k views

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help Setup The servers are quite hefty with an ...
3
votes
1answer
1k views

Replication slows down on massive deletes

First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue. We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so ...
16
votes
2answers
8k views

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added ...
19
votes
3answers
10k views

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
3
votes
2answers
175 views

How can a key lookup with zero executions and zero actual rows take time?

I have a query that is taking about 10 seconds to run and I am trying to optimize it. All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the ...
3
votes
2answers
1k views

SQL 2005- stored procedure performance troubleshooting

I am investigating performance issues for a stored procedure on SQL 2005 server. This is how I intend to do it: Analyze the stored procedure for any bad TSQL practices (like count * etc) Run it ...
4
votes
3answers
569 views

Usage of SQL Server 2008 R2 Express. Please Advise

I'd like you all to give me hints and advise on the following scenario: We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application. The Web Application won't have ...
19
votes
5answers
920 views

Should Query Tuning be Proactive or Reactive?

As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server). I make the best ...
3
votes
1answer
109 views

Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view?

Sorry for the long question, but let me give you my scenario. I have a DB that houses data exported from another DB. The containing tables are copies from the other (non-SQL) database. Each column ...
4
votes
2answers
209 views

Best way(s) to time ad hoc queries and stored procedures

What are the most accepted ways to time your queries, whether they are ad hoc or stored procedures? Is it more accurate to use SQL Profiler than SET STATISTICS TIME ON?
4
votes
2answers
1k views

Will creating partitions reduce locking and how do we implement this in sql-server?

A client wants our application to process more data faster so arranged a meeting with their dba to discuss options. This application generates quite a lot of data that is used for reporting. Before ...
2
votes
2answers
526 views

Clustered monotonically increased index insert performance

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this ...
6
votes
2answers
345 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
3answers
309 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 ...
9
votes
2answers
5k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
2
votes
1answer
895 views

Optimising a Database Shrink (in terms of speed and reducing fragmentation)

We have a large database that has had a lot of older data stripped out, leaving primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb ...
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 ...
10
votes
2answers
9k 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
3answers
308 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 ...
12
votes
1answer
3k views

Optimising BCP performance for BLOB data

I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
6
votes
1answer
674 views

Terabyte data set

I have the feeling that most of the database systems originate in the '80s and stereotypically implement tables, ACID transactions and constraints. They were build having in mind the scarcity of ...
3
votes
1answer
142 views

Does document “depth” affect CouchDB performance?

I have a project where it is very tempting to use a deeply nested collection of data. But how does that affect performance? Are there any reasons why I should avoid doing this? I'm talking about lists ...
10
votes
3answers
324 views

Query Performance Tuning

When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters? Do you run the query and view the actual execution plan? If so, what ...
6
votes
5answers
960 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 ( ...
12
votes
2answers
1k views

SQL Server: Has anyone used the large-page allocations tuning option?

Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any ...
5
votes
2answers
2k views

SQL Server query result transfer time over VPN (WAN)

I have a mysterious phenomenon on one remote SQL Server 2005 which I manage and query using SQL Server Management Studio. I connect to the server through a VPN tunnel (provided by an ISP, about 8 ...
15
votes
7answers
398 views

Which of these table designs is better for performance?

I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this. Here's what I know Company ...
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 ...
21
votes
3answers
5k views

Efficency of stored procedures vs raw queries

I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
11
votes
2answers
151 views

A question on SARGability

I just need to confirm that I understand something correctly: I recently viewed an SO question in which a user posted an answer in Linq like: from p in db.table where p.column.AddMinutes(1) > ...
5
votes
1answer
170 views

How big of an impact does NUMA really have?

I was reading up on Non-Uniform Memory Access (NUMA) and I was wondering how much of a difference it can really make? Another way to word that, what level of throughput is required before this ...
8
votes
2answers
6k 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 ...
15
votes
2answers
6k views

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
14
votes
3answers
2k views

Is there any point in undersizing VARCHAR columns?

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not. I would like to give the question of VARCHAR size a little twist and hope ...
9
votes
2answers
9k 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 ...
13
votes
10answers
1k views

What are the top 3 performance Issues that you encounter with your SQL Servers?

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
388 views

Using DTA vs. evaluating DMVs?

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA. Form How to determine if an Index is required or necessary I have ...
6
votes
3answers
12k views

Optimizing InnoDB default settings

I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
10
votes
2answers
447 views

@table_variable or #temp_table

I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get ...
5
votes
1answer
459 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 ...
1
vote
0answers
154 views

SQL Server and Perfmon [duplicate]

Possible Duplicate: SQL 2008 Server agent cant see perfmon counters I'm trying to setup SQL Server Perfmon counters on a Windows Server 2003 with SQL Server 2005 SP3. I'd like to run ...
6
votes
2answers
575 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. ...
12
votes
4answers
11k views

SQL Server statements intermittently slow on SQL Server 2008 R2

On one of our customers, we've been having some performance issues on our application. It's a .NET 3.5 web app that is consuming and updating data on a SQL Server database. Currently our production ...
7
votes
3answers
823 views

More Databases Per Sql Server 2005 Instance

Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate. Database size ranges from 50mb to 40-60gb in the extreme, ...
1
vote
2answers
256 views

dm_db_missing_index_details - does the order of equality_columns matter

I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view. MSDN says that this ...
4
votes
1answer
2k views

Storing hex values as strings or integers?

I've inherited a database that uses sets of 32-byte hex strings to attempt to uniquely identify objects. Each string is called a fingerprint and each object can have up to 20 fingerprints. I'm ...
3
votes
3answers
775 views

Best database choice for a highly scalable web ticketing system?

Essentially, we are developing using Ruby on Rails and want to make a highly scalable ticketing system. Essentially we will have a small database (containing event details and tickets sold) that will ...