InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 13:22:05 GMT20http://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxNice series Gail.Mon, 23 Jul 2012 18:14:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxCalypso Bay Jacobs Well Land Estate Reviews - Roche Group - Luxury Real Estate located between Brisbane and the Gold Coast in Queensland, Australia.[url=http://www.calypsobayreview.com.au/]calypso bay[/url][url=http://www.calypsobayreview.com/]calypso bay[/url]Mon, 23 Jul 2012 05:35:33 GMTSolimaredad24http://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxHard to say without detailed analysis.Fri, 20 Jul 2012 10:51:45 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]GilaMonster (7/20/2012)[/b][hr]Don't do that. Waste of time. It is highly, exceedingly unlikely that it's faster to create all the indexes then run the query then drop the indexes than to just run the query. Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.Do your analysis, if you need the indexes, create them permanently. If you don't, don't create them.The only place you typically create and drop indexes frequently is data warehouses. Drop/disable indexes before the data load, recreate/rebuild afterwards.[/quote]Thanks for this!One of my last tasks was to make a SP execute faster. I run the Estimated Execution Plan of SSMS and it suggested creating 3 new indexes. The tables were quite big (about 270 million records). The SP was lasting 'infinite' and after creation of indexes it finished for an 1.5h. But additionally I replaced the passing arguments of the nested functions and SPs (called from the main SP) with local variables and then use them through the code, so my doubt here is whether i reached the goal with the new indexes or with the replacement of the passing arguments of all the nested SPs and FNs. I read somewhere in BOL for the passing arguments to be replaced with local variables when nesting.I haven't dropped the indexes yet. On one table they increased up to 8 indexes. Because the server is for replications, i decided to do that.What is your suggestion in this situation? Is it smart to tell the admins to disable those indexes when loading new data? if they never disable them. I'm not sure whether they disable them whenever they load data. The admins are not on my local site, that is why i haven't asked them yet, but I would.Thanks in advanceIgorMiFri, 20 Jul 2012 10:26:40 GMTIgorMihttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxDon't do that. Waste of time. It is highly, exceedingly unlikely that it's faster to create all the indexes then run the query then drop the indexes than to just run the query. Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.Do your analysis, if you need the indexes, create them permanently. If you don't, don't create them.The only place you typically create and drop indexes frequently is data warehouses. Drop/disable indexes before the data load, recreate/rebuild afterwards.Fri, 20 Jul 2012 09:39:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]GilaMonster (7/20/2012)[/b][hr]I don't understand what you're asking.[/quote]My question is the following:there is an existing table with lets say 5 indexes(1 clustered + 4 nonclustered).The Estimated Execution Plan of SSMS, for a particular stored procedure or query, suggests to add 3 more nonclustered indexes (for the same table), and I add them just for my SP to execute faster.After that I don't need the last 3 indexes. The questions is: Is it enough just to drop those 3 indexes, or i have to drop them and additionally rebuild the old (the 5 indexes) indexes?RegardsIgorMIFri, 20 Jul 2012 09:33:44 GMTIgorMihttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]IgorMi (7/20/2012)[/b][hr]Hi GailI want to thank you for your effort on indexes.I'll use this opportunity to ask a question.Example: There is a Table with one clustered index and some non-clustered. It is connected to more other tables.Then I create non-clustered index(es) on the table, in order to execute some queries faster, and then i want to drop the non-clustered index(es), because some other actions to that table may change.Is it a reasonable practice?, won't it change something? Do i need to rebuild the previously existed indexes?, or it is not needed.ThanksIgorMi[/quote]I think you want to know that whether deleting few indexes on a table can make other indexes (on the same table) fragmented?If this was your question, i think the answer is NO. Deleting any non-clustered index on a table doesn't increase the fragmentation of other indexes.Fri, 20 Jul 2012 09:09:38 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxI don't understand what you're asking.Fri, 20 Jul 2012 08:56:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxHi GailI want to thank you for your effort on indexes.I'll use this opportunity to ask a question.Example: There is a Table with one clustered index and some non-clustered. It is connected to more other tables.Then I create non-clustered index(es) on the table, in order to execute some queries faster, and then i want to drop the non-clustered index(es), because some other actions to that table may change.Is it a reasonable practice?, won't it change something? Do i need to rebuild the previously existed indexes?, or it is not needed.ThanksIgorMiFri, 20 Jul 2012 08:24:03 GMTIgorMihttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]deroby (7/6/2012)[/b][hr]However... suppose we have a large table (wide & long) that gets INSERTs and UPDATEs on non-indexed fields only. Would it make sense to 'force' it as a heap and use non-clustered indexes only because I (naively?) would expect that the Bookmark lookup (**) would be A LOT faster using the RID than using the clustered-index key(s); the latter requiring zigzagging through the entire (clustered) b-tree again ... ??[/quote]Most likely no. SQL is optimised for tables having clustered indexes. While a RID lookup will read less pages than a key lookup, the lookup is still a horridly slow operation that you want to avoid most of the time.[quote]I agree that if queries attack the table using the clustered index keys directly the lack of a bookmark lookup makes them 'winners'. But in our case we have at least 2 other totally different approaches in accessing the same table and then those need to go via a rather expensive bookmark lookup now... making it a heap would make every approach more or less equally good/bad... [/quote]Why does two approaches automatically mean lookups? Even if it did, making the table a heap would probably make both equally bad, which is probably not the optimisation strategy I would favour.[quote]The only reason NOT to work like this would be that whenever a table-change affects a record to move from one page to another this would affect all the leaf-nodes of every index ...[/quote]When rows are moved in a heap (typically when they are updated and grow in size) they leave forwarding pointers behind, the nonclustered indexes aren't updated because that would be expensive.Forwarding pointers make reads of the heap more expensive than it would be othewise.Fri, 06 Jul 2012 08:38:32 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]deroby (7/6/2012)[/b][hr]Hi Gail, another thanks for the series!We've always used the 'rule' that every table needs a clustered index and I've never really challenged that. If the PK is lean enough, we use that, otherwise we put an IDENTITY(int) column to the table and take that... a simple no-brainer in most cases.However... suppose we have a large table (wide & long) that gets INSERTs and UPDATEs on non-indexed fields only. Would it make sense to 'force' it as a heap and use non-clustered indexes only because I (naively?) would expect that the Bookmark lookup (**) would be A LOT faster using the RID than using the clustered-index key(s); the latter requiring zigzagging through the entire (clustered) b-tree again ... ??[/quote]Well, if your UPDATEs sometimes make, say, VAR* columns larger, then you're likely to see table fragmentation, which is best fixed by normal index maintenance on the clustered index.I would say that the ability to do index maintenance is another very solid argument for "everything gets a clustered index".Fri, 06 Jul 2012 08:10:48 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxHi Gail, another thanks for the series!We've always used the 'rule' that every table needs a clustered index and I've never really challenged that. If the PK is lean enough, we use that, otherwise we put an IDENTITY(int) column to the table and take that... a simple no-brainer in most cases.However... suppose we have a large table (wide & long) that gets INSERTs and UPDATEs on non-indexed fields only. Would it make sense to 'force' it as a heap and use non-clustered indexes only because I (naively?) would expect that the Bookmark lookup (**) would be A LOT faster using the RID than using the clustered-index key(s); the latter requiring zigzagging through the entire (clustered) b-tree again ... ??I agree that if queries attack the table using the clustered index keys directly the lack of a bookmark lookup makes them 'winners'. But in our case we have at least 2 other totally different approaches in accessing the same table and then those need to go via a rather expensive bookmark lookup now... making it a heap would make every approach more or less equally good/bad... The only reason NOT to work like this would be that whenever a table-change affects a record to move from one page to another this would affect all the leaf-nodes of every index and it would turn out expensive to update all relevant leaf-nodes to the new RID. Then again, since we only add to the heap, such a situation would pretty much never happen and I see no really reason to (frequently) rebuilding the heap either... I might be missing something fundamental here but plan on giving it some testing the next days anyway... fascinating stuff =PRobyPS: another issue might be that I've noticed in the past that INSERTS on a heap seem to be slower than those on e table with the clustered index on an Identity() table... this might not be relevant any more on recent versions of the product. I never really understood why this was the case anyway...(**: which as I understand it is the part between 'finding the RID/clustered-index field(s) from the non-clustered index' and the actual fetch of the (non-indexed) fields from the table)Fri, 06 Jul 2012 07:55:23 GMTderobyhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]GilaMonster (12/8/2011)[/b][hr]Same as the index key values are. Part of the row in the leaf pages. That's the definition of an include column - one that's in the index leaf pages only, not the intermediate and root levels.[/quote]Thanks a lot Gail that you spared some time to answer my question. I got it now.[quote][b]Divine Flame[/b][hr]Included Column value ----> Pointer to actual data row??[quote][b]GilaMonster (12/8/2011)[/b][hr]Errr... The pointer to the actual data row is either the clustered index key or the RID if there's no clustered index on the table.[/quote][/quote]I understand this one correctly, but I think I wrote it here in a wrong way. I just wanted to stimulate the structure of an index like we have on the back of the books :-). I didn't mean to say that Included column are the pointers to the actual row :-D.Your article was written so well that after reading them, I was always clear that pointers are either RID or clustered index key :-).Thu, 08 Dec 2011 21:57:15 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxAs with most everyone else, just wanted to say thanks for all the wonderfulness. Also, you are my hero and when [if] I grow up I want to be just like you. :-DThu, 08 Dec 2011 15:31:00 GMTPossinatorhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]Divine Flame (11/29/2011)[/b][hr]Here from the above statement I wanted to know that how this included columns data is stored in the leaf level. [/quote]Same as the index key values are. Part of the row in the leaf pages. That's the definition of an include column - one that's in the index leaf pages only, not the intermediate and root levels.[quote]Included Column value ----> Pointer to actual data row??[/quote]Errr... The pointer to the actual data row is either the clustered index key or the RID if there's no clustered index on the table.Thu, 08 Dec 2011 13:18:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxHi Gail,It was really a nice series to learn about the indexes. However, I have one confusion here, so you may guide me :hehe:[quote] Unlike the clustered index nonclustered indexes does not contain the entire data row at the leaf level. Rather, the nonclustered index contains just the columns defined in the index, and a pointer to the actual data row.[/quote]Yes, understood.[quote]Columns specified as include columns are stored at the leaf level of the nonclustered index, but not at the intermediate or root levels.[/quote]Here from the above statement I wanted to know that how this included columns data is stored in the leaf level. Is it also stored as the index keys are stored ? i.e.Included Column value ----> Pointer to actual data row??Tue, 29 Nov 2011 05:18:45 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxAll very good articles, thank you.Sometimes you *think* you know something but it turns out you were only half right. No harm in keeping the ole grey matter in check by re-learning stuff you *think* you knew. :unsure:Knowledge is power...as a person once said. :-P[i][b]qh[/b][/i]Tue, 12 Apr 2011 11:31:04 GMTquackhandle1975http://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxGail - nice set or articles.Mon, 07 Feb 2011 08:42:45 GMTpaul s-306273http://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxI've been doing SQL dev for a long time, but decided to approach this series like I was just starting out and knew jack about indexes. Yes, I learned a thing or two! OK, maybe a thing or 5. Thanks Gail - great series.Fri, 28 Jan 2011 06:15:55 GMTVictor Kirkpatrickhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]Toby White (1/25/2011)[/b][hr]Great artical Gail! Your commentary about intersections and the factors around whether to place the most selective column in the leading edge of the index are very practical and helpful. [/quote]Thanks. There are posts that go into more detail on those and some other indexing topics on my blog: [url]http://sqlinthewild.co.za/[/url] (specifically in the indexing catagoryTue, 25 Jan 2011 12:27:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxGreat artical Gail! Your commentary about intersections and the factors around whether to place the most selective column in the leading edge of the index are very practical and helpful. Tue, 25 Jan 2011 12:19:05 GMTToby Whitehttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]rik gretzinger (1/21/2011)[/b][hr]I'm sorry but may be it is because I am 'old school'. I have been working with SQL Server for over 15 years. It used to be true that creating a clustered index on a key that would cause the table to increment always at the end ( i.e datetime, ID column) was bad if the table was very active (inserts). It would cause pages at the end of the table to be hot and contention would follow. When I did P & T gigs, we would pick a clustered index that would disperse this traffic and cause multiple entry points in the table. Yes this is more expensive but it would reduce locking issues.[/quote]The last time that was true was in SQL 6.5. With the introduction of row-level locking in SQL 7, the hot-spot problem went away. [quote]I was also told by an 'expert' that since a non-clustered index contains the clustered key that there was no reason to repeat this value in the non-clustered index. [/quote]Nonclustered indexes do contain the clustered index key. As a key column if the nonclustered is not unique, as an include column if it is unique.This is NOT a good reason to leave the clustered key out of a nonclustered index if it is needed. SQL is not stupid enough to put the columns into the index twice if you explicitly add the clustering key and there's always the chance that another column will be added to the index (in which case the order of the implicitly included columns changes) and there's always the chance that the clustered index will change.[quote]If you do the following query:select customer_no, order_no, order_date from a where order_date between '01/01/2009' and '12/31/2009'The optimizer would use the non-clustered but only for scanning. [/quote] No, it would seek on it. The index is covering (since the clustering key is implicitly part of that index) and the predicate is SARGable and on a left-based subset of the index key, hence that query can be satisfied by a simple index seek.Fri, 21 Jan 2011 13:36:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxAwesome article series Gail!I know several development teams that could use to read these, if only I could make them.They keep creating single column indexes for all their joins, without analyzing the patterns of the joins, where clauses, or select statements to cover their most common queries properly *sigh*.Heh... at least the apps I work with I've taught my devs to read the execution plans, and see if they can either tweak an existing index or create a new better one to cover any new patterns that show up. :)Can't say it enough about the awesomeness of this series.Fri, 21 Jan 2011 13:34:30 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxI'm sorry but may be it is because I am 'old school'. I have been working with SQL Server for over 15 years. It used to be true that creating a clustered index on a key that would cause the table to increment always at the end ( i.e datetime, ID column) was bad if the table was very active (inserts). It would cause pages at the end of the table to be hot and contention would follow. When I did P & T gigs, we would pick a clustered index that would disperse this traffic and cause multiple entry points in the table. Yes this is more expensive but it would reduce locking issues.I was also told by an 'expert' that since a non-clustered index contains the clustered key that there was no reason to repeat this value in the non-clustered index. For example:Table a ( customer_no int, order_no int, order_date datetime, ....)clustered index on (customer_no, order_no)nonlcustered index (order_date)In this case the non-clustered index contains the key from the clustered so it acts like an index with all three columns. Is this true?If you do the following query:select customer_no, order_no, order_date from a where order_date between '01/01/2009' and '12/31/2009'The optimizer would use the non-clustered but only for scanning. Where if you had the customer_no, order_no, and order_date in the non-clustered then it would be a fully covered query. This becomes very importnat where you have fat tables and non-clustered indexes can be used to vertically splti tables. Again you need to balance this with the cost but if the DB is very report intensive it becomes a valuable key especially when aggregating data.Fri, 21 Jan 2011 12:39:11 GMTrgretzhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]OceanDeep (1/21/2011)[/b][hr]I agree that in this situation the optimzer is wrong on put the low selectvity in the key field of index.[/quote]Maybe. Maybe not. There's nothing wrong with a low selectivity column as an index key. It's probably not useful to have it there alone, unless the index is covering (which if missing indexes suggest it, it probably is)[quote]it suggests a new nonclustered index with the primary clustered key among other fields in the 'include', which I believe is totally redundant. Isn't it obvious that a nonclustered index already has the clustered key in the leaf page?[/quote]The clustered index key is present in the leaf and maybe non-leaf levels of all nonclustered indexes, but there's nothing wrong with explicitly specifying it. SQL won't add it twice (it's smarter than that) and what happens if the clustered index is moved? If you don't have the clustered index key explicitly specified and it's needed in the index, then that index becomes less useful just because the clustered index has changed. Not desired behaviour.Fri, 21 Jan 2011 11:13:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]Charles Kincaid (1/21/2011)[/b][hr]It's the GUI tools (SSMS or Enterprise Manager) making the PK clustered is what has lead folks down the garden path.[/quote]Not just the GUI tools, it's the default in SQL.If I say ALTER TABLE ADD CONSTRAINT PRIMARY KEY I'll get a primary key enforced by a unique clustered index. However it's perfectly valid to say ALTER TABLE ADD CONSTRAINT PRIMARY KEY NONCLUSTERED and get a primary key enforced by a unique nonclustered index.Fri, 21 Jan 2011 11:08:37 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote]Thus, my question:In the querySELECT *FROM table1 t1INNER JOIN table2 t2ON t1.col1 = t2.col1AND t1.col2 = t2.col2AND t1.col3 = t2.col3when I have three indexes, one each on col1, col2 and col3, how does the execution plan indicate that index intersection is being used?[/quote]Think of it this way, SQL filters the t1 first then t2 and joins the matching columns. Having composite indexes on both tables as col1+col2+col3 will give you the best performance as it uses one single index on each table instead of 3 different indexes on each table. If you read that blog given by Gilla explains it very well.When you have 3 indexes the SQL engine filters one table on the first index, then on the 2nd index and merge the results(intersects them), then filters the table on the third index and merges the result with the first merged result. It does that on the second table and in the end matches the rows.Fri, 21 Jan 2011 11:05:30 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxNo, the high selectivity fields (like primary key in a table) are in the joins and low slectivity field such as status is in the where clause. I agree that in this situation the optimzer is wrong on put the low selectvity in the key field of index. You are right that we should not always trust optimizer's suggestion as sometimes during its index tuning, it suggests a new nonclustered index with the primary clustered key among other fields in the 'include', which I believe is totally redundant. Isn't it obvious that a nonclustered index already has the clustered key in the leaf page? Why optimzer would suggust it in the 'include'? Am I missing something?After thinking more about this 'other-use-of-Include' opinion, let assume the join keys and the SARG in the where clause are both high selectivity. I agree with you that putting both as index keys would make the seek goes quicker. If I follow the opinion and put the SARG as the index key and put the join key in the Include, it would make the index smaller but the seek will take a little longer as more leaf records need to be searched but shouldn't be that bad. In fact, it does improve the query performance just like they are in the index fields. Would it be a trade-off between index size and seek time to determine the correct design of a nonclustered index? It probably depends on how big of the tables are and rows of records involved. Your thoughts...Fri, 21 Jan 2011 11:02:10 GMTOceanDeephttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]GilaMonster (1/21/2011)[/b][hr]A table can be a heap and have a pk. It can have a clustered index without a pk. Primary key != clustered index.[/quote]Here is a point that is not well understood at all. Thanks for confirming this. As I see this the "primary key" is what is used most often to look up entries in said table. It is the target of the foreign key. In real world terms this is the unique assigned row identifier for the table. Not SQL's internal RID. It is used to get a unique row.So one could have a table that has a primary key of type GUID and back that with a non-clustered index. The order of information within the pages or logically from page to page may not be important to me in this case.It's the GUI tools (SSMS or Enterprise Manager) making the PK clustered is what has lead folks down the garden path.Fri, 21 Jan 2011 10:52:29 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]Doug Bishop (1/21/2011)[/b][hr]Obviously, a join on PK is the best solution. Unfortunately these tables are all heaps, with no PK.[/quote]A table can be a heap and have a pk. It can have a clustered index without a pk. Primary key != clustered index.[quote]When I have three indexes, one each on col1, col2 and col3, how does the execution plan indicate that index intersection is being used?[/quote]Please look at the blog post I linked to.Fri, 21 Jan 2011 10:15:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]the sqlist (1/21/2011)[/b][hr][quote][b]Doug Bishop (1/21/2011)[/b][hr]Very good article. I do have a question. I am aware of SQL Server creating intersecting indexes when you have a query on multiple columns and each column has its own index. What I had not heard before is that SQL Server may not necessarily use that schema, but only use one and do a scan for remaining values. I am on SQL Server 2000. I am not much of an execution plan expert, but would like to know what to look for in the execution plan to determine if it is using a single index or multiple indexes. We have a lot of queries where we join to multiple tables on multiple columns. Most of these tables have individual indexes on the join columns, and before I heard about intersecting indexes, I was under the impression that only one index would be used, thus compound indexes should be used. I'd like to prove or disprove this 'hypothesis' by looking at some execution plans for some of the queries.[/quote]Obviously the preferred way is to use the composite indexes. If you use the composite index you'll get the result in one trip using only one index. The problem is if the columns in the composite key may not be used all in all queries. My approach is this:For the join always use the PKs(in general clustered), which also define the relations between tables. For any other filtering you need use non clustered composite indexes.Let's say you have a table with an ID column as PK with multiple columns of which 3 , A, B and C, are frequently used in where clauses. Because it could be possible to use any combination of these 3 keys(could be more) and not necessarily all 3 of them each time what I do is creating 3 indexes like this:A+B+CB+CCThis configuration covers pretty much any combination and the pattern could be used, if needed, with any number of columns.[/quote]Obviously, a join on PK is the best solution. Unfortunately these tables are all heaps, with no PK. The tables are built on a regular basis as part of an ETL process. Surrogate keys would have no meaning between tables and it would be basically impossible (at least unfeasable) to build PK-FK relationships. Some of our tables are completely rebuild from a daily feed of MTD data and the overhead in trying to build relational models, and the time and resources to do it would result in the model being completed just about when the next feed comes. A nautural key cannot even be defined, because it is possible to basically have duplicate data in our feed, and that is not only normal, but expected, as the front end would allow two of one item to come across as the same item twice, not the item once with a quantity of 2.Thus, my question:In the querySELECT *FROM table1 t1INNER JOIN table2 t2ON t1.col1 = t2.col1AND t1.col2 = t2.col2AND t1.col3 = t2.col3when I have three indexes, one each on col1, col2 and col3, how does the execution plan indicate that index intersection is being used?Fri, 21 Jan 2011 10:12:04 GMTDoug Bishophttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]Doug Bishop (1/21/2011)[/b][hr]Very good article. I do have a question. I am aware of SQL Server creating intersecting indexes when you have a query on multiple columns and each column has its own index. What I had not heard before is that SQL Server may not necessarily use that schema, but only use one and do a scan for remaining values. I am on SQL Server 2000. I am not much of an execution plan expert, but would like to know what to look for in the execution plan to determine if it is using a single index or multiple indexes. We have a lot of queries where we join to multiple tables on multiple columns. Most of these tables have individual indexes on the join columns, and before I heard about intersecting indexes, I was under the impression that only one index would be used, thus compound indexes should be used. I'd like to prove or disprove this 'hypothesis' by looking at some execution plans for some of the queries.[/quote]Obviously the preferred way is to use the composite indexes. If you use the composite index you'll get the result in one trip using only one index. The problem is if the columns in the composite key may not be used all in all queries. My approach is this:For the join always use the PKs(in general clustered), which also define the relations between tables. For any other filtering you need use non clustered composite indexes.Let's say you have a table with an ID column as PK with multiple columns of which 3 , A, B and C, are frequently used in where clauses. Because it could be possible to use any combination of these 3 keys(could be more) and not necessarily all 3 of them each time what I do is creating 3 indexes like this:A+B+CB+CCThis configuration covers pretty much any combination and the pattern could be used, if needed, with any number of columns.Fri, 21 Jan 2011 10:00:53 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[url]http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/[/url]Fri, 21 Jan 2011 09:32:10 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxVery good article. I do have a question. I am aware of SQL Server creating intersecting indexes when you have a query on multiple columns and each column has its own index. What I had not heard before is that SQL Server may not necessarily use that schema, but only use one and do a scan for remaining values. I am on SQL Server 2000. I am not much of an execution plan expert, but would like to know what to look for in the execution plan to determine if it is using a single index or multiple indexes. We have a lot of queries where we join to multiple tables on multiple columns. Most of these tables have individual indexes on the join columns, and before I heard about intersecting indexes, I was under the impression that only one index would be used, thus compound indexes should be used. I'd like to prove or disprove this 'hypothesis' by looking at some execution plans for some of the queries.Fri, 21 Jan 2011 09:26:00 GMTDoug Bishophttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]OceanDeep (1/21/2011)[/b][hr]The opinion is that in a query that consists of join and where clause. We should put the SGR in the where clause as the key field in the nonclused index and put the fields used in the join in the 'include'. [/quote]I would put both as key columns. They're both potentially needed as search arguments, hence they belong in the key. Include is for columns that are selected but not searched (or joined) on[quote] Sometimes this type of nonclustered index is quite confusing to me as it contradicts the genernal understanding as you describe. Furthermore, if the where clause consists of very low selectivity field such as status, the optimizer still suggests the status field as the main key field in the index and put other high selectivity columns in the 'Include' field. This really doesn't make sense to me.[/quote]Are those 'high selective' columns used in the where clause? If they're only in the select, there's no point in making them key columns as they aren't search arguments.Bear in mind that the missing index suggestions are just that. Suggestions. Often wrong because the optimiser's only ever looking at one query at a time.Fri, 21 Jan 2011 09:22:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxGreat series.. They bring back some forgotten concepts. Thanks I do have a question on the 'Include' feature in nonclustered indexes. You touch on how to use it in your article, which I agree. I have come across some articles and even from the SQL optimzer execution plan about how the 'include' is used in a totally different way than you describe, and I like to get your opinion on. The opinion is that in a query that consists of join and where clause. We should put the SGR in the where clause as the key field in the nonclused index and put the fields used in the join in the 'include'. When I was turning a query and sometimes query optimizer in SQL 2008 would recognize a missing index and suggest a new one, the new index actually is constructed as what the opinion mentioned. Sometimes this type of nonclustered index is quite confusing to me as it contradicts the genernal understanding as you describe. Furthermore, if the where clause consists of very low selectivity field such as status, the optimizer still suggests the status field as the main key field in the index and put other high selectivity columns in the 'Include' field. This really doesn't make sense to me.Let me know your thoughts or if others have better understanding of this, feel free to chime in.WFri, 21 Jan 2011 09:10:52 GMTOceanDeephttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspxGreat series Gail. Learned a lot as well as a great refresher.Fri, 21 Jan 2011 08:44:51 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]GilaMonster (1/21/2011)[/b][hr][quote][b]the sqlist (1/21/2011)[/b][hr]"Include columns are useful in that they are columns available in the index but not contributing to the size of the index key"...Included columns [i]do[/i] affect the index size.[/quote]I never said they don't affect the index size. I said they don't affect the size of the index [b]key[/b], which they do not because they are not part of the index key and hence do not count towards the 900 byte limit on the index key.They are stored only at the leaf level of the index, not at the intermediate levels but since they are stored at the leaf level, they are part of the index and hence will contribute to the total size of the index on disk.[/quote]I agree, I missed the nuance, but the important thing here and worth mentioning is the overall index size. Key of course remains the same.Fri, 21 Jan 2011 06:37:19 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx[quote][b]the sqlist (1/21/2011)[/b][hr]"Include columns are useful in that they are columns available in the index but not contributing to the size of the index key"...Included columns [i]do[/i] affect the index size.[/quote]I never said they don't affect the index size. I said they don't affect the size of the index [b]key[/b], which they do not because they are not part of the index key and hence do not count towards the 900 byte limit on the index key.They are stored only at the leaf level of the index, not at the intermediate levels but since they are stored at the leaf level, they are part of the index and hence will contribute to the total size of the index on disk.Fri, 21 Jan 2011 05:32:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic819337-1390-1.aspx"Include columns are useful in that they are columns available in the index but not contributing to the size of the index key"I don't think this is true. I worked on a table with 45M rows, which had about 18 non clustered indexes. Several of them were created with included columns(and some of them had lots) and their size was enormous. More than half the size of that db was in this table's indexes only. I dropped the 3 largest of them and I freed loads of space. Included columns [i]do[/i] affect the index size. I must mention that those indexes were created in time by using the tuning advisor tool in MS for different queries by applying the recommendations. This tool might help with specific queries but they end up creating these huge indexes with included columns, which endup eating lots of space and actually killing performance.Fri, 21 Jan 2011 05:26:17 GMTthe sqlist