Covering index
in [Programming SQL]
|
From: Gert-Jan Strik on 11 May 2010 17:26 tshad wrote: > > "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 Yes, the index with the included column will probably give the SELECT query better performance than the index on just EID (without included columns), especially if the index is covering. -- Gert-Jan
From: tshad on 11 May 2010 17:39 Doing some tests to see what would happen if I used the index with the query it was made for and found: If I use the 2nd index (without the IsLinkable in the cover) with the query that tests the IsLinkable column, I get the following from the plan: A) Nested Loop with 1) index seek with the index (3%) 2) Key Lookup on primary key (CustID) to get the IsLinkable value. I assume it uses the CustID from the index to get the record from the table. If I use the 1st index (with the IsLinkable in the cover) with the query that doesn't test the IsLinkable column, I get the following from the plan: B) Nested Loop with 1) index seek with the index (2%) 2) Clustered index on another table. If I use the 2nd index but add IsLinkable into the cover so that I now have OrderID and IsLinkable in the Cover: Used with query that contains test of IsLinkable (same as A above) C) Nested Loop with 1) Index Seek with the index (4%) 2) Clustered index on another table Used with query that doesn't contain the test for IsLinkable D) Nested Loop with 1) Index Seek with the index (3%) 2) Clustered index on another table. It looks like (B) is the best index: CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ORDER] ([EID]) INCLUDE ([OrderID],[ModID],[Type],[IsLinkable]) Thanks, Tom "tshad" <tfs(a)dslextreme.com> wrote in message news:enZu9tU8KHA.5808(a)TK2MSFTNGP02.phx.gbl... > > "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 > >
From: tshad on 11 May 2010 18:04 "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message news:4BE9CB83.C70E9087(a)xs4all.nl... > tshad wrote: >> >> "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 > > Yes, the index with the included column will probably give the SELECT > query better performance than the index on just EID (without included > columns), especially if the index is covering. > Based on the results in my last post, the question is whether to use the 1st query (the one with the IsLinkable in the cover along with all the other columns except EID) only, which worked well for each query or to use both indexes and let the analyzer pick the correct one. Of course, that would force the analyzer to recompile each time, wouldn't it? If there was only one index, would that prevent the recompile? Thanks, Tom > -- > Gert-Jan
From: Gert-Jan Strik on 12 May 2010 16:33
> Based on the results in my last post, the question is whether to use the 1st > query (the one with the IsLinkable in the cover along with all the other > columns except EID) only, which worked well for each query or to use both > indexes and let the analyzer pick the correct one. If you create an index on EID that includes some extra columns, then it is pretty pointless the keep the existing index on EID. The only exception to that is if you have queries that select many rows but still use the index. In most situations the benefit of an index that is only smaller at the leaf level does not weigh up to the cost for the storage engine to maintain this "extra" index. > Of course, that would force the analyzer to recompile each time, wouldn't > it? No, it wouldn't. It would simply pick the best index for the query, and keep that query plan for all calls to the SP. > If there was only one index, would that prevent the recompile? No, the number of indexes does not affect recompile behavior. So after adding (or dropping) an index, you will get a recompile, but then that query plan will be used for all subsequent calls (until there is another event that invalidates the query plan, or drops it from memory because of memory pressure). -- Gert-Jan |