Friday, August 31, 2007

SQL Server the Root of All Evil

SQL Server is the root of all evil.  SQL Server causes more problems, with more applications than any other part of an enterprise system.  It is inefficient, slow to respond, and is the focal point of more performance issues than anything else.


OK, now that the myths are out of the way let's get to reality.  SQL Server is indeed at the center of many performance issues, but not because of the SQL Server product itself, but because of the usage of the product.  Database design is a key factor in how well SQL Server, or any database server for that matter, can respond to a query.  What columns actually need to be in your table?  Determining whether you need a sequential  MBUN, a GUID or a ROWID may seem trivial, but it has tremendous impact on performance.  Database design extends to more than just what columns belong in what table, but what indexes should be created and what columns should be used for clustering.  (P.S.  If your cluster index is a GUID, OUCH.  If you don't cluster on your data, OUCH.)


The physical structure, however, is just one part of the overall solution.  Having short, concise and well constructed stored procedures is very important.  Understanding that TempDB should not be used if a TABLE variable works is very important to understand.  Coming up with project standards, at the beginning of the project with regard to expected response time is important.  On a previous project we had a threshold for stored procedures set at 1 second so that if anything took longer than 1 second we looked into the reason why.  Some people significantly lower that number so that anything over 200 milliseconds gets investigated.  If you've set a limit of 1 second and you've told the user that no screen will take more than 4 seconds, then you know that you can call, at most, 4 stored procedures.


If you are experiencing "problems" with the database, don't automatically assume that it is the fault of the database.  If the design, construction and implementation of the database have a flaw you may experience problems, but they are not the fault of the hardware, nor of the database engine itself.

No comments: