Hi David,

I remember comment of the previous developer regarding one of the SP (SQL Roles provider). He added NOLOCK hint and indicated that it was for performance reason. Perhaps he observed the same behavior as you do.

>Naomi,
>
>There are a lot of variables that drive query plans, what works well for a smallish lookup table isn't the same for a large transaction table. And query plans only show one very narrow view of what's going on inside the engine. You really need to look at the I/Os that happen to verify that excessive page reads are not happening.
>
>For me now though the rule is to use this form of EXISTS check
>
>

>where exists ( select 'x' from ... )
>

>
>Also FWIW I've found that SQL2005 doesn't do near as good a job as SQL2000 on some query plans. I've found that hints on some joins and where clauses have radically improved performance in 2005 where they were not needed in 2000.
>
>>See also http://www.zimbio.com/SQL/articles/589/SQL+SERVER+SELECT+1+vs+SELECT+Interesting
>>
>>Not every one agrees.
>>
>>http://forums.asp.net/t/1374663.aspx