In my last post, I talked about how I measure query performances. Let’s talk about some of the best practices around query design so that we do not have to run into such issues frequently.
When I am writing code or reviewing someone else’s code then I follow the following rules. Do not bring data that you do not need.
Fundamentals
- Avoid network overhead with SET NOCOUNT ON. This will not return the number of rows returned after you execute a stored procedure or a batch.
- Instead of using COUNT(*) to verify if data exists, use EXISTS instead. COUNT(*) will scan all the rows and burn more resources. EXISTS on the other hand stops when it finds the first record matching the criteria.
- Be careful about implicit data type conversion. Surely there will be variables in your queries. You will compare certain columns and if the data types are different but compatible, SQL Server will implicitly convert those data types. Boom, there goes your performance out the window.
- Avoid using non-sargable search conditions, arithmetic expressions, and functions. When trying to fit in all the user requests in an application search box, we end up with open-ended search conditions such as
LIKE ‘%INeedEverything’. These kinds of searches will prevent query optimizers from using the indexes and will scan the entire table or your clustered index. Using arithmetic operators (WHERE TableA.Rate * 10 = 20) and functions (WHERE SUBSTRING(TableA.Name,1,1) = 'A') will certainly hurt your query performance. - Table variable vs temporary tables. My rule of thumb is if you know your result sets will be less than 20 records then I am OK using table variables. One benefit of using it is no statistics are created for table variables so recompilation will have no impact. However, if the result set is huge, then you run into serious issues, and then I recommend using Temporary Tables.
- Almost forgot, always explicitly qualify the owner of the objects. It will help avoid object name delays during query compilation.
- Minimize using cursors. SQL is all based on set and trying to work on a row level is counterintuitive. Your I/O will pay a big penalty so I recommend cursor usage to the minimum.
- Put those UDFs Inline. SQL Server will not like your queries using user-defined functions and may choose not to go parallel. RBAR isn’t all that good unless you love firing up a query and going out for a coffee. Take out the code from your UDF and put it into your query itself, screw the good old logic of code reuse via UDFs. Or take the chance with SQL Server 2019 which claims that it will inline UDFs.
Let’s move a gear
- Cache and reuse execution plans
Having the execution plans in the cache and using them repeatedly helps a query perform consistently. You can avoid ad hoc queries and parameterize them, sp_executesql is awesome. However, there are still so many reasons for recompilation like schema changes, changes in statistics, recompile hint, etc. Analyze reasons for recompilation using extended events and try to minimize them following any possible best practices. - Optimizer hints
Make no mistake I do not recommend using query hints unless you know what you are doing. When in doubt trust SQL Engine to make the right decision for you. Hints need to be tested thoroughly but you still run the risk of behavior change with changes in SQL versions. There are cases when the optimizer keeps producing inefficient plans. This is when a courageous database dude can put in a query hint like MAXDOP to avoid the unnecessary wait on parallelism. However if not tested and done without knowing the downstream impact, it can cause some serious heartburn. Use the NOLOCK hint and allow the application to read the dirty data. Imagine users seeing duplicate records, applications crying foul, and then the data got rolled back, should be fun. - Handle your Database Transactions the right way
Your queries should not be blocking one another and try to keep the scope of your transaction as short as possible. Make sure to implement error handling using TRY/CATCH and do not leave a possibility of having an open transaction.
