Top 10 SQL Server performance bottlenecks

Question

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

Answer 1

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 2

table scan because:

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

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

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

Answer 5

The Applying scalar function to each row in the result set

SELECT fn_EveluateSomeValue([ColumnName]) FROM LargeTable

Answer 6

Using cursors. For any database.

Answer 7

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 8

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 9

Some low-hanging fruit:

• Scalar UDFs
• omitting text() in XPath queries
• missing columns in indices (causing key lookups)
• using cursors
  • not using fast_forward when possible when cursors are necessary
• using JOIN when APPLY will do, or vice versa (test!)

Answer 10

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

Answer 11

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 12

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 13

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 14

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 15

For us, adjusting the BIOS Settings and Power Plan settings resulted in solid ~2X faster times for queries that were CPU intensive (searching, duration about 5s).

1) BIOS Settings changed from "Performance Per Watt Optimized (DAPC)" to either "Performance" or "Performance Per Watt Optimized (OS)". Both options seemed to perform similarly however the "OS" controlled one did not run the fan at max continuously.

2) The Windows power plan was changed from "Balanced” to “High performance”.

This was discovered after upgrading the CPUs and seeing almost no improvement.

Command to check CPU speed from SQLServerCentral:

PS> gwmi -class win32_Processor | SELECT CurrentClockSpeed, MaxClockSpeed

Update 6/13/2017: Warning: That number may report Current = Max however Task Manager may show a a different (much lower) number. Also the BIOS mode "Performance Per Watt Optimized (OS)" suddenly stopped working for us after some hardware changes. The CPU was locked at 50% max frequency, regardless of load Only "Performance" mode (loud fan mode) still achieved max CPU. We must be missing a firmware/software patch.

Answer 16

faulty hardware. I had a server once where the hard drive had some sectors that it was having difficulty reading and we replacing it had a noticeable performance increase.

Answer 17

Clauses to avoid (if possible) :

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