aaron.reese (12/11/2013)
Not sure what it will do to the cached execution plans though if the statistics for the different schemas are skewed 🙂
Different execution plans for different users, because the optimiser can tell that the plan is not safe for reuse between users. So if you have 200 database users, you get 200 plans in cache.
This is why it is recommended to use 2-part naming in procedures, to clarify which tables are used so that the optimiser doesn't include the user as part of the cache lookup key.
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