May 14, 2009 at 10:10 am
Hi,
the optimizer uses a loop join where i wish to get a hash join.
the tables have clustered indexes on the right columns and the statistics of the indexes have to be up-to-date as i recreated them.
If I use the hint "inner hash join" the query performs well, if i let the optimizer decide, it performs really bad with a loop join.
What should i check next, if i want to let the optimizer get the ideal plan?
Thank you,
Tobias
May 14, 2009 at 10:13 am
You need to check a few things.
Do you have good indexes that the optimiser can use.
Do you have good statistics that the optimizer can use.
Could post your table definition , index definition and query and if possible the query plan saved as a .sqlplan file
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 14, 2009 at 10:34 am
Highly likely it's a statistics problem. If SQL estimates a small number of rows it's likely to use a loop join.
Please post query and exec plan (without hash join hint)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2009 at 11:21 am
I attached the query and the plan and also the query with the hint, so you can see, where the problem is.
the following cix did not help:
create UNIQUE clustered index cix_aggregationsebenen_aufloesung_rf
on transformation.var_aggregationsebenen_aufloesung_rf(datenversion, kfg_zeitpunkt, master_exclude_zeitpunkt, fk_aggregationsebene, fk_risikofaktor)
on [FG_Data]
May 14, 2009 at 11:29 am
I'll look at the plans later. Just one thing now. That's a very big clustered index. Clustered indexes should be as narrow as possible because they're included in every nonclustered index. I don't know how big the columns are, but 5 columns is usually a bit much for the cluster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2009 at 2:43 am
Hi Gail,
When you say that a Clustered index is included in every non-clustered index, what do you mean by this?
Do you have where I could read up about this and test it out for performance?
I've thought that queries run slowly with large clustered indexes and then after adding a small non-clustered to run fast or is that not correct?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 15, 2009 at 2:56 am
Christopher Stobbs (5/15/2009)
When you say that a Clustered index is included in every non-clustered index, what do you mean by this?
The clustering key is the row's identifier. Hence it is part of every single nonclustered index. That means that a NC index can never be smaller than the cluster.
Wasn't really the point of the article, but - http://sqlinthewild.co.za/index.php/2009/02/09/all-indexes-are-unique/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply