Professional Web Applications Themes

Profiler Question (Scans) - Microsoft SQL / MS SQL Server

I have a couple questions regarding understanding Scan profiler output. 1) Sometimes the IndexID data column will contain a value that does not correspond to any index on the associated table. Why is this? 2) How can I tell when a table scan is being done? Thanks very much....

  1. #1

    Default Profiler Question (Scans)

    I have a couple questions regarding understanding Scan profiler output.

    1) Sometimes the IndexID data column will contain a value that does not
    correspond to any index on the associated table. Why is this?

    2) How can I tell when a table scan is being done?

    Thanks very much.


    Amos Soma Guest

  2. #2

    Default Re: Profiler Question (Scans)

    Is the value that doesn't correspond to any indexes 0 because 0 indicates a
    table scan.

    Maybe both questions are answered? Let us know!

    hth,
    kt

    --
    Please reply only on the newsgroups! Include dml/ddl, when possible.Thanks
    Kimberly L. Tripp
    President, SYSolutions, Inc. [url]www.SQLSkills.com[/url]
    Principal Mentor, Solid Quality Learning [url]www.SolidQualityLearning.com[/url]


    "Amos Soma" <nospam> wrote in message
    news:%23UHcjlkRDHA.1624tk2msftngp13.phx.gbl...
    > I have a couple questions regarding understanding Scan profiler output.
    >
    > 1) Sometimes the IndexID data column will contain a value that does not
    > correspond to any index on the associated table. Why is this?
    >
    > 2) How can I tell when a table scan is being done?
    >
    > Thanks very much.
    >
    >

    Kimberly L. Tripp Guest

  3. #3

    Default Re: Profiler Question (Scans)

    Kim,

    Oddly enough, the value is not 0. In one case, the value is 4 but the only
    index I have on the table is a primary key! In another case, the value is
    13 but I only have maybe 5 keys on that table. Very strange!

    Amos

    "Kimberly L. Tripp"
    <Kimberly_PleaseReplyOnTheNewsGroupsnospam.sqlski lls.com> wrote in message
    news:ehQcM6kRDHA.2188TK2MSFTNGP10.phx.gbl...
    > Is the value that doesn't correspond to any indexes 0 because 0 indicates
    a
    > table scan.
    >
    > Maybe both questions are answered? Let us know!
    >
    > hth,
    > kt
    >
    > --
    > Please reply only on the newsgroups! Include dml/ddl, when possible.Thanks
    > Kimberly L. Tripp
    > President, SYSolutions, Inc. [url]www.SQLSkills.com[/url]
    > Principal Mentor, Solid Quality Learning [url]www.SolidQualityLearning.com[/url]
    >
    >
    > "Amos Soma" <nospam> wrote in message
    > news:%23UHcjlkRDHA.1624tk2msftngp13.phx.gbl...
    > > I have a couple questions regarding understanding Scan profiler output.
    > >
    > > 1) Sometimes the IndexID data column will contain a value that does not
    > > correspond to any index on the associated table. Why is this?
    > >
    > > 2) How can I tell when a table scan is being done?
    > >
    > > Thanks very much.
    > >
    > >
    >
    >

    Amos Soma Guest

  4. #4

    Default Re: Profiler Question (Scans)

    Hi,

    I found the following in SQL Server 2000 Books Online:
    By monitoring the Index ID default data column, you can determine the
    identification number of the index being used by a specific query. The
    Index ID data column contains either:

    The value 1 when the clustered index of the table is being scanned.
    -or-

    The value greater than 2 and less than 255 when a non-clustered index of
    the table is being scanned.

    You may post the simple reproduction of the problem so that I can check on
    my side.


    Bill Cheng
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    --------------------
    | From: "Amos Soma" <com>
    | References: <#phx.gbl>
    <phx.gbl>
    | Subject: Re: Profiler Question (Scans)
    | Date: Wed, 9 Jul 2003 15:44:06 -0400
    | Lines: 43
    | X-Priority: 3
    | X-MSMail-Priority: Normal
    | X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
    | Message-ID: <#phx.gbl>
    | Newsgroups: microsoft.public.sqlserver.programming
    | NNTP-Posting-Host: 209.11.226.82
    | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP11.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:372347
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | Kim,
    |
    | Oddly enough, the value is not 0. In one case, the value is 4 but the
    only
    | index I have on the table is a primary key! In another case, the value is
    | 13 but I only have maybe 5 keys on that table. Very strange!
    |
    | Amos
    |
    | "Kimberly L. Tripp"
    | <sqlskills.com> wrote in
    message
    | news:phx.gbl...
    | > Is the value that doesn't correspond to any indexes 0 because 0
    indicates
    | a
    | > table scan.
    | >
    | > Maybe both questions are answered? Let us know!
    | >
    | > hth,
    | > kt
    | >
    | > --
    | > Please reply only on the newsgroups! Include dml/ddl, when
    possible.Thanks
    | > Kimberly L. Tripp
    | > President, SYSolutions, Inc. www.SQLSkills.com
    | > Principal Mentor, Solid Quality Learning www.SolidQualityLearning.com
    | >
    | >
    | > "Amos Soma" <com> wrote in message
    | > news:%phx.gbl...
    | > > I have a couple questions regarding understanding Scan profiler
    output.
    | > >
    | > > 1) Sometimes the IndexID data column will contain a value that does
    not
    | > > correspond to any index on the associated table. Why is this?
    | > >
    | > > 2) How can I tell when a table scan is being done?
    | > >
    | > > Thanks very much.
    | > >
    | > >
    | >
    | >
    |
    |
    |

    Bill Guest

Similar Threads

  1. Profiler Question
    By igroeg in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 18th, 10:06 AM
  2. Profiler does not work
    By David.Wichert in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: October 7th, 11:17 AM
  3. Profiler Question (Stored Procedures)
    By Amos Soma in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 14th, 12:56 PM
  4. Profiler Question (Profiler Message)
    By Brian Moran in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 3rd, 05:00 PM
  5. Profiler Question (Setting Filters)
    By Amos Soma in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 3rd, 04:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139