May 5, 2009 at 12:22 pm
I have a query that is run against the same db’s but by two different users. One user (me) is the database owner. The other user has select permissions on the required tables. When I run the execution plans, for the same query hitting the same machines I get totally different results. What’s worse, the dbo connection takes about a minute to complete while the ‘user’ connection takes almost 30 minutes to complete. Ideas?
Thanks
May 5, 2009 at 12:25 pm
When you say you get different results do you mean execution time or actual data? If different data, are the results consistent within users?
Are you schema qualifying the objects being accessed (schema.tablename)?
Have you compared 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
May 5, 2009 at 12:27 pm
The query yields the exact same data results. However the execution plans are different and the time it takes for each user is drastically different.
May 5, 2009 at 12:41 pm
and yes, I am fully qualifying the name because I'm using a linked server.
May 5, 2009 at 1:18 pm
What are you doing across the linked server?
Edit: I had left "you" out originally.
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
May 5, 2009 at 1:21 pm
Ah, I didn't know about the linked server before. Not that it gives me an idea what the problem is, but it does toss another wrinkle into the issue.
For those wondering why I'm talking even more strangely than normal, this question actually started on my blog (http://scarydba.wordpress.com/2009/04/24/unpacking-the-view/[/url])
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2009 at 1:26 pm
Ahhh, now I don't feel too bad for not having a better answer. At least I asked some different questions.
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
May 5, 2009 at 1:36 pm
In our environment we have a server of purely static market data. User's will 'pull' from this static data into their own databases on the 'user' server. This way they can do whatever they want to their copy of the data. The added benefit of this is that the static data only gets refreshed a couple of times a year and statistics are computed accordingly.
As was said earlier, I don't believe this is the problem. I believe the problem to be somewhere in security. The fast running query is run by a dbo while the slow query is run by a user who is in the public role with read priveleges to the static data.
May 5, 2009 at 2:09 pm
As a test, I granted my user dbo permissions on the databases in question and the execution plan matches the 'fast' dbo execution plan.
However, I am reluctant to grant this class of users dbo permissions.
May 5, 2009 at 2:15 pm
Interesting. What happens if you just eliminate the linked server part of the query?
What is the default schema of the user?
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
May 5, 2009 at 2:16 pm
I know this is unlikely, but is there an indexed view or some other db object that the dbo is able to make use of in the execution plan and the read only user is not???
May 5, 2009 at 3:33 pm
Yeah, I was wondering the same thing. Can you post the two execution plans, the good one and the bad one?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2009 at 10:48 am
please see attached excel file
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply