How to debug database query for performance

Question

I am completely new in databases and SQL queries.

Q1. What are the common problems commonly encountered in database queries? Probably extremely large queries, performance problems, timeout and connection errors, import and recovery errors.

Q2. What approach and tools do you need to be aware of debugging SQL query performance?

It will be great if you can share some of the problems and how you have moved forward to solve it.

Answer 1

Try using EXPLAIN before your queries, which will give you an excellent overview of what is happening under the hood.

.: SQL

Answer 2

, , , - . , , . , , , , (), .

, , SQL Server , SQL Profiler. SQL Management Studio, , .

Answer 3

, , .

, , , 3-4 . , , :

SELECT max(now() - xact_start) FROM pg_stat_activity
                               WHERE state IN ('idle in transaction', 'active');

, . .

Answer 4

SQL- - SQL Server Profiler http://msdn.microsoft.com/en-us/library/ms187929.aspx

, , ..

, . http://msdn.microsoft.com/en-us/library/ms178071.aspx

Answer 5

, . . , , , , , , . , , .

-

- ,

- . , - (, EAV), , , , , , , , .

, , - - ( , , ) . SOmetimes , , datbase. concurrency .

- ( , , , ..)

- SQl , , , .

Answer 6

, SQL- , :

Answer 7

, , , , , - ( ), , , - .

, , , , , , , , .

- - , .

For queries, use explanation plans that let you determine how the database engine will run your queries. (This will determine how the unions will be performed, and in what order and in the strength of your unions - if you can reduce the attitude of many to many in your unions, your request will work faster)