What is the difference between a seek and a scan? It can be quite a lot, actually!
By definition, a scan will touch on every row in the table or index and retrieve only those rows which match the query criteria. On the other hand, a seek will use available indexes to locate the data.
One thing to note is that I mentioned that a seek will use available indexes. While a table may have an index, SQL Server may still choose to perform a scan if the scan is considered less expensive – for example, a small table may be subject to a scan over a seek.
Read more…
You may have seen the term SARGable in blogs or forums and wondered what it means. To start with, SARGable is pseudo-acronym which stands for Search ARGument. A query is considered sargable if the WHERE clause can take advantage of an index to speed up the query. A non-sargable query implies that the WHERE clause (or part of it) cannot use any available indexes which could result in a table or index scan and possibly slow down query performance.
Read more…
As databases evolve, you may find that some indexes are no longer used. This is something I have seen a lot in my current job.
This script will allow you to see which indexes are used and whether they are used for seeks, scans or key lookups.
SELECT
SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]) AS [Object Name],
I.[name] AS [Index Name],
S.[user_seeks] AS [User Seeks],
S.[user_scans] AS [User Scans],
S.[user_lookups] AS [User Lookups]
FROM sys.dm_db_index_usage_stats S INNER JOIN sys.indexes I
ON I.[object_id] = S.[object_id] AND I.[index_id] = S.[index_id]
INNER JOIN sys.tables T
ON T.[object_id] = I.[object_id]
INNER JOIN sys.schemas SC
ON T.[schema_id] = SC.[schema_id]
WHERE
OBJECTPROPERTY(S.[object_id],’IsUserTable’) = 1 AND
OBJECTPROPERTY(S.[object_id], ‘IsMSShipped’) = 0 AND
S.[database_id] = DB_ID(DB_NAME())
ORDER BY
SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]), I.[index_id]