SQL Server table Optimization Primer One

Table Scan, index Scan, index lookup three differences

We often hear this three noun in our daily work, what does it mean? Under what circumstances will the above situation occur? And how can we optimize our SQL execution more efficiently when it comes to the above situation? Here we take these 3 questions into our topic "SQL Server table index optimization Primer One"

First look at a SQL, from this SQL we introduce each of these three concepts

SELECT orderid,orderphonehash,seriesname from dbo. Order_info_back WHERE seriesname= ' Wing Tigers '

Table Scan:

Look at the table scan, and then look at the design of our watch.

We found out. No primary key does not have any index, so the query for this table is a table scan.

Index Scan:

Here we add a clustered index to this table

ALTER TABLE [dbo]. [Order_info_back] ADD CONSTRAINT [Pk_order_info_back] PRIMARY KEY CLUSTERED
(
[ID] ASC
)

Then execute the SQL statement above to see what happens.

The table scan becomes an index scan, which is exactly what the clustered index scan should be. At this time, we see that the performance of SQL execution is not any improvement, why? Conceptually, a table scan is to scan a row of data one row at a time and then return the result, and the index scan is the area where the index is first found and then the data is traversed to return the result. So the performance should be improved in this way, the reason why we do not improve the performance of this example is because we do not index our query criteria, so even after the index scan is equivalent to the whole table traversal, performance is not much improvement.

Well, we're adding a nonclustered index to our table.

Index Lookup:

CREATE nonclustered INDEX [ix_orderinfo_seriesname] on [dbo]. [Order_info_back]
(
[Seriesname] Asc
)

Execute the above SQL.

I am this performance improvement apparent logical reading from the original 5952 into 17 quality leap up 350 times times, but we will find that there is a keylookup (Clustered) This thing consumes 80% of our performance, can we optimize this thing?

Well, first of all, understand why this stuff is happening. The query plan uses the "Ix_orderinfo_seriesname" nonclustered index we created to perform the lookup return result, and for Orderid,orderphonehash this two field is not part of the index. The query engine must return to the base table and query again and return the results. This process creates a key Lookup. Well now that you know how this thing is produced, then the following to eliminate this key Lookup, it is easy to let the query engine not check again. What to do? The legendary overlay index is to add the fields of the Select to the index, so you can try to find out.

CREATE nonclustered INDEX [ix_orderinfo_seriesname] on [dbo]. [Order_info_back]
(
[OrderId] Asc
[Orderphonehash] Asc
[Seriesname] Asc
)

Execute SQL

Sure as we expected key Lookup eliminated, but at this time we found that the logical read up, the above index lookup becomes an index scan, it is better not to eliminate. Why do you add a few more indexed fields index lookup becomes an index scan. Here you can guess why.

After a practical attempt to find out, the order of index lookup and Index scan and index fields is related to the field of where or on we adjust to the previous and then execute the next SQL to see the results

The magical discovery not only eliminates Key lookup but also becomes an index lookup, and only 3 of the performance of the logical read is increased by the top 17 of the above into 3

The index contains:

The above SQL is basically optimized to the extreme, but we all know that there are too many indexes to create problems. The performance of insertions, deletions, and modifications is affected. Is there a way to reduce the impact and the performance will not be affected? The answer, of course, is "indexed containment."

First on SQL

CREATE nonclustered INDEX [ix_orderinfo_seriesname] on [dbo]. [Order_info_back]
(
[Seriesname] Asc
)
INCLUDE ([OrderId],
[Orderphonehash])

Execute SQL

The magical discovery logic reads again and again, this time the real optimization to the extreme.

As to the difference between index and index inclusion, this concerns the underlying storage of SQL Server data, which is explained later.

SQL Server table Optimization Primer One