Analysis of problems in SQL Server that cause index lookups to become index scans _mssql

What happens in SQL Server that causes its execution plan to turn from index lookup to index Scan (index Scan)? The following from a few aspects of context-specific scenarios to do the next test, summary, induction.

1: An implicit conversion causes the execution plan to change from index lookup to index Scan (index Scan)

Implicit conversion would cause index scan instead of index seek. While implicit conversions occur into SQL Server to allow data evaluations against different data types, they can introduce  The performance problems for specific data type conversions the scan of the. index occurring during. Good design practices and code reviews can easily prevent implicit conversion issues from ever occurring in your the design or Workload.

The following example AdventureWorks2014 Database HumanResources.Employee table, because the Nationalidnumber field type is nvarchar, the following SQL has an implicit conversion, causing it to take an index scan (index Scan)

SELECT Nationalidnumber, loginID from 
HumanResources.Employee 

There are two ways we can avoid the implicit conversion of SQL:

1: Make sure the comparison has the same data type.

2: Use the CAST (explicit conversion) method.

By making sure that the data types of the comparisons are the same, we can let SQL Walk index lookup (index seek) as follows

SELECT Nationalidnumber,
    loginid
from  humanresources.employee

Note: Not all implicit conversions cause index lookup to be indexed (index Scan), implicit conversions that cause index Scans The blog describes implicit conversions between those data types that result in an index scan (Scan). As shown in the following illustration, there is no introduction here.

Some measures and methods to avoid implicit conversion

1: Good design and code specifications (early)

2: Rreview the release script (medium-term)

3: SQL by script query implicitly converted (later)

The following is an SQL statement that searches for an implicit conversion in the database from the execution plan

