Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Unanswered: linked server not using remote index

    Problem ms sql server 2012 not using remote index on sql server 2000 linked server with odbc

    Let say my servers are:
    srvdb and remotesrv

    when do select on srvdb:

    select * from remotesrv.database.dbo.inventory this select not using index on remoteserver

    but if this select is on remoteserver like

    select * from database.dbo.inventory, this select use index PK_Inventory (clustered)

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    With no where clause, I would not expect it to use an index whether it was a linked server, or a local table. Are you sure this is an index seek, and not an index scan (table scan in the case of a clustered index).

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Select *" will use a PK index if it is the clustered index, because the clustered index is the order in which the data is stored. But otherwise, "select *" with no join is not going to use any index because it scans the entire table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Force it to use the index.

  5. #5
    Join Date
    Jul 2013
    Posts
    2
    my select is:
    SELECT PocketPC, Quantity, InvDateTime, InvType FROM ssbmobile.SSBMobileMaster.dbo.Inventory
    WHERE db=1 AND ItemID= '000998' AND InvType= '2'
    AND ((PocketPC='-P1-' AND InvType = 2) OR (InvType = 1))

    and how to force to use index:
    i use
    WITH (INDEX(PK_Inventory))
    but nothing

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What are the indexes on ssbmobile.SSBMobileMaster.dbo.Inventory?
    Post the DDL please.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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