Software Consultants Sharing SQL Knowledge
As we’ve mentioned before, Entrance recently started a lunch and learn series where our software consultants share custom software development best practices with their teammates. We are currently doing a three-part series on SQL Server tuning and I wanted to share some of that discussion here.
A well thought-out database is very important for overall efficiency, functionality and responsiveness of search. Tables in a database can just be created with no organization at all and this is called a heap table. In order to find a row in a heap table you have to check every single row in the table.
From a high level perspective, seeks and scans, or how SQL Server finds things in a database, are relatively simple concepts to explain. A visual demonstration of this would be an unsorted phone book. If you needed the phone number of an individual, you would have to read through the entire phone book until you found it, this would be akin to a database table scan.
After doing this for a while, it starts to grate on you and then you likely decide to sort your phone book by last name and put all the last and first name combinations into an index in the front of the phone book. By doing this, the next time you need to find Mr. Jones’ phone number, you can easily search for his last name in your list by doing a binary search. This would be an example of a database index seek.
Everything is going well until one day you discover sometimes all you know is you need Bob’s phone number, but you don’t know Bob’s last name. Fortunately, there is still an advantage to be gained by having your index. You can scan over the index far faster than over the entire phonebook because the index contains far less information and many more names can fit on a sheet of paper. Because of this you are able to find Bob Wilson’s phone number much faster. This is also an example of an index scan.
The main point you should take away from this analogy is scans are read intensive and it is in the best interest of the database to minimize them. During the lunch and learn, we took an example database, and by simply creating a single index from our heap table, reduced our overhead dramatically from over 9 scans and 17,000 reads down to 1 scan and 7 reads. While for a single user this only shows up as an improvement in search of a few milliseconds, if hundreds of thousands of users are all searching the database concurrently, the server will be able to do a lot more with the same amount of resources.
Next week we will cover why it’s important to have a maintenance plan to keep your database up to date. Check out more Tips and Tricks from our software developers…