Linked Servers & index access
in [Server Configuration]
|
Prev: SQL-2K and TCPIP 6
Next: Taking database offline
From: Jay on 14 Dec 2009 17:38 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 14 Dec 2009 19:28 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 14 Dec 2009 20:03 It was in the instruction material on SkillSoft and I don't have a second server available to me to check anything. However, they were quite specific about 2005 needing that to be set. Can I assume that you think it's as dumb as I do? "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 14 Dec 2009 21:14 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: Andrew J. Kelly on 15 Dec 2009 08:48
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. >>> >> >> > > |