|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
What is bookmark lookupHello there
On the execution plan i see sometimes Bookmark lookup, who takes a lot of presantage of the query execution What that meens and how can i reduce it? Roy
That means , SQL Server have to use pointers from nonclustered index to the actual data rather than read the data directly from NCI Yes, it may be costly so you'll ve have to idenify such queries and take care about it. See if bookmark lookup is using SELECT *, which should never be used. Creating CI (using in WHERE clause) or/and Covering indexes are good choices to avoid bookmark lookup . Show quote "Roy Goldhammer" <r**@hotmail.com> wrote in message news:OXww8OO8FHA.2132@TK2MSFTNGP10.phx.gbl... > Hello there > > On the execution plan i see sometimes Bookmark lookup, who takes a lot of > presantage of the query execution > > What that meens and how can i reduce it? > > Simplified ;)?
It means that sql server need to go to the disk to retrieve data. He has a key (cluster indeks or row ID) and he needs to read the rest of the record. You can avoid it by 'covering' the query or indeksing fields involved in it. MC Show quote "Roy Goldhammer" <r**@hotmail.com> wrote in message news:OXww8OO8FHA.2132@TK2MSFTNGP10.phx.gbl... > Hello there > > On the execution plan i see sometimes Bookmark lookup, who takes a lot of > presantage of the query execution > > What that meens and how can i reduce it? > > What do you mean by saying 'covering'
Show quote news:uPH9jUO8FHA.3952@TK2MSFTNGP12.phx.gbl... > Simplified ;)? > It means that sql server need to go to the disk to retrieve data. He has a > key (cluster indeks or row ID) and he needs to read the rest of the > record. You can avoid it by 'covering' the query or indeksing fields > involved in it. > > > MC > > > "Roy Goldhammer" <r**@hotmail.com> wrote in message > news:OXww8OO8FHA.2132@TK2MSFTNGP10.phx.gbl... >> Hello there >> >> On the execution plan i see sometimes Bookmark lookup, who takes a lot of >> presantage of the query execution >> >> What that meens and how can i reduce it? >> >> > > Covering query is when you select fields that are present in indexes.
I meant that if you indeks the fields involved in the query bookmark lookups will go away. MC Show quote "Roy Goldhammer" <r**@hotmail.com> wrote in message news:%23uV1qYO8FHA.2576@TK2MSFTNGP12.phx.gbl... > What do you mean by saying 'covering' > > news:uPH9jUO8FHA.3952@TK2MSFTNGP12.phx.gbl... >> Simplified ;)? >> It means that sql server need to go to the disk to retrieve data. He has >> a key (cluster indeks or row ID) and he needs to read the rest of the >> record. You can avoid it by 'covering' the query or indeksing fields >> involved in it. >> >> >> MC >> >> >> "Roy Goldhammer" <r**@hotmail.com> wrote in message >> news:OXww8OO8FHA.2132@TK2MSFTNGP10.phx.gbl... >>> Hello there >>> >>> On the execution plan i see sometimes Bookmark lookup, who takes a lot >>> of presantage of the query execution >>> >>> What that meens and how can i reduce it? >>> >>> >> >> > > Think of a table that has a clustered index and a nonclustered index...
create table blah ( datecol datetime not null, id int not null, notes text ) create unique clustered index clidx on blah ( datecol ) create index ncidx on blah ( id ) When you do something like... select notes from blah where id = 1234 This will use the ncidx on id to get 1234, in order to get the notes column it must access the base table (you've only accessed the ncidx so far), it does that using the 'bookmark lookup', the ncidx actually contains the key that makes up the clustering index, in the above case 'datecol', so the actual index ncidx is id, datecol; the query then uses datecol as the bookmark and does a lookup to get the actual data 'notes'. Hope that helps. It doesn't necessarily need to go to disk, if the data is in cache it will get it from there. Show quote "Roy Goldhammer" <r**@hotmail.com> wrote in message news:OXww8OO8FHA.2132@TK2MSFTNGP10.phx.gbl... > Hello there > > On the execution plan i see sometimes Bookmark lookup, who takes a lot of > presantage of the query execution > > What that meens and how can i reduce it? > > Tony
Simple and smartly Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:e$M59uO8FHA.500@TK2MSFTNGP15.phx.gbl... > Think of a table that has a clustered index and a nonclustered index... > > create table blah ( > datecol datetime not null, > id int not null, > notes text > ) > > create unique clustered index clidx on blah ( datecol ) > create index ncidx on blah ( id ) > > When you do something like... > > select notes > from blah > where id = 1234 > > This will use the ncidx on id to get 1234, in order to get the notes > column it must access the base table (you've only accessed the ncidx so > far), it does that using the 'bookmark lookup', the ncidx actually > contains the key that makes up the clustering index, in the above case > 'datecol', so the actual index ncidx is id, datecol; the query then uses > datecol as the bookmark and does a lookup to get the actual data 'notes'. > > Hope that helps. > > It doesn't necessarily need to go to disk, if the data is in cache it will > get it from there. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "Roy Goldhammer" <r**@hotmail.com> wrote in message > news:OXww8OO8FHA.2132@TK2MSFTNGP10.phx.gbl... >> Hello there >> >> On the execution plan i see sometimes Bookmark lookup, who takes a lot of >> presantage of the query execution >> >> What that meens and how can i reduce it? >> >> > > Thanks Uri - its seriously unsual for me ;) I usually end up confusing
myself! Tony. Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:u$DAuyO8FHA.476@TK2MSFTNGP15.phx.gbl... > Tony > Simple and smartly > > > > > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:e$M59uO8FHA.500@TK2MSFTNGP15.phx.gbl... >> Think of a table that has a clustered index and a nonclustered index... >> >> create table blah ( >> datecol datetime not null, >> id int not null, >> notes text >> ) >> >> create unique clustered index clidx on blah ( datecol ) >> create index ncidx on blah ( id ) >> >> When you do something like... >> >> select notes >> from blah >> where id = 1234 >> >> This will use the ncidx on id to get 1234, in order to get the notes >> column it must access the base table (you've only accessed the ncidx so >> far), it does that using the 'bookmark lookup', the ncidx actually >> contains the key that makes up the clustering index, in the above case >> 'datecol', so the actual index ncidx is id, datecol; the query then uses >> datecol as the bookmark and does a lookup to get the actual data 'notes'. >> >> Hope that helps. >> >> It doesn't necessarily need to go to disk, if the data is in cache it >> will get it from there. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "Roy Goldhammer" <r**@hotmail.com> wrote in message >> news:OXww8OO8FHA.2132@TK2MSFTNGP10.phx.gbl... >>> Hello there >>> >>> On the execution plan i see sometimes Bookmark lookup, who takes a lot >>> of presantage of the query execution >>> >>> What that meens and how can i reduce it? >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics