Up until a few years ago, I’ll admit to having never been bitten by the parameter sniffing performance issue and if I am honest, I had not even heard of it. I got bitten by this yesterday
Read more…
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…