Set TRANSACTION Isolation level READ UNCOMMITTED DECLARE @dbname SYSNAME Set @dbname = QuoteName (db_name ()); With XmlNamespaces (DEFAULT ' Http://schemas.microsoft.com/sqlserver/2004/07/showplan ') SELECT stmt.value (' (@Stateme Nttext) [1] ', ' varchar (max) '), T.value (' (scalaroperator/identifier/columnreference/@Schema) [1] ', ' varchar (128) '), T . Value (' (scalaroperator/identifier/columnreference/@Table) [1] ', ' varchar (128) '), T.value (' (scalaroperator/ identifier/columnreference/@Column) [1] ', ' varchar (128) '), IC. Data_type as ConvertFrom, IC. Character_maximum_length as Convertfromlength, T.value (' (@DataType) [1] ', ' varchar (128) ') as ConvertTo, T.value (' (@Le Ngth) [1] ', ' int ') as Converttolength, Query_plan from Sys.dm_exec_cached_plans as CP CROSS APPLY SYS.DM_EXEC_QUERY_PL An (plan_handle) as QP CROSS APPLY query_plan.nodes ('/showplanxml/batchsequence/batch/statements/stmtsimple ') as Batch 
(stmt) CROSS APPLY stmt.nodes ('.//convert[@Implicit = ' 1 "] ') as N (t) JOIN informAtion_schema. COLUMNS as IC on QuoteName (IC. TABLE_SCHEMA) = T.value (' (scalaroperator/identifier/columnreference/@Schema) [1] ', ' varchar (128) ') and QuoteName (IC. TABLE_NAME) = T.value (' (scalaroperator/identifier/columnreference/@Table) [1] ', ' varchar (128) ') and IC. COLUMN_NAME = T.value (' (scalaroperator/identifier/columnreference/@Column) [1] ', ' varchar (128) ') WHERE t.exist (' scalaroperator/identifier/columnreference[@Database =sql:variable ("@dbname") [@Schema!= "[sys]"] = 1

2: A SARG predicate causes the execution plan to change from index lookup to index Scan (index Scan)

SARG (searchable Arguments) is also called the query parameter, which is defined as an operation that restricts the search because it usually refers to a specific match, a match within a range of values, or a two or more conditional and join. The most typical case of a statement that does not satisfy the Sarg form is a statement that includes a non operator, such as not,!=, <>;,!<;,!>;not EXISTS, don't in, and not, and so on, as in predicates using functions, predicates, and so on.

2.1: Indexed fields using functions can cause index scans (index Scan)

SELECT Nationalidnumber,
    loginid
from  humanresources.employee
WHERE SUBSTRING (Nationalidnumber, 1, 3) = ' 112 '

2.2 An indexed Field Operation results in an index Scan (index Scan)

Performing an operation on an indexed field field causes the execution plan to turn from index lookup to index Scan (index Scan):

SELECT * from Person.person WHERE BusinessEntityID + < 260

Generally try to avoid this, and if you can, try to logically convert the SQL (see below). Although this example seems very simple, but in practice, still see many such cases, just as many people know that smoking is harmful to health, but they can't quit! Many people may know this, but in practice they still make the mistake. The truth is so!

SELECT * from Person.person WHERE BusinessEntityID < 250

2.3 Like fuzzy query back causes index Scan (index Scan)

Whether a like statement belongs to a SARG depends on the type of wildcard character used, as the "condition%" belongs to Sarg, like '%condition ' is not SARG predicate operation

SELECT * from Person.person WHERE LastName like ' ma% '

SELECT * from Person.person WHERE LastName like '%ma% '

3:sql queries returning data pages (pages) to critical points (tipping point) can result in index scans (index Scan) or table scans (table Scan)

What is the tipping point?
It's the point where the number of rows returned is "No longer selective enough". SQL Server chooses nonclustered index to look up the corresponding data rows and instead performs a table s Can.

With regard to the critical point, let's start with a vivid example: tipping.

SET NOCOUNT on;
DROP TABLE test
CREATE TABLE Test (object_id INT, NAME VARCHAR (8));
CREATE INDEX pk_test on TEST (object_id)
DECLARE @Index INT =1;
While @Index <= 10000
BEGIN
  inserts into TEST
  SELECT @Index, ' Kerry ';
  SET @Index = @Index +1;
End
UPDATE STATISTICS TEST with Fullscan;
SELECT * from TEST WHERE object_id= 1

As shown above, when we query object_id=1 data, the optimizer uses index lookup (index Seek)

There is only one object_id=1 data above, what if Object_id=1 's data reaches 20% of the total amount of data in the whole table? We can update 2001 data manually. At this point, the SQL execution plan becomes a full table scan (table Scan).

UPDATE TEST SET object_id =1 WHERE object_id<=2000;
UPDATE STATISTICS TEST with Fullscan;
SELECT * from TEST WHERE object_id= 1

The critical point determines whether SQL Server uses a bookmark lookup or a full table/index scan. This also means that the critical point is only related to the non-overlay, nonclustered index (emphasis).

Why is the tipping point interesting?
It shows that narrow (non-covering) nonclustered indexes have fewer uses than often expected (just because a query has a C Olumn in the WHERE clause doesn ' t mean which SQL Server ' s going to use that index
It happens at a point that's typically much earlier than expected ... and, in fact, sometimes this is a VERY bad thing!
Only nonclustered indexes of that does not cover a query have a tipping point. Covering indexes don ' t have this same issue (which further proves why ' re so they for important performance)
You might find larger tables/queries performing table scans as in fact, it might is better to use a nonclustered index. How do you know, how do I test, how do you hint and/or force ... and, is that a good thing?

4: Missing or incorrect statistics will result in index scans (index Scan)

Missing or incorrect statistics can easily cause index lookup to become an index Scan (index Scan). This is very easy to understand, but the construction of such a case is more difficult, for a moment did not think, here skipped.

5: Predicate is not the first column of the Union index will cause index Scan (index Scan)

SELECT * into sales.salesorderdetail_tmp from Sales.SalesOrderDetail;
CREATE INDEX pk_salesorderdetail_tmp on sales.salesorderdetail_tmp (SalesOrderID, salesorderdetailid);
UPDATE STATISTICS sales.salesorderdetail_tmp with Fullscan;

The following SQL statement results in a consistent result, but the second SQL statement causes the index scan to be the first column of the joint index because the predicate is not a union

SELECT * from Sales.salesorderdetail_tmp
WHERE salesorderid=43659 and salesorderdetailid<10

SELECT * from Sales.salesorderdetail_tmp WHERE salesorderdetailid<10