/* Database+Disk+Performance™: Index Covering: Database, Disk IO, and Enterprise Apps */ Database+Disk+Performance™ /* */ /* -- -- */ /* Footer ----------------------------------------------- */ #footer { width:660px; clear:both; margin:0 auto; } #footer hr { display:none; } #footer p { margin:0; padding-top:15px; font:78%/1.6em "Trebuchet MS",Trebuchet,Verdana,Sans-serif; text-transform:uppercase; letter-spacing:.1em; }

Thursday, July 06, 2006

Index Covering: Database, Disk IO, and Enterprise Apps

The Series


This is the first in a series of articles on Index Covering which is a performance technique to cut down disk IO and improve query performance. The first article here will define the basic elements in a query execution plan and how to find the bad parts. The next article will show how to fix them. Finally I'll conclude with some larger examples from PeopleSoft, JD Edwards, Siebel and SAP.


Introduction


Databases have two basic structures for storing information. Index pages and data pages. Both reside on the disk. To achieve optimal performance, DBA’s create indexes which are really just organized subsets of data.


Since an index is a subset of data, extra reads are often needed to find all the additional information. Typically, this requires a bookmark lookup.


These extra reads on a disk can add up quickly and cause poor performance. The extra IOs also cause the database to use cache less efficiently.


What is a bookmark lookup?


Databases have a concept called “book mark lookups”. This means that the database has to find what it needs in the index page, then find the corresponding data page for the rest of the information required.


Think of it like this: A person needs to find George Washington’s birthday. They look in the index of an encyclopedia to find “Washington, George” which tells them to turn to page 98 to find an entry on him. On page 98, they find the birthday. This is an example of a “book mark lookup”. The researcher looks in the index, keeps their finger there, turns to a page in the book, finds the birthday for the rest of the information.


A bookmark lookup occurs when the database cannot find all the needed information for a query in the index page and so it goes to the data page to complete the request.





For example:

create table A (col_one int, col_two int, col_three int)

Go

create index A_P1 on a(col_one, col_two)

Go

[Data added to table…]

Select col_three

From A

Where col_one = ‘22’


In this example, the query would used index A_P1 to find the row pointer to the data page in the table, but since A_P1 index does not contain column col_three, it would have to do a book mark lookup from the index to the data page.


Why are bookmark lookups bad for performance?


Not all bookmark lookups are horrible. It just means that the database had to do some extra IO to find the data it needed. If you are only hitting a few rows, then it’s not an issue. If you are having to sort through hundreds or thousands of rows to find the one row you needed, or are in a massive nested loop, then book mark lookups can be a problem.


There are several examples below which will illustrate the pain in enterprise apps that can be large and small depending on the query.


How do you know you have a bookmark lookup?


A bookmark lookup shows up in the execution plan (ex. set statistics profile on) looking like this:


|--Bookmark Lookup(BOOKMARK:([Bmk1001]),OBJECT:([JDE_CRP].[CRPDTA].[F4311] AS [T1]))


Ok, so I got bookmark lookups, so what?


First, figure out if it’s causing a drag on the system. Look at the number of rows and executions that the bookmark lookup is using. Is it 5 or 50,000? This can be seen on the left hand side of the execution plan using set statistics profile on command.


In this example, zero rows were brought back and the loop was executed only once. This bookmark lookup was not a problem.


Rows Executes StmtText

------ --------- ---------------------------------------------------

0 1 |--Nested Loops(Inner Join,

0 1 |--Sort(ORDER BY:([T0].[ILUKID] DESC))

0 1 | |--Bookmark Lookup(BOOKMARK:([Bmk1000]),

0 1 | |--Index Seek(OBJECT:([JDE_CRP].[CRPDTA].[F4111].[x] AS

0 0 |--Clustered Index Seek(OBJECT:([JDE_CRP].[CRPDTA].[F4311].


Here is an example where a bookmark lookup caused severe problems. It is bringing back 85 million rows and is being executed 4,017 times.


Rows Executes StmtText

----------- ----------- ------------------------------------------------------------

1 1 |--Nested Loops(Inner Join, WHERE:([T1].[PR_POSTN_ID]=

364442 1 | |--Index Scan(OBJECT:([Siebel_Prd].[dbo].

85205956 4017 | |--Bookmark Lookup(BOOKMARK:([Bmk1001])

85205956 4017 | |--Nested Loops(Inner Join, OUTER

4017 4017 | |--Nested Loops(Inner Join, OUTER

4017 4017 | | |--Bookmark Lookup(BOOKMARK:([Bmk1002

4017 4017 | | | |--Nested Loops(Inner Join,

4017 4017 | | | | |--Bookmark Lookup(BOOKMARK:([Bmk

4017 4017 | | | | | |--Index Seek(OBJECT:([Siebel_

4017 4017 | | | | |--Index Seek(OBJECT:(

4017 4017 | | |--Clustered Index Seek(OBJECT:([Siebel

85205956 4017 | |--Index Seek(OBJECT:([Siebel_Prd].[dbo].[S_

11 Comments:

Blogger your said...

phentermine nice :)

9:35 AM  
Blogger Performance Tester said...

I like what you are doing here. I have won some of this knowledge, but it is very nice to see it laid out.

Good post, great blog!

4:08 PM  
Blogger your said...

earn money - tramadol cool blog :)

10:13 AM  
Blogger your said...

earn money - tramadol cool blog :)

10:13 AM  
Blogger your said...

earn money - tramadol cool blog :)

10:13 AM  
Blogger Sandra White said...

phentermine - health insurance - debt consolidation - home equity loans Nice comment.. I ll come back for sure :]

12:16 AM  
Blogger Sandra White said...

phentermine - health insurance - debt consolidation - home equity loans Nice comment.. I ll come back for sure :]

3:03 PM  
Blogger sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,A片,視訊聊天室,聊天室,視訊,視訊聊天室,080苗栗人聊天室,上班族聊天室,成人聊天室,中部人聊天室,一夜情聊天室,情色聊天室,視訊交友網

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,avdvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX,成人論壇

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

2:42 AM  
Blogger  said...

A片,A片,成人網站,成人漫畫,色情,情色網,情色,AV,AV女優,成人影城,成人,色情A片,日本AV,免費成人影片,成人影片,SEX,免費A片,A片下載,免費A片下載,做愛,情色A片,色情影片,H漫,A漫,18成人

a片,色情影片,情色電影,a片,色情,情色網,情色,av,av女優,成人影城,成人,色情a片,日本av,免費成人影片,成人影片,情色a片,sex,免費a片,a片下載,免費a片下載

情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣

A片,A片,A片下載,做愛,成人電影,.18成人,日本A片,情色小說,情色電影,成人影城,自拍,情色論壇,成人論壇,情色貼圖,情色,免費A片,成人,成人網站,成人圖片,AV女優,成人光碟,色情,色情影片,免費A片下載,SEX,AV,色情網站,本土自拍,性愛,成人影片,情色文學,成人文章,成人圖片區,成人貼圖

情色,AV女優,UT聊天室,聊天室,A片,視訊聊天室

6:59 AM  
Blogger freefun0616 said...

酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店經紀,
酒店打工經紀,
制服酒店工作,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
酒店經紀,

,

1:16 PM  
Blogger Chaminda Wijesinghe said...

More tutorials at http://www.itlecturenotes.blogspot.com/

12:17 AM  

Post a Comment

<< Home