SQL Optimizations - Manual Update statistics on small tables may provide a big impact.

Interesting Finds

Jason Haley — Fri, 12 May 2006 19:44:06 GMT

Manual Update statistics on small tables may provide a big impact

I want some Moore! — Fri, 12 May 2006 23:24:18 GMT

re: SQL Optimizations - Manual Update statistics on small tables may provide a big impact.

Peter K — Mon, 16 Jul 2007 03:46:13 GMT

I have a case in a star schema where the fact has many small dimensions around it and a handful of large dimensions.

The small dimenions typically have between 10 and 100 rows in them.

When the fact is joined out to all the dimensions, the optimizer halves its estimate of the size of the result set each time it joins to one of the small dimensions.

If the initial result set is say 100k rows, the estimate drops to 50K after the join to the first small dimension, 25k after the second, 12k after the third... roughly speaking.

Once the estimate of the result set gets to about 10k rows, the joins switch from hash joins to nested loop joins.

Net result is lots of nested loop joins occurring on results sets that are 100k and bigger.

The stats have been updated manually update statistics <tablename> with fullscan

Once a bit of noise is added to the dimensions the optimizer starts to get estimates of the result set correct.

About 1,000 rows seems to do the trick.

Is there a simple way to get the optimizer making the correct estimates of the size of the result set on these joins to the small dimensions

Microsoft SQL ISV Program Management Team SQL Optimizations Manual | unemployment office

Tue, 16 Jun 2009 11:02:45 GMT

PingBack from http://unemploymentofficeresource.info/story.php?id=15610

Microsoft SQL ISV Program Management Team SQL Optimizations Manual | patio set

Microsoft SQL ISV Program Management Team SQL Optimizations Manual | patio set — Thu, 18 Jun 2009 05:01:58 GMT

PingBack from http://patiosetsite.info/story.php?id=981

Microsoft SQL ISV Program Management Team SQL Optimizations Manual | adirondack chairs

Fri, 19 Jun 2009 12:28:12 GMT

PingBack from http://adirondackchairshub.info/story.php?id=3773

Microsoft SQL ISV Program Management Team SQL Optimizations Manual | debt solutions

Fri, 19 Jun 2009 19:43:43 GMT

PingBack from http://debtsolutionsnow.info/story.php?id=6802