Systems Engineering and RDBMS

Myths or so called “Rules of Thumb”

Posted by decipherinfosys on April 19, 2008

Those who have worked for some time in the IT industry know that there are many exceptions to the so called “Rules of Thumb”. That is why in many scenarios, if you ask an IT expert a question, the answer will be: It Depends 🙂 and no, the person is not trying to beat around the bush, he/she is actually trying to explain how the different situations effect that scenario and why the answer might be a different one depending upon a particular client situation. Yesterday, while having a discussion with one of the senior resources, he suggested that we should write something about such “Rules of Thumb” that exist in the DB world. We will list some of them in this post and then in subsequent posts, pick them one by one and show examples where the rules of thumb get violated. In addition, there are a lot of Myths out there which even senior consultants seem to propagate at client sites…here are some of them:

1) FTS (Full Table Scans) are always bad and Index usage is always good.

2) Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.

3) Empty Space in an index that gets created due to the DML operations do not get used.

4) Indexes should be rebuilt at regular intervals.

5) Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.

6) Usage of cursors is always bad so avoid them like the plague.

7) Truncate command cannot be rolled back because it is a non-logged operation.

8 ) Table variables in SQL Server are always only memory resident.

9) Column order in a covered index does not matter.

10) In the case of SQL Server, one can separate the clustered index from the table.

11) Only committed data gets written to the disk.

12) Logical IOs (LIO) are not a cause of concern, only Physical IO (PIO) are.

13) Count(1) is better performing than count(*).

14) Issue frequent commits in the application to make the transaction faster and also improve concurrency.

15) Views are evil evil DB Objects that always slow down performance.

There are many many more myths that are out there. We just put down some of them over here and will start writing about each one in subsequent blogs giving examples and code samples to show the exceptions to the so called “Rules of Thumb” and the myths. One should always question the “experts” – everything should be proved using sample pieces of code if it sounds fishy. It is more of a science rather than an art guys and using code samples and understanding the internals and the boundary conditions is as important as understanding the context in which the recommendations are being applied.

One Response to “Myths or so called “Rules of Thumb””

  1. […] to be accessing the data and then decide how the composite index needs to be formed. No simple rule of thumb applies as you have seen from this […]

Sorry, the comment form is closed at this time.

%d bloggers like this: