What resources exist for Database performance-tuning?

Question

What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area?

The idea of this question is to collect the shed load of resources that invariably exist, so that people can have a "one stop" knowledge shop of the good, peer approved resources.

---

General SQL

• Book: SQL Performance Tuning
• Book: SQL Tuning
• Book: The Art of SQL
• Book: Refactoring SQL Applications
• Book: Database tuning: principles, experiments, and troubleshooting techniques
• Use The Index, Luke! - A Guide to database performance for developers

PostgreSQL (wiki) (PGsearch)

• Performance Optimization
• Quick PostgreSQL Optimization
• Explain Analyze Interpreter
• PostgreSQL Performance Tips

MySQL

• Hidden Features of MySQL
• Book: High Performance MySQL / Blog: High Performance MySQL
• Blog: Xaprb (for MySQL DBAs)

Oracle

• How do you interpret a query's explain plan?
• Oracle Advanced Tuning Scripts
• Oracle Database Performance Tuning Guide
• Ask Tom
• Oracle Database SQL Reference
• Book: Understanding Oracle Performance
• Book: Optimizing Oracle Performance
• Book: Troubleshooting Oracle Performance
• Book: Cost-Based Oracle Fundamentals

MS SQL Server

• SQL Server Performance
• eBook: High Performance SQL Server
• SO Question: What are the best SQL Server performance optimization techniques?
• Brent Ozar's Perf Tuning Page
• SqlServerPedia's Perf Tuning Page
• Book: Sql Server 2008 Internals
• How to Identify Slow Running Queries with SQL Profiler

Sybase SQL Anywhere

• Blog: SQL Anywhere
• Book: SQL Anywhere Studio 9 Developer's Guide

JDBC

• JDBC Performance Tips

Answer 1

SO has a good one here: How do you interpret a query’s explain plan?

Answer 2

This guy's answer to a not-the-same-inquiry is probably a good start.

http://stackoverflow.com/questions/368858/hidden-features-of-mysql/600830#600830

Answer 3

If you are looking for SQL Server specific Performance tuning references there are an absolute shed load of quality resources available online, ranging from white papers on implementing specific technologies such as partitioning, to excellent Blogs that detail step by step instruction on how to performance tune a sql server platform.

Shameless plug follows: You can start you research by reviewing the performance tuning area of my personal Blog, or for any specific SQL Server requirements/issues feel free to fire me an email.

SQL Server Resources

Answer 4

For MySQL, the performance tuning 'bible' is High Performance MySQL

Answer 5

"SQL Performance Tuning" http://books.google.com/books?id=3H9CC54qYeEC&dq=sql+performance+tuning&printsec=frontcover&source=bn&hl=en&ei=1dDoSYmjMOrlnQfX-bSYBw&sa=X&oi=book_result&ct=result&resnum=4 covers most of the major DBMS -- how to write high performing cross platform SQL queries, etc.

Answer 6

And something for PostgreSQL: "Performance Optimization" at the official wiki.

Answer 7

SQL Server Performance Decent site for MSSQL specific info.

Answer 8

A lot of good MySQL specific tips can be found at http://www.mysqlperformanceblog.com/

Answer 9

Xaprb is a must-read blog for MySQL DBAs. The author has written a book on high-performance MySQL

For the happy few working with Sybase SQL Anywhere I can only recommend Breck Carter's blog and his SQL Anywhere Studio 9 Developer's Guide

Answer 10

Quick PostgreSQL Optimization (query optimizing)

Short read, explains a lot of things well and 'works' a real example which is nice for those of us that learn better that way.

After seeing the wiki link to PostgreSQL, figured I'd edit this post with links for mysql/oracle docs, not really an optimization guides specifically but both are good resources, especially the mysql one. For optimization and any other tuning features.

Answer 11

http://www.javaperformancetuning.com/tips/jdbc.shtml
http://www.google.com/search?q=database+performance+tuning

Answer 12

Here is another highly-regarded book that is platform-neutral:

Dan Tow's SQL Tuning: Generating Optimal Execution Plans

Contains some specific examples for Oracle, MS SQL, and IBM DB2, but the techniques involved should apply to other platforms, too.

Answer 13

If you are using an Oracle database, this guide may also help. http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/toc.htm

Answer 14

For SQL Server, I primarily use:

• Brent Ozar's Perf Tuning Page

• SqlServerPedia's Perf Tuning Page

Answer 15

Oracle's very own Tom Kyte has a fantastic repository on every type of performance problem imaginable on http://asktom.oracle.com. He usually takes the time to recreate specific problems and gives very detailed explanations.

Answer 16

I would add that besides having your database theoretically tuned, you should profile your application using a profiler that tracks SQL calls.

Despite your best intentions, a few bad calls will sneak into your application and will often cause 90% of your performance-related problems.

Answer 17

• Book: Troubleshooting Oracle Performance (Antognini Christian)

Answer 18

For people working with Oracle I recommend this link.............

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

From my experiences with Oracle database development, I have found that building up a knowledge of how to use SQL, how it works and knowing what is available (supplied functions, clauses that you didn't know existed or enhanced from the last version) means I spend a lot less time having to tune sql.

Answer 19

http://explain.depesz.com/

• helps you interpret PostgreSQL's EXPLAIN ANALYZE output.

The whole Performance Tips chapter in the PostgreSQL docs is worth reading.

Answer 20

I'd start out by understanding how the database works at a fundamental level. How is data stored on disk, what does creating an index do, how does query plan optimization work, how are plans cached, when to cached plans expire.

If you can commit all that to memory, most of the advice about tuning seems obvious.

Here's a great book for MSSQL

SQL Server Internals

Answer 21

I was pretty happy when I saw this way of quickly seeing what happened with a SQL statement you are tuning under Oracle. Change the first SQL statement below to your SELECT statement and keep that hint in there.

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM DUAL;

SELECT * FROM TABLE(dbms_xplan.display_cursor( NULL, NULL, 'RUNSTATS_LAST'))
;

PLAN_TABLE_OUTPUT
-----------------------------------------------------
SQL_ID  5z36y0tq909a8, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM DUAL

Plan hash value: 272002086

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |      1 |00:00:00.02 |       3 |      2 |
---------------------------------------------------------------------------------------------


12 rows selected.

Where:

• E-Rows is estimated rows.
• A-Rows is actual rows.
• A-Time is actual time.
• Buffers is actual buffers.

Where the estimated plan varies from the actual execution by orders of magnitude, you know you have problems.

Answer 22

How to Identify Slow Running Queries with SQL Profiler is a good tutorial on how to go about identifying slow running queries. This will allow one to focus their attention where it is most needed.

Answer 23

http://kedar.nitty-witty.com/blog/perl-script-for-analyze-optimize-repair-mysql-databases/

Answer 24

Another good link that i would like to share is : Tips for improving and tuning sql server database

Answer 25

For Oracle, Cost-Based Oracle: Fundamentals by Jonathan Lewis.