MSFT - Maddog: Two users running same query get different query plans
I've got a strange situation here where two different users are running the exact same query (down to spacing etc.) and they're getting different query plans. The one plan finishes in a few seconds, whereas the other plan times out after 5 minutes. I've verified that the queries are identical, and noticed through SQL Profiler that the plans are only different in the compiled parameters. We've got forced parameterization turned on, so it seems that the 'good' plan was compiled for the same parameters that were used, whereas the 'bad' plan was compiled for different ones - but then SQL uses the 'good' parameters against he 'bad' plan, and we get timeouts.
The really strange thing is there's a set of users who seem to always get the 'good' plan, and a set of users who always get the 'bad' one. I've identified at least 3 users in both sets. I'm attempting to setup a repro environment on this.
I'm going to try to attach all the relevant data.
Note that we're running CU1;
Microsoft SQL Server 2008 (RTM) - 10.0.1763.0 (X64) Sep 18 2008 20:59:12 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)
Upvotes: 1<=-=Nov 21 2008 10:34AM=-=>
For this specific query I’d recommend that you put OPTION on it because it runs for a long time, probably a lot longer than it takes to compile. Use a plan guide to do this if you can’t change the application. You are suffering from the well known “parameter sniffing” problem and we are working to fix this in the next release. I may put one of our people in touch with you to understand your requirements better.
Eric<=-=Nov 21 2008 11:36AM=-=>
A couple of questions -. Are the different plans active (i.e. cached) at the same time? . Are the different users connecting from the same application?
One possible explanation is SET options which affect plan choice (in as much as they affect semantics). Certain SET options are part of the cache key so different options lead to different plans being cached for the same query.
Campbell Fraser, SQL Development
A couple of questions -
. Are the different plans active (i.e. cached) at the same time?
. Are the different users connecting from the same application?
One possible explanation is SET options which affect plan choice (in as much as they affect semantics). Certain SET options are part of the cache key so different options lead to different plans being cached for the same query.<=-=Jan 14 2009 11:04AM=-=>
Yes, both plans were cached at the same time, and yes, the users were connecting through the same application. The application in question doesn’t allow users to configure their SET options, so I don’t believe those played a role.
Unfortuantely, the user who was exhibiting this behaviour (which we were able to repro on a few occasions) has since left the company, and we haven’t found other users who could repro the problem.<=-=Nov 23 2009 6:22AM=-=>
One thing to check is if the query specifies schema.tablename. If it only specifies tablename then if the two users are authenticating to sql with two different usernames there is a chance that SQL will not reuse the cached plan. SELECT * FROM dbo.tablename vs SELECT * FROM tablename. This is due to the fact that the users can have different default schemas and the executing username is part of the cache key.
As for the difference in the time to execute the only idea I have is far fetched and it is that there might be a table that have the ex employees username’s schema that has more data than the others? Hope this helps!