Don’t Blame the Server; It’s Probably Your Code

From WikiContent

(Difference between revisions)
Jump to: navigation, search
Current revision (00:27, 3 December 2009) (edit) (undo)
 
(3 intermediate revisions not shown.)
Line 1: Line 1:
Sure, your server is very busy but don't assume that it's the only reason your SQL query seems to take forever to return data. Even a busy server can return results efficiently. In fact, sometimes a busy server can be a SQL developer's best friend (I'll explain why below).
Sure, your server is very busy but don't assume that it's the only reason your SQL query seems to take forever to return data. Even a busy server can return results efficiently. In fact, sometimes a busy server can be a SQL developer's best friend (I'll explain why below).
-
There are a couple SQL "indiscretions" that you should check for in your query before complaining to the DBA or System Administrator:
+
There are some common SQL "indiscretions" that you should check for in your query before complaining to the DBA or System Administrator:
'''Cartesian Products''': Have you defined join criteria for each table in your SQL? If you forget one of them, and it happens to be a large table, your query will suffer the fate of “''Charlie on the MTA''” and will “''never return''”.
'''Cartesian Products''': Have you defined join criteria for each table in your SQL? If you forget one of them, and it happens to be a large table, your query will suffer the fate of “''Charlie on the MTA''” and will “''never return''”.
-
'''Indexes''': Hopefully, your tables have primary keys and your SQL is utilizing their corresponding indexes. However, your SQL may reference other columns that are not part of an index. An ''Explain Plan'' on your SQL will tell you which indexes are being used. You’ll need to decide if they are appropriate and if you need to add or change indexes to support better performance. Use indexes to avoid Full-Table scans.
+
'''Indexes''': Hopefully, your tables have primary keys and your SQL is utilizing their corresponding indexes. However, your SQL may reference other columns which are not part of an index. An ''Explain Plan'' on your SQL will tell you which indexes are being used. You’ll need to decide if they are appropriate and if you need to add or change indexes to support better performance. Use indexes to avoid Full-Table scans.
-
'''Hints''': These provide a way to direct the optimzer to modify the way it executes your SQL. You can use these to force a specific index to be used, use a different level of parallelism, use a different join-type, etc.
+
'''Hints''': These provide a way to direct the optimizer to modify the way it executes your SQL. You can use these to force a specific index to be used, use a different level of parallelism, use a different join-type, etc.
----
----
-
There are a couple other things that should always be checked before blaming the server, once you have resolved the previous issues. Although not specifically SQL-related, these often tend to be trouble areas:
+
There are a couple other key things that should be checked, once you have resolved the previous issues. Although not specifically query-related, these often tend to cause trouble:
'''Statistics''': These are often overlooked when investigating poorly performing SQL. You may have dutifully resolved all of your joins, and have a wonderfully-crafted indexing strategy, but without updated statistics, you may still experience poor performance. When properly updated, statistics tell the database optimizer all it needs to know about your tables and indexes to make the best choices for executing the SQL. ''This is something you may need to ask your DBA about.''
'''Statistics''': These are often overlooked when investigating poorly performing SQL. You may have dutifully resolved all of your joins, and have a wonderfully-crafted indexing strategy, but without updated statistics, you may still experience poor performance. When properly updated, statistics tell the database optimizer all it needs to know about your tables and indexes to make the best choices for executing the SQL. ''This is something you may need to ask your DBA about.''
Line 17: Line 17:
As you can see, there are a number of things that can be checked before blaming a "busy server" for your poorly performing SQL. You can usually manage the first few items on your own, while the others may require some discussion and support from your DBA.
As you can see, there are a number of things that can be checked before blaming a "busy server" for your poorly performing SQL. You can usually manage the first few items on your own, while the others may require some discussion and support from your DBA.
-
By the way, I mentioned above that a “busy server can be a SQL developer’s best friend” and promised to explain that. A busy server means that data is likely being retrieved and changed with high frequency. Under these conditions, it is more likely that some of the data you need is already in memory! This means that the optimizer doesn’t need to take the extra step of locating and grabbing it from the disks, which are slower than memory. So, sometime a busy server can be a good thing for your query performance!
+
Too often, IT departments respond to poor database performance by adding CPU or Memory hardware, which can be very expensive. It should now be clear that unless you have checked and resolved all of the above items, the additional hardware may be unnecessary.
 +
 
 +
By the way, I mentioned above that a “busy server can be a SQL developer’s best friend” and promised to explain that. A busy server means that data is likely being retrieved and changed with high frequency. Under these conditions, it is more likely that the data you need is already in memory! This means that the database doesn’t need to take the extra step of locating and grabbing the data from the disks, which are slower than memory. So, sometime a busy server can be a good thing for your query performance!
 +
 
 +
This work is licensed under a Creative Commons Attribution 3
 +
 
 +
Return to [[97_Things_SQL]]

Current revision

Sure, your server is very busy but don't assume that it's the only reason your SQL query seems to take forever to return data. Even a busy server can return results efficiently. In fact, sometimes a busy server can be a SQL developer's best friend (I'll explain why below).

There are some common SQL "indiscretions" that you should check for in your query before complaining to the DBA or System Administrator:

Cartesian Products: Have you defined join criteria for each table in your SQL? If you forget one of them, and it happens to be a large table, your query will suffer the fate of “Charlie on the MTA” and will “never return”.

Indexes: Hopefully, your tables have primary keys and your SQL is utilizing their corresponding indexes. However, your SQL may reference other columns which are not part of an index. An Explain Plan on your SQL will tell you which indexes are being used. You’ll need to decide if they are appropriate and if you need to add or change indexes to support better performance. Use indexes to avoid Full-Table scans.

Hints: These provide a way to direct the optimizer to modify the way it executes your SQL. You can use these to force a specific index to be used, use a different level of parallelism, use a different join-type, etc.


There are a couple other key things that should be checked, once you have resolved the previous issues. Although not specifically query-related, these often tend to cause trouble:

Statistics: These are often overlooked when investigating poorly performing SQL. You may have dutifully resolved all of your joins, and have a wonderfully-crafted indexing strategy, but without updated statistics, you may still experience poor performance. When properly updated, statistics tell the database optimizer all it needs to know about your tables and indexes to make the best choices for executing the SQL. This is something you may need to ask your DBA about.

Reorganization: In the beginning, when it was first loaded, your data was nicely and neatly organized inside your tables. However, over time rows got deleted, causing your files to look like Swiss cheese. Some rows were updated and split into pieces because of a lack of available real estate (chaining). Every so often, tables often need to be reorganized so that the optimizer doesn’t need to wildly jump around the database files collecting your data. This is something you need to ask your DBA about.


As you can see, there are a number of things that can be checked before blaming a "busy server" for your poorly performing SQL. You can usually manage the first few items on your own, while the others may require some discussion and support from your DBA.

Too often, IT departments respond to poor database performance by adding CPU or Memory hardware, which can be very expensive. It should now be clear that unless you have checked and resolved all of the above items, the additional hardware may be unnecessary.

By the way, I mentioned above that a “busy server can be a SQL developer’s best friend” and promised to explain that. A busy server means that data is likely being retrieved and changed with high frequency. Under these conditions, it is more likely that the data you need is already in memory! This means that the database doesn’t need to take the extra step of locating and grabbing the data from the disks, which are slower than memory. So, sometime a busy server can be a good thing for your query performance!

This work is licensed under a Creative Commons Attribution 3

Return to 97_Things_SQL

Personal tools