Through the years I have amassed some tips and trick to make SQL Server run more efficiently. For right now, I would like to tell you about some common mistakes that developers make, I know I have made some of these!
Tip 1 – It’s all about the cached execution plan!
Ok so SQL Server has execution plans, big deal, what does this mean to you? Here’s what you need to know, for every query you execute SQL Server determines the best possible way of getting data from the database files. For some very complex queries, the possibilities could be well into the millions! To save time executing repeated queries, it stores the best possible way of executing these queries in a cache.
Those are the details but here is how to take advantage of this. Store the queries you are using in Stored Procedures. This will help SQL Server store the execution plan. There is one secret to this, assign the parameters to local variables within the Stored Procedures!
Tip 2 – Reduce Network Traffic!
You may have heard that Select * is a bad thing well turns out that there a number of reasons. The big one extra packets are getting sent from the SQL Server. Secondly it also increases disk I/O on the server. In SQL Server, less is more. Also if you SET NOCOUNT ON, the server won’t send messages back to your program throughout it’s execution so traffic is further reduced.
Tip 3 – Set the Initial Database Size Accordingly!
If you set it too small, as the database expands, the data file(s) will grow in size but not necessarily together. These files will become fragmented on the disk. This results in more disk operations, which unfortunately result in more CPU usage as well as more disk operations. Sizing it appropriately will prevent this fragmentation from occurring for the most part. If you feel that you databases are too fragmented you could rebuild the databases or detach them from the SQL Server engine and run a disk defrag on the disk where the database files are stored.
There is another aspect of this. Set the growth increments large enough. If the increments are too small you will create more fragmentation for the database.
Tip 4 – Use Indexes!
Use indexes where your “where” clauses point to. When indexes are not used the query has to scan every record in the table to find the results. If it is indexed, SQL Server scans through the index (containing just the field(s) that you need). Which results in less disk I/O and much faster results. Don’t do this on fields that are constantly written over because because writing to the field has two writes (one in the table and one in the index). Also constant changes can lead to index fragmentation, which will slow down queries where many rows are returned.
That’s it for now but more will be posted in the future. If you found this useful, please subscribe.