Top 10 SQL Server performance bottlenecks

Question

What is the most common performance bottleneck that is not caused by the database structure?

Answer 1

The Applying scalar function to each row in the result set

SELECT fn_EveluateSomeValue([ColumnName]) FROM LargeTable

Answer 2

Triggers can be a HUGE bottleneck (and headache) when you are processing large batches of commands.

Answer 3

1. Server - Like memory and storage types.
2. Network - Latency and bandwidth issues.
3. Indexing - not sure if you consider this database structure
4. Queries - Bad queries with join issues that may cause full table scans.

Answer 4

Using cursors. For any database.

Answer 5

Physical.... Running out of memory and having to go to disk.

Answer 6

table scan because:

• index does not exist
• stats out of date
• functions in where clause prevent usage

Answer 7

Let's see (in no particular order)

1. Cursors

2. non-sargable where clauses

3. failure to index foreign key fields

4. failure to index fields commonly used in the where clause

5. correlated subqueries

6. accidental cross joins causing the need to distinct the result set

7. Badly performing code coming from ORMs

8. Code that causes too many reads or is called more than once when it didn't need to be (I've seen applications that send the same code many times when they didn't need to or every time a page is opened)

9. network pipe overloaded

10. User defined functions causing row-by-row processing

11. Parameter sniffing

12. out of date statistics

13. Union instead of union all

Answer 8

If the I/O system in the server isn't up to the job, you can get latch contention on tempdb and this in turn can cause serious performance problems.

Answer 9

I tend to run into the following bottlenecks (in order of frequency):

1. Missing or incorrect indices (resulting in table scans)
2. Poorly written queries
3. I/O contention

Answer 10

Having a poor my.cnf configuration can kill a server even with proper database design and indexing. Read:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/ http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql/ http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Helpful my.cnf analytical tool: http://www.day32.com/MySQL/tuning-primer.sh

Answer 11

Using table data one row at a time instead of one table at a time (i.e. cursors).

Unnecessary (or badly designed) locks.

Logs logging things that don't need to be logged (Delete from table instead of Truncate table, etc.)

Having constraints on when bulk loading data. Seriously slows down the insert. Turn them off, load the data, then turn them back on.

Answer 12

Ditto ref cursors, also badly written client apps that get a huge recordset back then process it themselves because the developer didn't understand that sql was anything but a huge heap to hold data in.

Trying to do everything in one huge query instead of breaking it down into sensible steps. Sometimes this will not give SQL a chance to correctly reduce the amount of index (hopefully !) pages that it has to look at. Related to this, not understanding how to use temp tables to hand off manipulations prior to updating large indexed tables.

Not having indexes on joins or commonly queried fields.

Not including fields in indexes when you are always seeking and return the same value (for instance formatted name for an employee when you look up using EmployeeID) causing two actions instead of one.

Answer 13

I've never managed to find much useful information on breaking up large queries, in Oracle it seems your advised more to keep everything together in one query rather than use temp tables. You can also get redo log issues if your temp table is holding a lot of data. I'd like to know more/get some links?

Answer 14

Clauses to avoid (if possible) :

• DISTINCT / UNIQUE
• UNION
• GROUP BY
• ORDER BY