SQL Server query performance optimization-index and SARG (2)

 

SQL Server query performance optimization-index and SARG (I)

For non-SARG statements, SQL SERVER must evaluate each record to determine whether it meets the WHERE clause conditions. Therefore, indexes are usually useless for queries using non-SARG conditions. A non-SARG statement usually contains the following operations: NOT ,! =, <>,!> ,! <, Not exists, not in, not like, and "% IS %" mentioned above. The use of LIKE will cause a full table SCAN (tarle scan) or clustering scan to reduce performance.

Create SQL Server query performance optimization-index and SARG (1) Create Index 1 and 3 in the first part of the Index

-- Example 1: SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where COP_G_NO like '% 79'

 

Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 1, logical reads 1306, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.

 

 

 

-- Example 2: SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] with (index (idx_wbk_pde_list_cop_g_no) where COP_G_NO like '123456'

 

 

Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 1, logical reads 925, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.

 

 

----- Example 3: SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where COP_G_NO like '123'

 

Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 1, logical reads 1306, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.

 

 

Use Index	Query statement	Query the number of records	Execution cost
Full table Scan	Example 1	916	1.03687
Specify non-clustered Index	Example 2	919	2.24756
Use clustered Index	Example 3	919	1.03687

From the examples 1 and 3 in the above table, we can see that the execution costs of full table scan and clustered index scan are the same, or the difference is not big. In Example 2, the specified non-clustered index is used. Because the data is searched using a non-clustered index, an RID key value (or Bookup Lookup) is randomly performed, therefore, the higher the number of query records, the higher the execution cost.

 

 

The following describes some common WHERE clause improper usage scenarios and discusses the more correct usage:

. Perform operations on the WHERE clause.
. Negative query.
. Use a function for the WHERE clause.
. Use OR.

I. Do notWHERE clause performs operations
First, compare SARG and non-SARG with a simple example. You can use Managemenet studio to check whether the optimizer can effectively analyze the statement.

 1. No Index

 

--- Example 1: SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] WHERE wbook_no + G_NO = 'be40491095002011844 'in where
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where wbook_no = 'be4049000050020' and g_no = '2016'

 

 1) I/O read/write count queried by Examples 1 and 2

Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 1, logical reads 1306, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.

 2) Cost of the query execution plan for example 1 and Example 2

 

 

 

2. Create only clustered indexes.

ALTER TABLE [dbo].[WBK_PDE_LIST_ORG_HISTROY] ADD  CONSTRAINT [PK_WBK_PDE_LIST_ORG_HISTROY] PRIMARY KEY CLUSTERED ([WBOOK_NO] ASC,[G_NO] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]GO

 

 1) I/O read/write count queried by Examples 1 and 2

Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 1, logical reads 1314, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.

 

 2) Cost of the query execution plan for example 1 and Example 2

 

 

3Create SQL Server query performance optimization-index and SARG (1) Create Index 1 (Clustered index) and index 5 (non-clustered index)

 

 

 

 

 1) Example 1 is the same as above. 2.

2) Example 2: IO read/write count

Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 0, logical read 3, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.

 3) Example 2: query execution plan cost

 

Significant differences can be seen from the three execution plans.

Because the syntax of SARG allows the query optimization program to directly compare condition constants, it can effectively use the clustered index or non-clustered index created on the column Name field in the WHERE clause. Instead of SARG, You need to perform operations to determine whether the data meets the requirements. As a result, indexes cannot be directly used. Therefore, the clustered index scan, full table scan, and index scan modes are used.

Because the first query statement does not conform to the SARG format, you can see that the query optimization query does not effectively use the index.

 

In case 1 and Case 2, the full table scan method is used for query optimization queries. It can be seen that the entire data table has 1306 pages,

The query statement reads 1306 times logically, which is similar to the total data page and scans the entire table. As shown in the query plan.

In the second case, because the idx_WBK_PDE_LIST_COP_G_NO index is missing, only the clustered index PK_WBK_PDE_LIST_ORG_HISTROY is available. We can see that the index page of PK_WBK_PDE_LIST_ORG_HISTROY has a total of 1306 pages, and the logic of this query statement is 1314 times, which is similar to the total index page and also a clustered index scan, as shown in the query plan.

In the third case, the fields in the condition following the WHERE clause are indexed because they comply with the SARG format, so you can see that the query optimization program will effectively use the previously created clustered index (Index 1) for clustered index search. Fast.