Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sherrer
64 Posts |
 Posted - 2002-11-26 : 19:06:22
|
| A developer brought a situation to me today that goes against everything I know about SQL Server.Below are identical queries except for the select statement. The excecution plans are also showed. The acctount table has the AcctID as the PK, and has a clustered index on the AcctNo column. The BillHist table has TransID as the PK, the TransDt as a clustered index, and AcctID as a non-clustered index.The execution plans show nearly the same subtree cost, but the second query takes 100 times longer to run than the first. The second query chooses the clustered index over the index on AcctID. The first query chooses the index on AcctID like expected. We have made sure all statists have been recomputed over all the indexes using all the data.The other odd thing is that when transid is not used in the select statement, both queries run exactly the same. As soon as transid is put back in, it reverts back to the wrong excecution plan. Also, if the acctno in the where clause is changed, it again chooses the correct excecution plan.I don't see why the select statement changes the execution plan, nor do I understand why the different acctno make the change either. Neither should affect the execution plan; correct?Please let me know if I can tell more about the situation.Thanks-----------------------------------------------------------------------SELECT *FROM dbo.Account INNER JOIN dbo.BillHist ON dbo.Account.AcctID = dbo.BillHist.AcctIDWHERE (dbo.Account.AcctNo LIKE '42-93-122606%') |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([USIBillingData].[dbo].[BillHist]) WITH PREFETCH) 61 3 1 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1002]), OBJECT:([USIBillingData].[dbo].[BillHist]) WITH PREFETCH [BillHist].[TransID], [BillHist].[InvoiceID], [BillHist].[CReadHistID], [BillHist].[PReadHistID], [BillHist].[TransDt], [BillHist].[AcctID], [BillHist].[TransAmt], [BillHist].[RevCode], [BillHist].[OpenBal], [BillHist].[Bucket] 16388.223 101.51875 0.0 349 109.8752 [Account].[AcctID], [Account].[DiscTypeCode], [Account].[SysAcctStatCode], [Account].[SkipRsnCode], [Account].[AcctNo], [Account].[AcctGrpID], [Account].[IsActive], [Account].[StartDt], [Account].[InDate], [Account].[OutDate], [Account].[Occupants], [Accou NULL PLAN_ROW 0 1.0 |--Parallelism(Gather Streams) 61 6 3 Parallelism Gather Streams NULL NULL 16388.223 0.0 0.43688631 270 8.3564472 [Account].[AcctID], [Account].[DiscTypeCode], [Account].[SysAcctStatCode], [Account].[SkipRsnCode], [Account].[AcctNo], [Account].[AcctGrpID], [Account].[IsActive], [Account].[StartDt], [Account].[InDate], [Account].[OutDate], [Account].[Occupants], [Accou NULL PLAN_ROW 0 1.0 |--Nested Loops(Inner Join) 61 7 6 Nested Loops Inner Join NULL NULL 16388.223 0.0 6.8502769E-2 270 7.9195609 [Account].[AcctID], [Account].[DiscTypeCode], [Account].[SysAcctStatCode], [Account].[SkipRsnCode], [Account].[AcctNo], [Account].[AcctGrpID], [Account].[IsActive], [Account].[StartDt], [Account].[InDate], [Account].[OutDate], [Account].[Occupants], [Accou NULL PLAN_ROW 1 1.0 |--Filter(WHERE:(like([Account].[AcctNo], '42-93-122606%'))) 61 10 7 Filter Filter WHERE:(like([Account].[AcctNo], '42-93-122606%')) NULL 1340.8677 0.0 2.4135619E-4 231 2.6714996E-2 [Account].[AcctID], [Account].[DiscTypeCode], [Account].[SysAcctStatCode], [Account].[SkipRsnCode], [Account].[AcctNo], [Account].[AcctGrpID], [Account].[IsActive], [Account].[StartDt], [Account].[InDate], [Account].[OutDate], [Account].[Occupants], [Accou NULL PLAN_ROW 1 1.0 | |--Clustered Index Seek(OBJECT:([USIBillingData].[dbo].[Account].[IDX_AcctNo]), SEEK:([Account].[AcctNo] >= '42-93-122605þ' AND [Account].[AcctNo] < '42-93-122607') ORDERED) 61 11 10 Clustered Index Seek Clustered Index Seek OBJECT:([USIBillingData].[dbo].[Account].[IDX_AcctNo]), SEEK:([Account].[AcctNo] >= '42-93-122605þ' AND [Account].[AcctNo] < '42-93-122607') ORDERED [Account].[AcctID], [Account].[DiscTypeCode], [Account].[SysAcctStatCode], [Account].[SkipRsnCode], [Account].[AcctNo], [Account].[AcctGrpID], [Account].[IsActive], [Account].[StartDt], [Account].[InDate], [Account].[OutDate], [Account].[Occupants], [Accou 1340.8677 2.4106277E-2 1.5628405E-3 231 2.5669118E-2 [Account].[AcctID], [Account].[DiscTypeCode], [Account].[SysAcctStatCode], [Account].[SkipRsnCode], [Account].[AcctNo], [Account].[AcctGrpID], [Account].[IsActive], [Account].[StartDt], [Account].[InDate], [Account].[OutDate], [Account].[Occupants], [Accou NULL PLAN_ROW 1 1.0 |--Index Seek(OBJECT:([USIBillingData].[dbo].[BillHist].[idx_AcctID]), SEEK:([BillHist].[AcctID]=[Account].[AcctID]) ORDERED) 61 15 7 Index Seek Index Seek OBJECT:([USIBillingData].[dbo].[BillHist].[idx_AcctID]), SEEK:([BillHist].[AcctID]=[Account].[AcctID]) ORDERED [Bmk1002] 12.222103 6.3284999E-3 9.1980983E-5 48 7.8243432 [Bmk1002] NULL PLAN_ROW 0 1340.8677SELECT BillHist.transid, account.acctnoFROM dbo.Account INNER JOIN dbo.BillHist ON dbo.Account.AcctID = dbo.BillHist.AcctIDWHERE (dbo.Account.AcctNo LIKE '42-93-122606%') |--Parallelism(Gather Streams) 62 18 16 Parallelism Gather Streams NULL NULL 16388.223 0.0 0.11348113 84 99.912361 [BillHist].[transid], [Account].[acctno] NULL PLAN_ROW 0 1.0 |--Hash Match(Inner Join, HASH:([Account].[AcctID])=([BillHist].[AcctID]), RESIDUAL:([BillHist].[AcctID]=[Account].[AcctID])) 62 19 18 Hash Match Inner Join HASH:([Account].[AcctID])=([BillHist].[AcctID]), RESIDUAL:([BillHist].[AcctID]=[Account].[AcctID]) NULL 16388.223 0.0 25.181133 84 99.798882 [BillHist].[transid], [Account].[acctno] NULL PLAN_ROW 1 1.0 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Account].[AcctID])) 62 20 19 Parallelism Repartition Streams PARTITION COLUMNS:([Account].[AcctID]) NULL 1340.8677 0.0 3.7050042E-2 227 0.10081508 [Account].[acctno], [Account].[AcctID] NULL PLAN_ROW 1 1.0 | |--Filter(WHERE:(like([Account].[acctno], '42-93-122606%'))) 62 21 20 Filter Filter WHERE:(like([Account].[acctno], '42-93-122606%')) NULL 1340.8677 0.0 2.4135619E-4 227 6.3765042E-2 [Account].[acctno], [Account].[AcctID] NULL PLAN_ROW 1 1.0 | |--Clustered Index Seek(OBJECT:([USIBillingData].[dbo].[Account].[IDX_AcctNo]), SEEK:([Account].[acctno] >= '42-93-122605þ' AND [Account].[acctno] < '42-93-122607') ORDERED) 62 22 21 Clustered Index Seek Clustered Index Seek OBJECT:([USIBillingData].[dbo].[Account].[IDX_AcctNo]), SEEK:([Account].[acctno] >= '42-93-122605þ' AND [Account].[acctno] < '42-93-122607') ORDERED [Account].[acctno], [Account].[AcctID] 1340.8677 2.4106277E-2 1.5628405E-3 227 2.5669118E-2 [Account].[acctno], [Account].[AcctID] NULL PLAN_ROW 1 1.0 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([BillHist].[AcctID])) 62 26 19 Parallelism Repartition Streams PARTITION COLUMNS:([BillHist].[AcctID]) NULL 3981791.0 0.0 21.846724 127 74.553978 [BillHist].[transid], [BillHist].[AcctID] NULL PLAN_ROW 1 1.0 |--Clustered Index Scan(OBJECT:([USIBillingData].[dbo].[BillHist].[IDX_TransDt])) |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-26 : 20:01:00
|
Kevin, GO HOME ALREADY!  When using SELECT *, the optimizer is free(er) to choose any index in aid of the execution plan, even if it's not something that's specified in the JOIN or WHERE clauses. That is why it's using different indexes. Changing the account number is probably forcing a recompile; in changing it back to the original value it is probably pulling the older, inefficient plan from cache. Same applies for the data: a new account number would probably require a fresh read from disk, the cache would be flushed of the old data, and going back to the original account number needs to read from disk again.Also, by specifying columns from BillHist explicitly, it seems that it's using a worktable of some kind (the hash matches in the second plan) and that's probably what's eating up most of the time. There's some more detail in Books Online about hash joins (and the other types too) It seems that the BillHist table is large enough that the optimizer has to partition the hash (can't be done entirely in memory); there's a number of partitions in the second plan.Try running DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS (unless you're running some heavy processes on the server that need to complete) before each run of the query, and compare the times and plans.And GO HOME ALREADY! You're working way too late.  |
 |
|
|
sherrer
64 Posts |
Posted - 2002-11-26 : 21:53:37
|
| Thanks Rob :)I will take a look at your suggestions. I have never seen the select statement make a difference in query execution before. I can understand a change in the where clause.And I did leave work right after posting... |
|
|
|
|
|
|
|
|
Problem with excecution plan