Linked Servers & index access
in [Server Configuration]
|
Prev: SQL-2K and TCPIP 6
Next: Taking database offline
From: Jay on 15 Dec 2009 11:40 Links read, only now I'm even more confused. "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:%23wE%23x0YfKHA.2184(a)TK2MSFTNGP04.phx.gbl... > You might want to have a look at these: > > http://msdn.microsoft.com/en-us/library/aa172718(SQL.80).aspx > > http://groups.google.com/group/microsoft.public.data.oledb/browse_frm/thread/6ceeafc2faa35d9/257021f6aebd9e23?tvc=1&q=group%3Amicrosoft.public.data.oledb+author%3AErland+author%3ASommarskog&hl=sv#257021f6aebd9e23 > > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "Jay" <spam(a)nospam.org> wrote in message > news:ex3LSxSfKHA.4636(a)TK2MSFTNGP04.phx.gbl... >> OK, I found it in my 2008. >> >> In MS, expand Server Objects/Linked Servers/Providers. Right click on any >> provider and select properties. >> >> Among the options is: "Index as Access Path", which is defined as: >> >> "Index as access path >> If nonzero, SQL Server attempts to use indexes of the provider to fetch >> data. By default, indexes are used only for metadata and are never >> opened" >> >> ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_5techref/html/a2e7ba18-1a38-433c-aa63-8f75909e09dc.htm >> >> This doesn't make sense. >> >> "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message >> news:MZSdnWwuyuvySrvWnZ2dnUVZ_o-dnZ2d(a)earthlink.com... >>> Can you please explain exactly what it is that makes you believe you >>> need to configure something to allow remote users to use indexes?? >>> >>> -- >>> Kevin G. Boles >>> Indicium Resources, Inc. >>> SQL Server MVP >>> kgboles a earthlink dt net >>> >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:eWC6o4QfKHA.4952(a)TK2MSFTNGP06.phx.gbl... >>>> I'm looking at the linked server configuration and am stuck on a why >>>> question. >>>> >>>> When configuring, if you want a remote user to be able to use the >>>> indexes on the server being configured, you must explicitly state so. >>>> The default is that remote users will not be able to use the indexes. >>>> >>>> Why on earth would you not want ANY user, let alone a remote user, to >>>> be able to use the indexes? After all, the indexes were put there to be >>>> used and improve performance. >>>> >>>> If anything, I would expect it to default to yes. >>>> >>> >>> >> >>
From: Jay on 15 Dec 2009 12:33 OK, I MAY be starting to get this (and I do mean maybe). The "Index As Access Path" option is in OLE DB Providers for access to non-SQL Server database systems (where MS doesn't know their capabilities). If you're linking a SQL Server database, this issue simply doesn't even come up. Yes? No? "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:%23wE%23x0YfKHA.2184(a)TK2MSFTNGP04.phx.gbl... > You might want to have a look at these: > > http://msdn.microsoft.com/en-us/library/aa172718(SQL.80).aspx > > http://groups.google.com/group/microsoft.public.data.oledb/browse_frm/thread/6ceeafc2faa35d9/257021f6aebd9e23?tvc=1&q=group%3Amicrosoft.public.data.oledb+author%3AErland+author%3ASommarskog&hl=sv#257021f6aebd9e23 > > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "Jay" <spam(a)nospam.org> wrote in message > news:ex3LSxSfKHA.4636(a)TK2MSFTNGP04.phx.gbl... >> OK, I found it in my 2008. >> >> In MS, expand Server Objects/Linked Servers/Providers. Right click on any >> provider and select properties. >> >> Among the options is: "Index as Access Path", which is defined as: >> >> "Index as access path >> If nonzero, SQL Server attempts to use indexes of the provider to fetch >> data. By default, indexes are used only for metadata and are never >> opened" >> >> ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_5techref/html/a2e7ba18-1a38-433c-aa63-8f75909e09dc.htm >> >> This doesn't make sense. >> >> "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message >> news:MZSdnWwuyuvySrvWnZ2dnUVZ_o-dnZ2d(a)earthlink.com... >>> Can you please explain exactly what it is that makes you believe you >>> need to configure something to allow remote users to use indexes?? >>> >>> -- >>> Kevin G. Boles >>> Indicium Resources, Inc. >>> SQL Server MVP >>> kgboles a earthlink dt net >>> >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:eWC6o4QfKHA.4952(a)TK2MSFTNGP06.phx.gbl... >>>> I'm looking at the linked server configuration and am stuck on a why >>>> question. >>>> >>>> When configuring, if you want a remote user to be able to use the >>>> indexes on the server being configured, you must explicitly state so. >>>> The default is that remote users will not be able to use the indexes. >>>> >>>> Why on earth would you not want ANY user, let alone a remote user, to >>>> be able to use the indexes? After all, the indexes were put there to be >>>> used and improve performance. >>>> >>>> If anything, I would expect it to default to yes. >>>> >>> >>> >> >>
From: TheSQLGuru on 15 Dec 2009 13:12 Isn't this pretty clear: "Using the OLE DB provider's indexes has performance benefits only when the index and table rowsets are on the same computer as the instance of Microsoft� SQL Server�. Thus, the Index AS Access Path option should be set only if the data source is on the same computer as SQL Server." No idea WHY performance is beneficial only when data source is on same computer as SQL Server, but there you have it. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Jay" <spam(a)nospam.org> wrote in message news:eSw8RVafKHA.4952(a)TK2MSFTNGP06.phx.gbl... > Links read, only now I'm even more confused. > > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:%23wE%23x0YfKHA.2184(a)TK2MSFTNGP04.phx.gbl... >> You might want to have a look at these: >> >> http://msdn.microsoft.com/en-us/library/aa172718(SQL.80).aspx >> >> http://groups.google.com/group/microsoft.public.data.oledb/browse_frm/thread/6ceeafc2faa35d9/257021f6aebd9e23?tvc=1&q=group%3Amicrosoft.public.data.oledb+author%3AErland+author%3ASommarskog&hl=sv#257021f6aebd9e23 >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "Jay" <spam(a)nospam.org> wrote in message >> news:ex3LSxSfKHA.4636(a)TK2MSFTNGP04.phx.gbl... >>> OK, I found it in my 2008. >>> >>> In MS, expand Server Objects/Linked Servers/Providers. Right click on >>> any provider and select properties. >>> >>> Among the options is: "Index as Access Path", which is defined as: >>> >>> "Index as access path >>> If nonzero, SQL Server attempts to use indexes of the provider to fetch >>> data. By default, indexes are used only for metadata and are never >>> opened" >>> >>> ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_5techref/html/a2e7ba18-1a38-433c-aa63-8f75909e09dc.htm >>> >>> This doesn't make sense. >>> >>> "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message >>> news:MZSdnWwuyuvySrvWnZ2dnUVZ_o-dnZ2d(a)earthlink.com... >>>> Can you please explain exactly what it is that makes you believe you >>>> need to configure something to allow remote users to use indexes?? >>>> >>>> -- >>>> Kevin G. Boles >>>> Indicium Resources, Inc. >>>> SQL Server MVP >>>> kgboles a earthlink dt net >>>> >>>> >>>> "Jay" <spam(a)nospam.org> wrote in message >>>> news:eWC6o4QfKHA.4952(a)TK2MSFTNGP06.phx.gbl... >>>>> I'm looking at the linked server configuration and am stuck on a why >>>>> question. >>>>> >>>>> When configuring, if you want a remote user to be able to use the >>>>> indexes on the server being configured, you must explicitly state so. >>>>> The default is that remote users will not be able to use the indexes. >>>>> >>>>> Why on earth would you not want ANY user, let alone a remote user, to >>>>> be able to use the indexes? After all, the indexes were put there to >>>>> be used and improve performance. >>>>> >>>>> If anything, I would expect it to default to yes. >>>>> >>>> >>>> >>> >>> > >
From: Jay on 15 Dec 2009 13:29 I spent some time reading that and I don't think it's clear at all. I'm on Server A (which must be SQL Server) setting up a linked server to Server B (which could be Oracle, ODBC, Access, or SQL Server). If Server B is SQL Server, or Access, then there is no provider. The "Index As Access Path" is a configuration option for a provider. So, a comment discussing a provider option for SQL Server is not clear. "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:PcudnflW8IFtTbrWnZ2dnUVZ_rydnZ2d(a)earthlink.com... > Isn't this pretty clear: > > "Using the OLE DB provider's indexes has performance benefits only when > the index and table rowsets are on the same computer as the instance of > Microsoft� SQL Server�. Thus, the Index AS Access Path option should be > set only if the data source is on the same computer as SQL Server." > > No idea WHY performance is beneficial only when data source is on same > computer as SQL Server, but there you have it. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "Jay" <spam(a)nospam.org> wrote in message > news:eSw8RVafKHA.4952(a)TK2MSFTNGP06.phx.gbl... >> Links read, only now I'm even more confused. >> >> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >> news:%23wE%23x0YfKHA.2184(a)TK2MSFTNGP04.phx.gbl... >>> You might want to have a look at these: >>> >>> http://msdn.microsoft.com/en-us/library/aa172718(SQL.80).aspx >>> >>> http://groups.google.com/group/microsoft.public.data.oledb/browse_frm/thread/6ceeafc2faa35d9/257021f6aebd9e23?tvc=1&q=group%3Amicrosoft.public.data.oledb+author%3AErland+author%3ASommarskog&hl=sv#257021f6aebd9e23 >>> >>> >>> -- >>> >>> Andrew J. Kelly SQL MVP >>> Solid Quality Mentors >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:ex3LSxSfKHA.4636(a)TK2MSFTNGP04.phx.gbl... >>>> OK, I found it in my 2008. >>>> >>>> In MS, expand Server Objects/Linked Servers/Providers. Right click on >>>> any provider and select properties. >>>> >>>> Among the options is: "Index as Access Path", which is defined as: >>>> >>>> "Index as access path >>>> If nonzero, SQL Server attempts to use indexes of the provider to fetch >>>> data. By default, indexes are used only for metadata and are never >>>> opened" >>>> >>>> ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_5techref/html/a2e7ba18-1a38-433c-aa63-8f75909e09dc.htm >>>> >>>> This doesn't make sense. >>>> >>>> "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message >>>> news:MZSdnWwuyuvySrvWnZ2dnUVZ_o-dnZ2d(a)earthlink.com... >>>>> Can you please explain exactly what it is that makes you believe you >>>>> need to configure something to allow remote users to use indexes?? >>>>> >>>>> -- >>>>> Kevin G. Boles >>>>> Indicium Resources, Inc. >>>>> SQL Server MVP >>>>> kgboles a earthlink dt net >>>>> >>>>> >>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>> news:eWC6o4QfKHA.4952(a)TK2MSFTNGP06.phx.gbl... >>>>>> I'm looking at the linked server configuration and am stuck on a why >>>>>> question. >>>>>> >>>>>> When configuring, if you want a remote user to be able to use the >>>>>> indexes on the server being configured, you must explicitly state so. >>>>>> The default is that remote users will not be able to use the indexes. >>>>>> >>>>>> Why on earth would you not want ANY user, let alone a remote user, to >>>>>> be able to use the indexes? After all, the indexes were put there to >>>>>> be used and improve performance. >>>>>> >>>>>> If anything, I would expect it to default to yes. >>>>>> >>>>> >>>>> >>>> >>>> >> >> > >
From: Erland Sommarskog on 16 Dec 2009 06:37
Jay (spam(a)nospam.org) writes: > OK, I MAY be starting to get this (and I do mean maybe). > > The "Index As Access Path" option is in OLE DB Providers for access to > non-SQL Server database systems (where MS doesn't know their > capabilities). > > If you're linking a SQL Server database, this issue simply doesn't even > come up. > > Yes? No? Probably. Note that this is a *provider* option, not a server option. If you set this option, it applies to all linked servers that uses this provider. I can't say that I fully understood the explanations from Books Online, and I have worked some with OLE DB programming. A good tip is that if you don't understand the meaning of option, you should not fiddle with it. :-) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |