Covering index
in [Programming SQL]
|
From: tshad on 11 May 2010 15:35 "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 > The actual procedure is very large and has a couple of views but the WHERE clause is essentially: FROM CustType O WHERE @Type % O.CType = 0 O.EID = @EID and ( O.ModuleID = COALESCE(@ModId, O.ModID) OR ( O.ModID is Null And @ModId is Null ) ) and ( O.IsLinkable = COALESCE(@LinkableState, O.IsLinkable) OR ( O.IsLinkable is Null And @LinkableState is Null ) ) The first time I ran this the plan showed a scan with operator cost of 40% and a suggested index of: CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ORDER] ([EID]) INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) (Notice that IsLinkable is in the INCLUDE section). When I add this index I get an index seek using this index with a cost of 6%. I added this index and then removed it (for testing). Now the plan shows a Hash Match of 2 index seeks of the index with only EID as the column and an index with only Type as the column. This is then combined in a Nested Loop operater with an index with only the ModID as the column. These operators have costs of 3%, 4% and 5%. Now the index is as above: CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ORDER] ([EID],[ModID],[Type]) INCLUDE ([OrderID]) Now 2 of the columns have been moved out of the INCLUDE section and moved to the COLUMN section and the IsLinkable is now gone??? When I add this, I also get one index seek (like the 1st index suggestion) and a cost of 4%. I would think it would be larger since it would have to go to the table to get the IsLinkable column. 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 15:46 "tshad" <tfs(a)dslextreme.com> wrote in message news:e62PnEU8KHA.5464(a)TK2MSFTNGP05.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 >> > > The actual procedure is very large and has a couple of views but the WHERE > clause is essentially: > > FROM CustType O > WHERE @Type % O.CType = 0 > O.EID = @EID > and ( > O.ModuleID = COALESCE(@ModId, O.ModID) > OR ( > O.ModID is Null > And @ModId is Null > ) > ) > and ( > O.IsLinkable = COALESCE(@LinkableState, O.IsLinkable) > OR ( > O.IsLinkable is Null > And @LinkableState is Null > ) > ) > > The first time I ran this the plan showed a scan with operator cost of 40% > and a suggested index of: > > CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] > ON [dbo].[ORDER] ([EID]) > INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) > > (Notice that IsLinkable is in the INCLUDE section). > > When I add this index I get an index seek using this index with a cost of > 6%. > > I added this index and then removed it (for testing). > > Now the plan shows a Hash Match of 2 index seeks of the index with only > EID as the column and an index with only Type as the column. This is then > combined in a Nested Loop operater with an index with only the ModID as > the column. These operators have costs of 3%, 4% and 5%. > > Now the index is as above: > > CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] > ON [dbo].[ORDER] ([EID],[ModID],[Type]) > INCLUDE ([OrderID]) > > Now 2 of the columns have been moved out of the INCLUDE section and moved > to the COLUMN section and the IsLinkable is now gone??? > > When I add this, I also get one index seek (like the 1st index suggestion) > and a cost of 4%. I would think it would be larger since it would have to > go to the table to get the IsLinkable column. > I just noticed something after this index is added. In the Index Seek operatore, the seek predicates are: EID, ModID and Type. Then it is filtered by some of the other WHERE predicates including "@LinkableState is Null" the before being joined with another table. But there doesn't seem to be a place where IsLinkable is mentioned???? 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 >>>> >>>> >>> >>> >> >> > >
From: tshad on 11 May 2010 16:05 I found out why it was giving me 2 indexes. There was a spot in the procedure (which is very large) where the same select was being done without the IsLinkable test. Whether one or another is done is based on whether a parameter is null or not. What is strange and why I didn't see it was that I am running the same statement (exec...) with exactly the same parameters. So I don't know why it gave me the 1st index in the first place. But now that I have seen both indexes and either statement could be run, the question is which one to use or should I use both??? If I use either one, I don't get any more suggestions from query plan. So it seems like either one would work. Thanks, Tom "tshad" <tfs(a)dslextreme.com> wrote in message news:et%23swKU8KHA.5848(a)TK2MSFTNGP06.phx.gbl... > "tshad" <tfs(a)dslextreme.com> wrote in message > news:e62PnEU8KHA.5464(a)TK2MSFTNGP05.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 >>> >> >> The actual procedure is very large and has a couple of views but the >> WHERE clause is essentially: >> >> FROM CustType O >> WHERE @Type % O.CType = 0 >> O.EID = @EID >> and ( >> O.ModuleID = COALESCE(@ModId, O.ModID) >> OR ( >> O.ModID is Null >> And @ModId is Null >> ) >> ) >> and ( >> O.IsLinkable = COALESCE(@LinkableState, O.IsLinkable) >> OR ( >> O.IsLinkable is Null >> And @LinkableState is Null >> ) >> ) >> >> The first time I ran this the plan showed a scan with operator cost of >> 40% and a suggested index of: >> >> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >> ON [dbo].[ORDER] ([EID]) >> INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) >> >> (Notice that IsLinkable is in the INCLUDE section). >> >> When I add this index I get an index seek using this index with a cost of >> 6%. >> >> I added this index and then removed it (for testing). >> >> Now the plan shows a Hash Match of 2 index seeks of the index with only >> EID as the column and an index with only Type as the column. This is >> then combined in a Nested Loop operater with an index with only the ModID >> as the column. These operators have costs of 3%, 4% and 5%. >> >> Now the index is as above: >> >> CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] >> ON [dbo].[ORDER] ([EID],[ModID],[Type]) >> INCLUDE ([OrderID]) >> >> Now 2 of the columns have been moved out of the INCLUDE section and moved >> to the COLUMN section and the IsLinkable is now gone??? >> >> When I add this, I also get one index seek (like the 1st index >> suggestion) and a cost of 4%. I would think it would be larger since it >> would have to go to the table to get the IsLinkable column. >> > > I just noticed something after this index is added. > > In the Index Seek operatore, the seek predicates are: EID, ModID and Type. > Then it is filtered by some of the other WHERE predicates including > "@LinkableState is Null" the before being joined with another table. But > there doesn't seem to be a place where IsLinkable is mentioned???? > > 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 >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
From: Gert-Jan Strik on 11 May 2010 16:14 tshad wrote: > The actual procedure is very large and has a couple of views but the WHERE > clause is essentially: > > FROM CustType O > WHERE @Type % O.CType = 0 > O.EID = @EID > and ( > O.ModuleID = COALESCE(@ModId, O.ModID) > OR ( > O.ModID is Null > And @ModId is Null > ) > ) > and ( > O.IsLinkable = COALESCE(@LinkableState, O.IsLinkable) > OR ( > O.IsLinkable is Null > And @LinkableState is Null > ) > ) Tom, This query does not lend itself for optimization. It is pointless to add big indexes. This is why... The optimizer has to create a query plan that is correct for all possible parameter values. SQL Server will compile the stored procedure upon first invocation. The actual parameter values that you use (the first time you call the SP) are used to determine which indexes to use. So if you use different parameter values, you might get different query plans. That is the reason that the tuning wizard can easily come up with different index recommendations if the parameters are not always the same. Without rewriting the query, it is pointless to try to optimize it generically. Basically, you can only optimize the query for one specific set of parameters. For example for a @ModId that is very selective and a @LinkableState that is NULL. For this query (the way it is written now), it is impossible to get a good query plan for two totally different the parameter sets, like (@ModId=NULL and @LinkableState=NULL) and (@ModId=selective and @LinkableState=selective), because there different parameter sets require a completely different query plan for optimal performance. The disclaimer here is that I assume that @EID is not selective (in other words, WHERE O.EID = @EID would return many rows). If @EID is in fact very selective, then it is pointless to index ModID or IsLinkable anyway. -- Gert-Jan
From: tshad on 11 May 2010 16:49 "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message news:4BE9BAAD.C7F68AB6(a)xs4all.nl... > tshad wrote: >> The actual procedure is very large and has a couple of views but the >> WHERE >> clause is essentially: >> >> FROM CustType O >> WHERE @Type % O.CType = 0 >> O.EID = @EID >> and ( >> O.ModuleID = COALESCE(@ModId, O.ModID) >> OR ( >> O.ModID is Null >> And @ModId is Null >> ) >> ) >> and ( >> O.IsLinkable = COALESCE(@LinkableState, O.IsLinkable) >> OR ( >> O.IsLinkable is Null >> And @LinkableState is Null >> ) >> ) > > Tom, > > This query does not lend itself for optimization. It is pointless to add > big indexes. This is why... > > The optimizer has to create a query plan that is correct for all > possible parameter values. SQL Server will compile the stored procedure > upon first invocation. The actual parameter values that you use (the > first time you call the SP) are used to determine which indexes to use. > So if you use different parameter values, you might get different query > plans. That is the reason that the tuning wizard can easily come up with > different index recommendations if the parameters are not always the > same. > > Without rewriting the query, it is pointless to try to optimize it > generically. Basically, you can only optimize the query for one specific > set of parameters. For example for a @ModId that is very selective and a > @LinkableState that is NULL. For this query (the way it is written now), > it is impossible to get a good query plan for two totally different the > parameter sets, like (@ModId=NULL and @LinkableState=NULL) and > (@ModId=selective and @LinkableState=selective), because there different > parameter sets require a completely different query plan for optimal > performance. > > The disclaimer here is that I assume that @EID is not selective (in > other words, WHERE O.EID = @EID would return many rows). If @EID is in > fact very selective, then it is pointless to index ModID or IsLinkable > anyway. Well, if that is correct, wouldn't the first index be a better index than only EID or, as you surmise and are probably correct, one with multple columns where some might be null. As I mention in my latest post, there are actually a couple of queries which are similar and which one is called is based on the parameters passed. As far as selectivity, if I do a select...where O.EID = @EID, I get anywhere from 1 to 4000 records (not selective, right). Thanks, Tom > > -- > Gert-Jan
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Connections and ##temp tables Next: FILESTREAMING SQL 2008 R2 |