Running SQL 2012 SP2
I've got this query that runs in 30 seconds and returns about 24000. The table variable returns about 145 rows (no performance issue here), and the TransactionTbl table has 14.2 Million rows, a compound, clustered primary key, and 6 non-clustered indexes, none of which meet the needs of the query.
declare @CltID varchar(15) = '12345'
declare @TranDate datetime = '2015-07-25'
declare @Ballance table
CurrencyID varchar(3) )
insert into @Ballance
select * from GLSchemB.fn_GBCAA(@CltID, @TranDate)
select G.Ledger_Code, G.AssetID, G.Amount , G.CurrencyID
from GLSchemB.TransactionTbl g
inner join @Ballance a
on B.CurrencyID = G.CurrencyID and
B.AssetID = G.AssetID and
B.Ledger_Code = G.Ledger_Code
where G.CltID = @CltIDo and
G.Date <= @TranDate
Actual execution plan shows SQL is doing an index seek, then a nested loop join, and then fetching the remaining data from the TransactionTbl using a Key Lookup.
I designed a new indexes based on the query, which when I force it's usage via an index hint, reduces the run time to sub-second, but without the index hint the SQL optimiser won't use the new index, which looks like this:
CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date) include (Ledger_Code, Amount, CurrencyID, AssetID)
and I tried this:
CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date, Ledger_Code, CurrencyID, AssetID) include (Amount)
and even a full covering index!
I did some testing, including disabling all indexes but the PK, and the optimiser tells me I've got a missing index and recommends I create one EXACTLY like the one I designed, but when I put my one back it doesn't use it.
I though this may be due to fragmentation and/or stats being out of date, so I rebuilt the PK and my index, and the optimiser started using my index, doing an index seek and running sub-second. Thinking I had solved the problem I rebuilt all the indexes, testing after each one, and my index was used BUT as soon as I flushed the related query plan, the optimiser went back to using a less optimal index, with a seek and key lookup plan and taking 30 seconds.
For now I've resorted to using the OPTION (TABLE HINT(G, INDEX(IX_Test))) to force this, but it's a work around only.
Does anyone have any ideas why the optimiser would select a less optimal query plan? Any other things that could be causing this, or where I should be looking. I'm starting to think there is a bug in the optimiser that is weighting it too heavily in favour of the primary key.
Senior DBA Consultant
SQL Services Ltd
Nothing in life is ever so complicated that with a little work you can't make it more complicated!
Nothing in life is ever so complicated that with a little work it can't be made more complicated.