In SQL SERVER, index search becomes an index scan problem analysis, sqlserver

In SQL SERVER, index search becomes an index scan problem analysis, sqlserver

In SQL Server, what will cause the execution plan to change from Index search to Index Scan? The following describes the test, summary, and induction based on the specific context scenarios.

1: implicit conversion will change the execution plan from Index search to Index Scan)

Implicit Conversion will cause index scan instead of index seek. while implicit conversions occur in SQL Server to allow data evaluations against different data types, they can introduce performance problems for specific data type conversions that result in an index scan occurring during the execution. good design practices and code reviews can easily prevent implicit conversion issues from ever occurring in your design or workload.

In the following example, because the NationalIDNumber field type in the HumanResources. Employee table of the AdventureWorks2014 database is NVARCHAR, the following SQL statements undergo implicit conversion, resulting in Index Scan)

SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber = 112457891 

We can avoid implicit SQL conversion in two ways:

1: Make sure that the two data types are the same.

2: Use the explicit conversion method.

After we make sure that the data types of the two items are the same, we can let the SQL statements take Index search (Index Seek) as follows:

SELECT nationalidnumber,    loginidFROM  humanresources.employeeWHERE nationalidnumber = N'112457891' 

Note: not all implicit conversions will cause Index search to become Index Scan ), implicit Conversions that cause Index Scans blog introduces the Implicit conversion between data types that will cause Index Scan ). As shown in, we will not introduce it too much here.

Some measures and methods to avoid implicit conversion

1: good design and code specifications (early stage)

2: Perform Rreview (medium stage) on the release script)

3: use scripts to query implicitly converted SQL statements (later stage)

The following is an SQL statement used to search for implicit conversions from the execution plan of the database.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDDECLARE @dbname SYSNAME SET @dbname = QUOTENAME(DB_NAME());WITH XMLNAMESPACES   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT   stmt.value('(@StatementText)[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('(@Length)[1]', 'int') AS ConvertToLength,   query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(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: Non-SARG predicates will cause the execution plan to change from Index search to Index Scan)

Searchable Arguments is also called a query parameter. It is defined as an operation used to restrict the search, because it usually refers to a specific match, match within the range of a value or the AND join of two or more conditions. The most typical case for statements that do NOT meet the requirements of the SARG format is to include non-operator statements, such as: NOT ,! =, <> ;,! <;,!>; Not exists, not in, not like, and so on. IN addition, there are operations such as using functions and predicates IN predicates.

2.1: Index Scan is performed when the Index field is used as a function)

SELECT nationalidnumber,    loginidFROM  humanresources.employeeWHERE SUBSTRING(nationalidnumber,1,3) = '112'

2.2 Index Scan is performed on Index fields)

The Calculation of Index field fields will result in the execution plan changing from Index search to Index Scan ):

SELECT * FROM Person.Person WHERE BusinessEntityID + 10 < 260

Avoid this situation as much as possible. If possible, convert the SQL logic as much as possible (as shown below ). Although this example looks very simple, I have seen many such cases in practice, just as many people know that smoking is harmful to health, but they cannot quit! Many people may understand this, but they still make this mistake in practice. That's why!

SELECT * FROM Person.Person WHERE BusinessEntityID < 250

2.3 Index Scan caused by LIKE fuzzy query)

Whether the Like statement belongs to the SARG depends on the type of the wildcard used. LIKE 'condition % 'belongs to the SARG, LIKE' % Condition 'is a non-SARG predicate operation.

SELECT * FROM Person.Person WHERE LastName LIKE 'Ma%'

SELECT * FROM Person.Person WHERE LastName LIKE '%Ma%'

3: When the data page (Pages) returned by an SQL query reaches the critical Point (Tipping Point), Index Scan or Table Scan may occur)

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

With regard to Tipping Point, we will not tangle with the concept below. Let's start with a fresh example:

SET NOCOUNT ON;DROP TABLE TESTCREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(8));CREATE INDEX PK_TEST ON TEST(OBJECT_ID)DECLARE @Index INT =1;WHILE @Index <= 10000BEGIN  INSERT INTO TEST  SELECT @Index, 'kerry';  SET @Index = @Index +1;ENDUPDATE STATISTICS TEST WITH FULLSCAN;SELECT * FROM TEST WHERE OBJECT_ID= 1

As shown above, when we query data with OBJECT_ID = 1, the optimizer uses Index search (Index Seek)

The preceding OBJECT_ID = 1 contains only one data entry. What if OBJECT_ID = 1's data reaches 20% of the total table data size? We can manually update 2001 data records. At this time, the SQL Execution Plan is changed to full 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 bookmarks for searching or full table/index scanning. This also means that the critical point is only related to non-covered and non-clustered indexes (important ).

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 column in the WHERE clause doesn't mean that 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 that do not cover a query have a tipping point. Covering indexes don't have this same issue (which further proves why they're so important for performance tuning)
You might find larger tables/queries grouping Ming table scans when in fact, it might be better to use a nonclustered index. how do you know, how do you test, how do you hint and/or force... And, is that a good thing?

4: Missing or incorrect statistical information may cause Index Scan)

If the statistical information is missing or incorrect, Index search (Index Seek) is easily changed to Index Scan ). This is easy to understand, but it is difficult to construct such a case. I did not think of it for a moment. I skipped it here.

5: If the predicate is not the first column of the Union Index, Index Scan will occur)

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 returns the same results, but the second SQL statement causes index scanning because the predicate is not the first column of the Union index.

SELECT * FROM Sales.SalesOrderDetail_TmpWHERE SalesOrderID=43659 AND SalesOrderDetailID<10

SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID<10