I get asked to teach junior developers about SQL Server on a frequent basis.   I had an interesting question posed to me yesterday that I thought was very astute of the developer.    He asked “What is so different about a database server versus any other platform”

This is actually a pet peeve of mine.    Some developers don’t treat it any differently and I think database servers should be seen as a sacred item.      I re-wrote one query written by a VB programmer many years ago that was taking 7 hours to complete..    My new query ran in 30 seconds.. 

Even though the “server” is multi threaded,  when it comes down to row level access, no matter how you look at it, that row can’t be shared when performing updates.

For this reason alone, you should ALWAYS be concerned about optimizing that task you are currently coding.      Most developers can code a Select or Insert.  But, do you stop and take the time to ask yourself if it really is the most efficient way to perform the task?  I know I know, it doesn’t always matter, but if you develop the discipline to at least think about it, you’ll be a much better database developer.

Quoting some much smarter people than myself, 80% of performance issues are related to the code and NOT the infrastructure.   

My recommendations:

Check out http://www.sql-server-performance.com

If you are using transactions in your stored procedures, try to make sure that only Inserts, Updates and Deletes and error handling are between the BEGIN TRANSACTION and COMMIT TRANSACTION.       This is not always possible, but at least ask yourself if it is possible.      When you issue that BEGIN TRANSACTION, the Lock Manager puts up that big red stop sign and no-one else gets through until you are done.   So if you have one Insert, 10 reads and then a second insert, you‘ve dramatically extended that Lock.

If you are writing a complex select statement, be sure to use the Estimated Execution Plan in Query Analyzer.     You don’t have to understand all the actions that are performed to get a benefit from the Execution Plan.   Visually take a look at the size of the arrows and the estimated row counts.    If you have 10 steps and the final row count is 2 rows, but you carry 7 million rows through each step until the last one,   you can probably write the query differently to make it perform better.

Performance is what you make of it!!