Covering index
in [Programming SQL]
|
From: tshad on 10 May 2010 19:01 I am looking at a SP that has a select statement where the Query Analyser suggests a covering index: CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ORDER] ([EID]) INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) There already is one that is not covering index on the table for other SPs, CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ORDER] ([EID]) Am I losing anything by replacing the old index with this one (where this one may be the only one that uses the index)? Thanks, Tom
From: tshad on 10 May 2010 19:40 When I added the index and took out the old one, the cost went from 40% to 6%. But then I put the old index back and dropped the old index to do some tests and now it is using another index along with the old index with 4% on one and 5% on the other and a different suggestion comes up: CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ORDER] ([EID],[ModID],[Type]) INCLUDE ([OrderID]) In this one, some of the columns that were in the covering index are now part of the index (and the IsLinkable is gone). Also, should I be getting rid of the index where that only using EID and put one of the other indexes in or should I leave it in when I add the new indexes? Thanks, Tom "tshad" <t(a)dslextreme.com> wrote in message news:OzR44SJ8KHA.1436(a)TK2MSFTNGP06.phx.gbl... >I am looking at a SP that has a select statement where the Query Analyser >suggests a covering index: > > CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] > ON [dbo].[ORDER] ([EID]) > INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) > > There already is one that is not covering index on the table for other > SPs, > > CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] > ON [dbo].[ORDER] ([EID]) > > Am I losing anything by replacing the old index with this one (where this > one may be the only one that uses the index)? > > Thanks, > > Tom > >
From: Uri Dimant on 11 May 2010 03:48 INCLUDE adds nonkey columns to the leaf level pages. > CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] > ON [dbo].[ORDER] ([EID],[ModID],[Type]) > INCLUDE ([OrderID]) In the above index the order of key columns is important as it could affect an execution plan. Actually without seeing your data+SELECT statement +WHERE condition it is hard to suggest something useful "tshad" <t(a)dslextreme.com> wrote in message news:ODQ8joJ8KHA.4604(a)TK2MSFTNGP04.phx.gbl... > When I added the index and took out the old one, the cost went from 40% to > 6%. > > But then I put the old index back and dropped the old index to do some > tests and now it is using another index along with the old index with 4% > on one and 5% on the other and a different suggestion comes up: > > CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] > ON [dbo].[ORDER] ([EID],[ModID],[Type]) > INCLUDE ([OrderID]) > > In this one, some of the columns that were in the covering index are now > part of the index (and the IsLinkable is gone). > > Also, should I be getting rid of the index where that only using EID and > put one of the other indexes in or should I leave it in when I add the new > indexes? > > Thanks, > > Tom > > "tshad" <t(a)dslextreme.com> wrote in message > news:OzR44SJ8KHA.1436(a)TK2MSFTNGP06.phx.gbl... >>I am looking at a SP that has a select statement where the Query Analyser >>suggests a covering index: >> >> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >> ON [dbo].[ORDER] ([EID]) >> INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) >> >> There already is one that is not covering index on the table for other >> SPs, >> >> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >> ON [dbo].[ORDER] ([EID]) >> >> Am I losing anything by replacing the old index with this one (where this >> one may be the only one that uses the index)? >> >> Thanks, >> >> Tom >> >> > >
From: tshad on 11 May 2010 14:08 "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:OtlAw4N8KHA.5476(a)TK2MSFTNGP06.phx.gbl... > INCLUDE adds nonkey columns to the leaf level pages. > >> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >> ON [dbo].[ORDER] ([EID],[ModID],[Type]) >> INCLUDE ([OrderID]) > > In the above index the order of key columns is important as it could > affect an execution plan. Actually without seeing your data+SELECT > statement +WHERE condition it is hard to suggest something useful I am just curious as to why it suggested one plan which I implemented but then when took the new index away (leaving it exactly the same) it suggested a different index with a different include section. Also, if I implement this one, shouldn't I drop the old index which has only the EID (which would be covered by this new index where EID is first. Thanks, Tom > > > > "tshad" <t(a)dslextreme.com> wrote in message > news:ODQ8joJ8KHA.4604(a)TK2MSFTNGP04.phx.gbl... >> When I added the index and took out the old one, the cost went from 40% >> to 6%. >> >> But then I put the old index back and dropped the old index to do some >> tests and now it is using another index along with the old index with 4% >> on one and 5% on the other and a different suggestion comes up: >> >> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >> ON [dbo].[ORDER] ([EID],[ModID],[Type]) >> INCLUDE ([OrderID]) >> >> In this one, some of the columns that were in the covering index are now >> part of the index (and the IsLinkable is gone). >> >> Also, should I be getting rid of the index where that only using EID and >> put one of the other indexes in or should I leave it in when I add the >> new indexes? >> >> Thanks, >> >> Tom >> >> "tshad" <t(a)dslextreme.com> wrote in message >> news:OzR44SJ8KHA.1436(a)TK2MSFTNGP06.phx.gbl... >>>I am looking at a SP that has a select statement where the Query Analyser >>>suggests a covering index: >>> >>> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >>> ON [dbo].[ORDER] ([EID]) >>> INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) >>> >>> There already is one that is not covering index on the table for other >>> SPs, >>> >>> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >>> ON [dbo].[ORDER] ([EID]) >>> >>> Am I losing anything by replacing the old index with this one (where >>> this one may be the only one that uses the index)? >>> >>> Thanks, >>> >>> Tom >>> >>> >> >> > >
From: tshad on 11 May 2010 14:51
"tshad" <tfs(a)dslextreme.com> wrote in message news:OVMB0TT8KHA.3880(a)TK2MSFTNGP04.phx.gbl... > > "Uri Dimant" <urid(a)iscar.co.il> wrote in message > news:OtlAw4N8KHA.5476(a)TK2MSFTNGP06.phx.gbl... >> INCLUDE adds nonkey columns to the leaf level pages. >> >>> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >>> ON [dbo].[ORDER] ([EID],[ModID],[Type]) >>> INCLUDE ([OrderID]) >> >> In the above index the order of key columns is important as it could >> affect an execution plan. Actually without seeing your data+SELECT >> statement +WHERE condition it is hard to suggest something useful > > I am just curious as to why it suggested one plan which I implemented but > then when took the new index away (leaving it exactly the same) it > suggested a different index with a different include section. > > Also, if I implement this one, shouldn't I drop the old index which has > only the EID (which would be covered by this new index where EID is first. > Actually, now that I think about it, I may want to keep both indexes as the index with EID by itself would perform better for the procedures that only need the EID and not the other columns. This being that the index key would be smaller. Thanks, Tom > Thanks, > > Tom > >> >> >> >> "tshad" <t(a)dslextreme.com> wrote in message >> news:ODQ8joJ8KHA.4604(a)TK2MSFTNGP04.phx.gbl... >>> When I added the index and took out the old one, the cost went from 40% >>> to 6%. >>> >>> But then I put the old index back and dropped the old index to do some >>> tests and now it is using another index along with the old index with 4% >>> on one and 5% on the other and a different suggestion comes up: >>> >>> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >>> ON [dbo].[ORDER] ([EID],[ModID],[Type]) >>> INCLUDE ([OrderID]) >>> >>> In this one, some of the columns that were in the covering index are now >>> part of the index (and the IsLinkable is gone). >>> >>> Also, should I be getting rid of the index where that only using EID and >>> put one of the other indexes in or should I leave it in when I add the >>> new indexes? >>> >>> Thanks, >>> >>> Tom >>> >>> "tshad" <t(a)dslextreme.com> wrote in message >>> news:OzR44SJ8KHA.1436(a)TK2MSFTNGP06.phx.gbl... >>>>I am looking at a SP that has a select statement where the Query >>>>Analyser suggests a covering index: >>>> >>>> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >>>> ON [dbo].[ORDER] ([EID]) >>>> INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) >>>> >>>> There already is one that is not covering index on the table for other >>>> SPs, >>>> >>>> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >>>> ON [dbo].[ORDER] ([EID]) >>>> >>>> Am I losing anything by replacing the old index with this one (where >>>> this one may be the only one that uses the index)? >>>> >>>> Thanks, >>>> >>>> Tom >>>> >>>> >>> >>> >> >> > > |