SQL Server Performance

Performance Optimization eBook

Discussion in 'Forum Announcements' started by vbkenya, Aug 23, 2003.

  1. vbkenya New Member

  2. trifunk New Member

    Wow, I obviously didn't get the first post, what a cool find.

    Cheers
    Shaun

    World Domination Through Superior Software
  3. bradmcgehee New Member

    I had listed this free offer in my news release page, but I don't think a lot of people ever check this section of the website out.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. gaurav_bindlish New Member

    Hey brad, what's the URL?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. satya Moderator

    Brad

    I believe it would be nice if you can set another forum for such free offers, as forum is more popular and strong now with visitors.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. gaurav_bindlish New Member

    I agree with Satya on this.

    BTW, the idea of starting forum for European Collegues was very good.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. FrankKalis Moderator

    Hi there,

    just came across this thread about the ebook.

    And I have a problem with one statement there.

    In chapter 8 'Always have a clustered index' it states


    quote:
    In fact, if you create a nonclustered index and don#%92t already have a clustered index, SQL Server has to create a “phantom” clustered index anyway because nonclustered indexes always point to clustered index keys.

    I'm not sure if this is correct.



    Cheers,
    Frank
  8. gaurav_bindlish New Member

    This statement is perfect. Inside SQL Server 2K says that even if there is no clustered index on a table, SQL Server internally creates a phantom clustered index on the same for faster data retrieval. Note this clustered index is different from the regular clustered index in the way that it doesn't actually sort the data.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  9. FrankKalis Moderator

    quote:
    This statement is perfect. Inside SQL Server 2K says that even if there is no clustered index on a table, SQL Server internally creates a phantom clustered index on the same for faster data retrieval. Note this clustered index is different from the regular clustered index in the way that it doesn't actually sort the data.
    well, doesn't it even show up somewhere?
    Hm...I can't find a test I did some time ago, but I remember I didn't came across a clustered index.

    Anyway, if InsideSQL Server 2k this says, it should be OK. Which page?

    Cheers,
    Frank
  10. vbkenya New Member

    Can't seem to find that reference to a "phantom" clustered index in Delaney's Book.

    I have always thought that non-clustered indexes at the leaf level use a unique RID composed of the File ID, Page ID and the Slot ID to point to the actual data pages.

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  11. gaurav_bindlish New Member

    I have a correction here...

    I confused the creation of unique index with creation of clustered index. The fact is that when an clustered index is created on a table, SQL Server internally attaches a value to the duplicate entries to make them unique. The fact that i mentioned that there is a phantom index created is incorrect.

    Sorry bout that...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  12. Twan New Member


    Also since SQL7 a non-clustered index has at its lowest level the key values for the clustered index, this is easy to verify using the dbcc commands mentioned below. If there is no clustered index then it still does not seem to have the pointer to the actual data... so there must be some form of 'phantom' type index... I've not been able to find out much about it though...

    and a clustered index definitely does not sort the actual data within a single page. Instead the table ends up being near-sorted...

    Cheers
    Twan

    (check out DBCC PAGE, DBCC TAB and DBCC IND)
  13. FrankKalis Moderator

    Now, finally found what I was looking for. <br /><br />First I created a table like so.<br /><br />CREATE TABLE [tbl_AgentList] (<br />[CCID] [int] NOT NULL ,<br />[AgentID] [int] NOT NULL ,<br />[AgentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,<br />[DteTme] [datetime] NOT NULL CONSTRAINT [DF_tbl_AgentList_DteTme] DEFAULT (getdate()),<br />CONSTRAINT [IX_tbl_AgentList] UNIQUE NONCLUSTERED <br />(<br />[CCID],<br />[AgentID]<br />) ON [PRIMARY] <br />) ON [PRIMARY]<br />GO<br />Then filled it will 632 rows.<br />Once done I ran <br />select * from sysindexes where [id] = object_id('tbl_AgentList') AND [name] = 'IX_tbl_AgentList'<br />and got the value from the column "first"<br />0x1F1A00000100<br />next I run <br />dbcc traceon(3604)<br />so I can actually get the data back from DBCC PAGE.<br />Then I transalated the value from first above to it's page value <br />Page File<br />1F1A0000 0100<br />Convert<br />1A1F0000 0001<br />or<br />6687 1<br />So page 6687 on filegroup 1<br />And thus I run <br />DBCC page(dbname,filegroup,page,option)<br />I prefer the layout of option 3<br />DBCC page('score',1,6687,3) --Score is my db name<br />and I see the following example data<br /><br />PAGE: (1:6687)<br />--------------<br />BUFFER:<br />-------<br />BUF @0x00DA3180<br />---------------<br />bpage = 0x1B5EC000 bhash = 0x00000000 bpageno = (1:6687)<br />bdbid = 6 breferences = 3 bstat = 0xb<br />bspin = 0 bnext = 0x00000000 <br />PAGE HEADER:<br />------------<br />Page @0x1B5EC000<br />----------------<br />m_pageId = (1:6687) m_headerVersion = 1 m_type = 2<br />m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4<br />m_objId = 1364915934 m_indexId = 5 m_prevPage = (0:0)<br />m_nextPage = (1:6717) pminlen = 17 m_slotCnt = 426<br />m_freeCnt = 2 m_freeData = 7338 m_reservedCnt = 0<br />m_lsn = (6245:121:10<img src='/community/emoticons/emotion-11.gif' alt='8)' /> m_xactReserved = 0 m_xdesId = (0:0)<br />m_ghostRecCnt = 0 m_tornBits = 0 <br />Allocation Status<br />-----------------<br />GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED <br />PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED<br />ML (1:7) = NOT MIN_LOGGED <br />FileId PageId Row Level CCID AgentID ? <br />------ ----------- ------ ------ ----------- ----------- ------------------ <br />1 6687 0 0 NULL NULL NULL<br />1 6687 1 0 2271 20126 0xD601000001000100<br />1 6687 2 0 2271 20127 0xD601000001000200<br />1 6687 3 0 2271 20128 0xD601000001000300<br />1 6687 4 0 2271 20130 0xD601000001000400--I want this record<br />1 6687 5 0 2271 20131 0xD601000001000500<br />1 6687 6 0 2271 20132 0xD601000001000600<br />1 6687 7 0 2271 20133 0xD601000001000700<br />1 6687 8 0 2271 20145 0xD601000001000800<br />1 6687 9 0 2271 20147 0xD601000001000900<br />1 6687 10 0 2271 20148 0xD601000001000A00<br />1 6687 11 0 2271 20149 0xD601000001000B00<br />1 6687 12 0 2271 20150 0xD601000001000C00<br />1 6687 13 0 2271 20151 0xD601000001000D00<br />1 6687 14 0 2271 20152 0xD601000001000E00<br />1 6687 15 0 2271 21500 0xD601000001000F00<br />1 6687 16 0 2271 21502 0xD601000001001000<br />(Note: 0xD601000001000400<br />D6010000 0100 0400<br />Page File RowInThisIndex)<br />So the record I want has CCID 2271 and AgentID 20130 and its value D60100000100 leads me to Page 470 and filegroup 1<br />and thus I run<br />DBCC page('score',1,470,3)<br />PAGE: (1:470)<br />-------------<br />BUFFER:<br />-------<br />BUF @0x00D9D100<br />---------------<br />bpage = 0x1B2E8000 bhash = 0x00000000 bpageno = (1:470)<br />bdbid = 6 breferences = 23 bstat = 0x49<br />bspin = 0 bnext = 0x00000000 <br />PAGE HEADER:<br />------------<br />Page @0x1B2E8000<br />----------------<br />m_pageId = (1:470) m_headerVersion = 1 m_type = 1<br />m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0<br />m_objId = 1364915934 m_indexId = 0 m_prevPage = (0:0)<br />m_nextPage = (1:159) pminlen = 20 m_slotCnt = 117<br />m_freeCnt = 3055 m_freeData = 5029 m_reservedCnt = 0<br />m_lsn = (5355:386:2) m_xactReserved = 0 m_xdesId = (0:1591211)<br />m_ghostRecCnt = 0 m_tornBits = 805439525 <br />Allocation Status<br />-----------------<br />GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED <br />PFS (1:1) = 0x62 MIXED_EXT ALLOCATED 80_PCT_FULL DIFF (1:6) = CHANGED<br />ML (1:7) = NOT MIN_LOGGED <br />...Data removed always starts at Slot 0...<br />Slot 4 Offset 0xfe3<br />-------------------<br />Record Type = PRIMARY_RECORD <br />Record Attributes = NULL_BITMAP VARIABLE_COLUMNS <br />1B2E8FE3: 00140030 000008df 00004ea2 00a414fa 0........N......<br />1B2E8FF3: 000092cc 01000004 45002a00 72617764 .........*.Edwar<br />1B2E9003: 202c7364 6e616d41 6164 ds, Amanda<br />CCID = 2271 <br />AgentID = 20130 <br />AgentName = Edwards, Amanda <br />DteTme = Nov 22 2002 9:57AM<br />...Data removed in this case it ended at slot 116 so a total of 117 records on the page...<br />DBCC execution completed. If DBCC printed error messages, contact your system administrator.<br /><br />I did not cross any clustered indexes with this non-clustered index. <br />So I think the statement in the eBook<br /><br /><br /><br />Cheers,<br />Frank
  14. Twan New Member


    Wow well done Frank!

    It took me a few minutes to read this and work through how you got from one step to the other. But believe it or not I follow it, and concur that it looks like a heap does not have a phantom clustered index...

    Cheers
    Twan

    PS for those who don't get the jump from D6010000 0100 0400 to page 470, file 1, slot 4

    the order of each word has to be changed so that you get 000001D6 0001 0004 which is 470 1 4 in decimal
  15. FrankKalis Moderator

    In fact the only thing that this shows is that you shouldn't rely too much on only one source of information and watch what is going on with open eyes [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Cheers,<br />Frank
  16. bambola New Member

    You are right, Frank.
    Great research!

    Bambola.

  17. vbkenya New Member

    The research confirms what most DBAs hold as truth about indexes.<br /><br />I think the quote in the e-book was grossly misinterpreted. The "phantom clustered index" used by the non-clustered index is actually the unique pointer composed of the <b>File<img src='/community/emoticons/emotion-4.gif' alt=':p' />age<img src='/community/emoticons/emotion-7.gif' alt=':S' />lot</b> combination. Yes, it is phantom (it is not exactly an index) and does not sort the data but points to it (like most indexes are supposed to do). <br /><br />Different sources of information are always good and will always present facts differently and correctly. <br /><br />Cheers,<br /><br /><br />Nathan H.O.<br />Moderator<br />SQL-Server-Performance.com

Share This Page