The performance damage of bookmarks in SQL Server, sqlserver

The performance damage of bookmarks in SQL Server, sqlserver

In my blog, I used to talk about searching for bookmarks in SQL Serverl, and there are many problems they bring about. In today's article, I want to further talk about bookmarking from the perspective of performance, and how they can reduce the performance of your entire SQL Server.

Bookmarksearch-repeated Loop

If your non-clustered index is not a covered non-clustered index, the SQL Server Query Optimizer will introduce the bookmarks for searching. For each row returned from a non-clustered index, SQL Server needs to perform additional search operations in the clustered index or heap table.

For example, if your clustered index contains three layers, you need to read three additional pages for each row to return the required information. Therefore, the query optimizer selects the bookmarkup search operation in the execution plan only when it makes sense-based on the selection degree of your query. Shows the execution plan of the bookmarked search operation.

Generally, people do not pay too much attention to bookmarks because they only execute a few times. If your query selection is too low, the query optimizer uses clustered index scanning or table scanning operators to directly scan the entire table. However, only the cache execution plan is reused on SQL Server. This plan has different running values, including the bookmarked query (based on the initial input values ), therefore, this situation can easily occur, and the bookmarked search is executed repeatedly.

To demonstrate this performance problem, the query optimizer I specified in the next query uses a specific non-clustered index. The query itself returns 80000 rows, because for each query to be executed, the SQL Server needs to perform a bookmarked query for 80000 times-repeated execution.

CREATE PROCEDURE RetrieveDataAS SELECT * FROM Table1 WITH (INDEX(idxTable1_Column2)) WHERE Column3 = 2GO

Shows the actual execution plan after the query is executed.

The execution plan looks terrible (the query optimizer even enables parallel plans !), Because the bookmarked search operator executes 80000 times here, the query itself generates more than 165000 logical reads! (The number of logical reads can be obtained from statistic io ).

Next we will show you what happens to SQL Server when many parallel users execute this bad query. I will use ostress.exe (part of the RML tool) to simulate queries for 100 concurrent users.

ostress.exe -Q”EXEC BookmarkLookupsPerformance.dbo.RetrieveData” -n100 -q

It took nearly 15 seconds to complete 100 parallel queries on my testing system. During this period, the CPU usage is very high, because SQL Server requires nested loop operators to perform bookmarking search. Nested loop operations occupy CPU resources.

Now let's modify the index design to create a non-clustered index for this query. With non-clustered indexes, the query optimizer does not need to perform bookmarks in the execution plan. The same results can be returned for a non-clustered index query:

CREATE NONCLUSTERED INDEX idxTable1_Column2 ON Table1(Column3)INCLUDE (Column2)WITH (DROP_EXISTING = ON)GO

Now we use ostress.exe to execute the same query again. We can see that each query is completed within 5 seconds. It is very different from the 15 seconds we just saw. This is the power to overwrite non-clustered indexes: In our queries, the data requested by the valve can be directly found in the non-clustered index, so you can avoid bookmarking.

Summary

In this article, I show you that poor bookmarkdoning may damage your performance. Therefore, it is very important for important queries to quickly complete the query-but the execution plan for searching with parallel bookmarks is not a good choice. Covering non-clustered indexes here can help you. You can consider this method when designing an index next time.

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!