InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 20:02:01 GMT20http://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Brandie Tarvin (2/11/2013)[/b][hr]Thank you for expounding, Paul. I learned something new this morning.[/quote]Ditto. Love randomly finding a thread with great information in!Wed, 13 Feb 2013 04:18:20 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxThank you for expounding, Paul. I learned something new this morning.Mon, 11 Feb 2013 05:00:19 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Lynn Pettis (2/8/2013)[/b][hr]At this point, I will bow out and defer to Paul. He has spent much more time than I digging into these things than I and has a much better understanding of indexing and the optimizer.[/quote]I feel I should stress that pretty much everything you guys have said on this thread so far has been accurate; the problem we are dealing with here is the optimizer making what looks like the right choice from its point of view, but which turns out to be highly suboptimal because it doesn't have good information to base its choices on, and the model it uses is a very general-purpose affair. The obvious solution has already been suggested - provide an obvious best-choice (covering) index. The alternative is to create better statistics, or to accept the out-of-model condition and use an index hint. So, overall, I'm just adding extra analysis around the points already made in case it is of interest to Roger.Fri, 08 Feb 2013 18:56:16 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxAt this point, I will bow out and defer to Paul. He has spent much more time than I digging into these things than I and has a much better understanding of indexing and the optimizer.Fri, 08 Feb 2013 18:46:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Roger Sabin (2/8/2013)[/b][hr]I am having a hard time understanding why the first index would be used when it requires tremendously more IO and time than the second index to return the same records. Why isn't it obvious to the query optimizer that the first index costs more than the second index?[/quote]There are a number of reasons.First up, where there is an obvious covering nonclustered index for the query, the optimizer picks it straight away at the trivial plan stage. The plan is chosen before the optimizer starts exploring the space of possible plans and choosing one based on lowest estimated cost. You can see this in the SSMS execution plan view by clicking on the SELECT icon and looking at the Optimization Level property in the Properties window - it shows TRIVIAL for the CorrectIndex.sqlplan file.For the second query, there is no longer an obviously best choice, and although the trivial plan logic is still run, it does not find a plan. So, the optimizer starts generating plan alternatives and costing them. One alternative is to seek the clustered index:ProcessYear = 2013ProcessMonth = 1...and then apply a residual filter to rows that match:VisionAccountNumber = '1234'The alternative is to seek the nonclustered index:ProcessYear = 2013ProcessMonth = 1VisionAccountNumber = '1234'...and then use the clustered index keys stored in the leaf level of the nonclustered index to locate the base row in the clustered index and return the value it finds for CustomerName. This process repeats for each row found by the nonclustered index seek.These two alternatives are assigned an estimated cost using the query optimizer's cost model, and this is where the problem occurs. The model is just a model, of course, one which happens to work pretty well in practice over the broad range of queries, database designs, and hardware in use across the world. It does not look to make a precise estimate of the cost of the query in your particular circumstances, and the cost it produces is also dependant on the quality of statistical information it has to work with.Now, without scripted statistics for the table in question I cannot work through the numbers and show you precisely why the clustered index seek + residual predicate is chosen over the nonclustered seek + base table lookups, but if you compare the estimated plan costs for both (using an index hint as necessary) you will see the clustered access path appears to be cheaper.One reason for this is that the optimizer assumes each query starts with a cold data cache, so all I/Os will at least initially be physical. The model also assumes sequential I/O (scanning a range of the clustered index) will be much faster than the random I/O expected to result from a given number of lookups from the nonclustered index to the base table.The main reason though is that the optimizer is grossly underestimating the number of rows returned by the range seek of the clustered index which will need to have the residual filter applied. This may be due to a lack of good statistics on the column combination concerned, or it may be a consequence of the way predicates are modelled. You can check this by issuing a query like SELECT COUNT_BIG(*) FROM UNMonthly_CMS WHERE ProcessYear = 2012 AND ProcessMonth = 1 OPTION (RECOMPILE); the estimated number of rows ought to be quite close to the actual rows.If you fancy scripting out the indexes and statistics using SSMS, I can go into more detail.Fri, 08 Feb 2013 18:29:37 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxI understand that using the first index (the clustered one) allows SQL Server to retrieve any column in the record once it has found the record it wants (using the where clause). Whereas, the second index requires a separate lookup to get any column not in the index which does take more time.I am having a hard time understanding why the first index would be used when it requires tremediously more IO and time than the second index to return the same records. Why isn't it obvious to the query optimizer that the first index costs more than the second index?For example, executing the following queries (the first one uses the clustered index, the second one uses the second index, the third one is being forced to use the second index though it has a non-indexed column):[code="sql"]set statistics io onset statistics time onselect ProcessYear, ProcessMonth, VisionAccountNumber, CustomerName from UN_MonthlyCMS where ProcessYear = 2013 and ProcessMonth = 1 and VisionAccountNumber = '1234'select ProcessYear, ProcessMonth, VisionAccountNumber from UN_MonthlyCMS where ProcessYear = 2013 and ProcessMonth = 1 and VisionAccountNumber = '1234'select ProcessYear, ProcessMonth, VisionAccountNumber, CustomerName from UN_MonthlyCMS with (index (IDX_MonthlyCMS_ProcessYear_ProcessMonth_VisionAccountNumber_CreditPlanNumber)) where ProcessYear = 2013 and ProcessMonth = 1 and VisionAccountNumber = '1234'set statistics time offset statistics io off[/code]you get this:[code="other"]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.(0 row(s) affected)Table 'UN_MonthlyCMS'. Scan count 1, logical reads 355528, physical reads 7, read-ahead reads 216495, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 2312 ms, elapsed time = 147795 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.(0 row(s) affected)Table 'UN_MonthlyCMS'. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 35 ms.(0 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'UN_MonthlyCMS'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.[/code]Though these queries are using a bogus account number so that no actual records are found, when I ran them with a real account number I got similar IO usage and times.Fri, 08 Feb 2013 16:53:06 GMTRoger Sabinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Roger Sabin (2/8/2013)[/b][hr]So, you are saying that SQL Server determined --the cost of finding a few records (using second index) + lookup for other fields was greater than ---the cost of searching through thousands of records (using first index) to find the few matching that where clause but without a need to do a lookup for the additional fields neededand that is why the first index was chosen.I always thought that the index with the greatest number of equality matches (in the order of the columns in the index without any gaps) was always selected. Only when there were range matches or two or more indexes could satisfy the equality matches would it take into account things like lookups.[/quote]Look at your execution plans, SQL Server performed an INDEX SEEK on both of them to satisify the respective queries. SQL Server did not have to scan the clustered index to find the data to satisify the query that also returned the value from the CompanyName column (if I remembered the column correctly).Fri, 08 Feb 2013 15:37:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Roger Sabin (2/8/2013)[/b][hr]So, you are saying that SQL Server determined --the cost of finding a few records (using second index) + lookup for other fields was greater than ---the cost of searching through thousands of records (using first index) to find the few matching that where clause but without a need to do a lookup for the additional fields neededand that is why the first index was chosen.I always thought that the index with the greatest number of equality matches (in the order of the columns in the index without any gaps) was always selected. Only when there were range matches or two or more indexes could satisfy the equality matches would it take into account things like lookups.[/quote]It didn't search through thousands of records, the clustered index was selective enough that it performed a Clustered Index Seek, not a Clustered Index Scan and found the records and didn't then have to perform a second Clustered index seek and then combine the results in order to get the full data. If you want it to use the non-clustered index, make the non-clustered index include the columns, otherwise the clustered index seek operation is more efficient.Fri, 08 Feb 2013 12:48:11 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxRoger,If I understand things correctly, it isn't about equality of the Account Number match. It's about you asking the engine for more information than the nonclustered index could easily supply. The engine had a choice, do 2 searches with the nonclustered index or do 1 search with the clustered index. Since the clustered index is physically sorted by the order of the index-specified columns, the engine knew where to find all relevant information tied to the account number. It didn't have to grab a pointer and then search all the relevant levels of the index for the additional information.You are correct that the nonclustered index is faster when no other information (save the columns listed in the index) has been requested.Fri, 08 Feb 2013 12:46:14 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxSo, you are saying that SQL Server determined --the cost of finding a few records (using second index) + lookup for other fields was greater than ---the cost of searching through thousands of records (using first index) to find the few matching that where clause but without a need to do a lookup for the additional fields neededand that is why the first index was chosen.I always thought that the index with the greatest number of equality matches (in the order of the columns in the index without any gaps) was always selected. Only when there were range matches or two or more indexes could satisfy the equality matches would it take into account things like lookups.Fri, 08 Feb 2013 12:37:11 GMTRoger Sabinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Brandie Tarvin (2/8/2013)[/b][hr]Lynn, for my own edification... This is because Name isn't included in either index, right? So the Clustered Index is easier to use to find name as associated with the rest of the data that actually is in the index?[/quote]All fields are always part of the clustered index. If you don't specify a field as part of the clustering key, it's still included in the clustered index because the clustered index is the table itself.Fri, 08 Feb 2013 12:22:09 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Brandie Tarvin (2/8/2013)[/b][hr][quote][b]Lynn Pettis (2/8/2013)[/b][hr][quote][b]Roger Sabin (2/8/2013)[/b][hr]I understand what's been said.But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account. Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?[/quote]You are doing an equality comparision on AccountNumber on both indexes. SQL Server is doing a seek on both indexes. For your second query, if SQL Server used the nonclustered index, it would then have to do a bookmark lookup to the table (in this case the clustered index) to get the rest of the data to satisfy the query. Instead it chose to do an index seek on the clustered index as this satisfies the query.[/quote]Lynn, for my own edification... This is because Name isn't included in either index, right? So the Clustered Index is easier to use to find name as associated with the rest of the data that actually is in the index?[/quote]Think of it, in this case, as a covering index since all columns not part of the index are also included by definition. Yes, if Name had been a part of the nonclustered index, perhaps as suggested an included column, then SQL Server would have used that index instead. It is much smalleer than the clustered index (fewer columns, more "data" per page, etc).Fri, 08 Feb 2013 12:21:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Lynn Pettis (2/8/2013)[/b][hr][quote][b]Roger Sabin (2/8/2013)[/b][hr]I understand what's been said.But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account. Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?[/quote]You are doing an equality comparision on AccountNumber on both indexes. SQL Server is doing a seek on both indexes. For your second query, if SQL Server used the nonclustered index, it would then have to do a bookmark lookup to the table (in this case the clustered index) to get the rest of the data to satisfy the query. Instead it chose to do an index seek on the clustered index as this satisfies the query.[/quote]Lynn, for my own edification... This is because Name isn't included in either index, right? So the Clustered Index is easier to use to find name as associated with the rest of the data that actually is in the index?Fri, 08 Feb 2013 11:34:41 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Roger Sabin (2/8/2013)[/b][hr]I understand what's been said.But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account. Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?[/quote]You are doing an equality comparision on AccountNumber on both indexes. SQL Server is doing a seek on both indexes. For your second query, if SQL Server used the nonclustered index, it would then have to do a bookmark lookup to the table (in this case the clustered index) to get the rest of the data to satisfy the query. Instead it chose to do an index seek on the clustered index as this satisfies the query.Fri, 08 Feb 2013 11:26:04 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxIt might help us a little to see the queries in question.I'm looking at the pictures generated by the execution plans and I see a difference in the predicate searches that might point to why the clustered index is being chosen over the non-clustered index. The indexes themselves seem to have the same relative cost, but I'm still learning how to interpret the data on these, so I could be wrong.Fri, 08 Feb 2013 11:25:55 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxI understand what's been said.But I still don't see why the first index is more "correct" when it requires trolling through thousands of records to find just the few for an account when the second index goes directly to the few records for the account. Doesn't the equality comparison on the AccountNumber column make the second index a better choice than the first index?Fri, 08 Feb 2013 11:12:31 GMTRoger Sabinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxLynn's spot on with this.Because the Name field is missing from the non-clustered index it would require a seek on the non-clustered followd by a bookmark lookup to the clustered index for the fields missing from the index. That's more expensive than using the Clustered index once to perform the entire query.If you want to use the non-clustered index, INCLUDE the name field in the index.i.e.[code]DROP INDEX UN_MonthlyCMS.[IDX_MonthlyCMS_ProcessYear_ProcessMonth_VisionAccountNumber_CreditPlanNumber]CREATE INDEX [IDX_MonthlyCMS_ProcessYear_ProcessMonth_VisionAccountNumber_CreditPlanNumber] ON UN_MonthlyCMS(ProcessYear, ProcessMonth, VisionAccountNumber) INCLUDE (CustomerName)[/code]Fri, 08 Feb 2013 07:16:41 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspx[quote][b]Roger Sabin (2/7/2013)[/b][hr]The two indexes are as follows:Name: IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumberIndexed Columns: YearNum, MonthNum, AltKey and AccountNumberName: IDX_MonthlyAccount_YearNum_MonthNum_AccountNumberIndexed Columns: YearNum, MonthNum, AccountNumberIf we use the following select statement, the second index (IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber) is correctly used:select YearNum, MonthNum, AccountNumber from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'But, if we use the following select statement, the first index (IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber) is incorrectly used:select YearNum, MonthNum, AccountNumber, Name from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'[/quote]Aside from what Lynn is saying, I recently learned that indexes are also chosen based on column order. I do not know why the first index (that has AltKey) is being chosen for the first query, but SQL Server is correctly choosing the second index on the second query because the order of your SELECT follows the order of columns defined in the second index. That's part of how it knows which indexes it can use.Food for thought. EDIT: Lynn, feel free to correct me if I'm wrong. I'm still figuring out the innards of indexing.Fri, 08 Feb 2013 05:16:46 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxYour "wrong index" is the clustered index. Since SQL Server needed to go to the table for the additional information not included in the nonclustered index (the "correct index"), SQL Server decided it was cheaper to do an INDEX SEEK on the clustered index rather than an INDEX SEEK on the nonclustered index with a bookmark lookup to the data for the additional information.Looks to me as if SQL Server used the "correct index" where you believe it used the "wrong index" for the second query.Thu, 07 Feb 2013 18:00:36 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxExecution plans from our production for both types of select statementsThu, 07 Feb 2013 17:09:12 GMTRoger Sabinhttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxNot enough information. If you could post the actual execution plan (saved as a .sqlplan file) for both queries we could probably give you a better answer.As it is, we no little about the table or the actual definition of the indexes, how much data is being returned, etc.Thu, 07 Feb 2013 16:48:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1417384-338-1.aspxI was wondering if anyone has seen this before and has any idea why it is happening. This is occuring in our production. (All column, table and index names have been changed to protect the guilty.) Problem involves a single select statement on a single table.First the background information:We are using SQL Server 2005.Table has 40,257,935 rows.Table has 390 columns.Table has several indexes of which only 2 are pertinent to this discussion.The table stores a monthy snapshot of account information. The two indexes are as follows:Name: IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumberIndexed Columns: YearNum, MonthNum, AltKey and AccountNumberName: IDX_MonthlyAccount_YearNum_MonthNum_AccountNumberIndexed Columns: YearNum, MonthNum, AccountNumberIf we use the following select statement, the second index (IDX_MonthlyAccount_YearNum_MonthNum_AccountNumber) is correctly used:select YearNum, MonthNum, AccountNumber from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'But, if we use the following select statement, the first index (IDX_MonthlyAccount_YearNum_MonthNum_AltKey_AccountNumber) is incorrectly used:select YearNum, MonthNum, AccountNumber, Name from MonthlyAccount where YearNum = 2013 and MonthNum = 1 and AccountNumber = 'ABCDEF'The only change between the two selects is the addition of a column (Name) in the select list that is not part of the second index.Why is the wrong index being used in the second select?Thu, 07 Feb 2013 16:27:05 GMTRoger Sabin