In the world of SQL query optimization, performance is everything. As databases grow in size, the need for efficient queries becomes even more critical. When optimizing SQL queries, two key concepts you need to understand are scan and seek. These operations can significantly impact the speed and efficiency of your queries. The thumb rule for optimization is simple: “Scans are bad, Seeks are good.” In this post, we’ll break down these operations and explore why SQL query optimization should focus on utilizing seek operations whenever possible for better performance.

Yes, that’s the thumb rule to target while optimizing SQL Queries “Scans are bad, Seeks are good“.

Scan indicates reading the whole of the index/table looking for matches – the time this takes is proportional to the size of the index.

Seek, on the other hand, indicates b-tree structure of the index to seek directly to matching records – time taken is only proportional to the number of matching records.

In general an index seek is preferable to an index scan (when the number of matching records is proportionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the total number of records in your table. However, in certain situations, an index scan can be faster than an index seek (sometimes significantly faster) – usually when the table is very small, or when a large percentage of the records match the predicate.

Table Scans: With no indexes defined/available at all that are relevant to your query the planner is forced to use a table scan meaning that every row is looked at. This can result in every page relating to the table’s data being read from disk (worst case scenario). However, for some queries, table scan is used even when a useful index is present – this is usually because the data in the table is so small that it is more hassle to traverse the indexes.

Index Scans: Reads the entire index—all the rows in the index order and occurs when an index exists that is only partially helpful.The index might only be partially helpful if there is relatively low selectivity in the distribution of the data. Index scan means the DB reads the data in an Index directly, when it finds what it wants in the Index , it uses the record addresses in the index to go to the table and read only what it requires.There’s a performance advantage to an index scan because an index tends to be much narrower than the full table column.

Clustered Index Scan: In a table without a clustered index (a heap table), data pages are not linked together – so traversing pages requires a lookup into the Index Allocation Map. A clustered table, however, has it’s data pages linked in a doubly linked list – making sequential scans a bit faster. Of course, in exchange, you have the overhead of dealing with keeping the data pages in order on INSERTs, UPDATEs, and DELETEs. A heap table, however, requires a second write to the Index Allocation Map.

Index Seeks: traverses a B-tree and walks through leaf nodes seeking only the matching or qualifying rows based on the filter criteria.  Only relevant index pages need to be read instead of every page in the index (or table).

Clustered Indexes: With a clustered index the table data is stored in the leaf nodes of that index instead of being in a separate heap structure. This means that there will never need to be any extra row lookups after finding rows using that index no matter what columns are needed [unless you have off-page data like TEXT columns or VARCHAR(MAX) columns containing large object (LOB) data types]. You can have only one clustered index for this reason, so chose the one  carefully in order to get maximum gain.

SQL Server Query Optimization Best Practices

1. Ensure Proper Indexing: Ensure that the columns frequently used in your queries (e.g., in WHERE clauses or JOIN operations) have proper indexes.

2. Use Index Seeks Whenever Possible: Index seeks are significantly more efficient than table scans. Try to optimize your queries to utilize index seeks rather than scans.

3. Avoid Unnecessary Indexes: Too many indexes can degrade performance. Keep your indexing strategy aligned with your query patterns.

4. Analyze Execution Plans: Regularly use SQL Server Management Studio (SSMS) or Azure SQL Database tools to analyze query execution plans and identify bottlenecks.

5. Maintain Updated Statistics: Ensure your statistics are up to date, as outdated statistics can lead to suboptimal query plans.

Refer SQL Server Best Practices

Conclusion:
When it comes to SQL query optimization, understanding the difference between scan and seek operations is key to improving the performance of your queries. While table scans and index scans may be acceptable in some cases, index seeks are generally more efficient, especially as your database grows in size. By carefully managing your indexes and optimizing your queries to prioritize seek operations, you can significantly improve performance and reduce the load on your system.

Regular analysis of query execution plans, proper indexing, and ongoing maintenance of your database will help you fine-tune your SQL queries for maximum performance. So, whether you are working with a small or large dataset, always keep the principle of “scans are bad, seeks are good” in mind to ensure you’re making the most out of your SQL queries.