|
|
Posted by Larry Bertolini on 11/21/06 14:56
While trying to help a developer troubleshoot a performance
problem, I have discovered something that strikes me as odd.
When I run a particular query, using a non-privileged login that has
necessary access to the objects, the query runs in 3.5 hours.
When I run the same query, but use a login that has sysadmin
role, the query runs in 1 second.
I compared the showplan output, and the "regular" user's execution plan
is significantly different from the "sysadmin" user's execution plan
My questions are:
1.) Is the optimizer supposed to be sensitive to the login's privileges
when choosing an execution plan?
2.) Is it possible that a non-privileged login could have access to
the tables, but not access to indexes or statistics, that would
enable the optimizer to choose a better plan?
Background:
SQL Server 2005
The "problem" query is a join, of a table in tempdb (defined as
tempdb.[username].tablename, not #tablename) with a view. The
view is a complex join of several other views, each of which
is defined as a select from a table in a database on a linked server.
(Yes, this might not sound optimal, but the query *does* run in 1 second
when run by a sysadmin.)
Navigation:
[Reply to this message]
|