SQL Server Performance Forum – Threads Archive

finding bookmark in SQL 2005

I understand that if there’s a clustered index, there won’t be any RowID, because we can find the row based on clustered index. So, since there’s no bookmark in Query plan in SQL 2005, and there’s a clustered index, it’s very hard to find the bookmark. Am I understanding this correctly?
Thanks

When data is located using a non-clustered index, SQL Server uses a bookmark lookup operation to jump from the non-clustered index to the clustered data pages. This becomes significant when the query is retrieving multiple rows that may be located in the same page in a non-clustered index, but spread out over the clustered index. Rows that might have been identified from two non-clustered index pages are then fully retrieved using the bookmark lookup to retrieve data from perhaps dozens of data pages. Also fyi, within SQL Server 2005 the covering index no longer carries the overhead of slower updates. http://www.sql-server-performance.com/da_benchmark_covering_indexes.asp may get you some information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks Satya, yes you’re right about that. what I was saying is that if you have a clustered idex, SQL 2005 won’t show you RID (bookmark), at least it didn’t for me. So, if your table is not a heap, as in most cases, how do you see if there’s a bookmark being used?

See Craig’s blog<a target="_blank" href=http://blogs.msdn.com/craigfr/archive/2006/06/30/652639.aspx>http://blogs.msdn.com/craigfr/archive/2006/06/30/652639.aspx</a> in this csae for the explanation of how it uses when index is used. If not we have to get clarification from Kimberly Tripp [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

Thanks!

So, did you get what you are looking? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>