Don’t Blame the Server; It’s Probably Your Code
ThomVF (Talk | contribs)
(New page: 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,...)
Next diff →
Revision as of 03:15, 25 November 2009
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:
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.
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-style, 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:
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 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.
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!