Troubleshooting query performance can be a frustrating task for developers. The goal of this series is to break down the steps to analyze a query and provide concrete actions that can be used to create performance oriented queries.
When trying to improve the performance of a query you will almost always be working on some or all of the following high level tasks:
- Minimize network traffic
- Minimize physical I/O
- Minimize logical I/O
- Minimize CPU usage
- Perform set-based operations
Remember – queries are optimized in relation to the current system and database. This means that performance begins before a single line of code is written and before a single byte of data is inserted. Disk setup, network setup, server setup, data model, indexes and other databases on the system all impact performance (this also means that your query impacts the other queries or even entire databases running on the same server).
Writing efficient queries also means less troubleshooting in the future.