tresiqus (5/13/2014)
Well, as far as I can tell, the config option to optimize for ad-hoc workloads wasn't available in Sql Server 2000. But it's been awhile since I'd had to work with Sql 2K, so I may have missed it or missed it in the documentation.
Didn't realize you were on SQL 2k, I just assume 2008+ now. I'm not sure optimize for ad hoc workloads was available in 2000 either and I don't have a 2000 box to look at. You could do:
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE;
GO
Exec sp_configure
GO
EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE;
and look to see if it is returned.
The difference in your row estimates explains the difference in query plans. 100K vs 3 definitely changes the way the optimizer will think about the query. 3 rows definitely would be a seek, lookup, and nested loop joins while 100K would be scan and probably a merge or hash join on the first join.
Can you post the execution plans?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question