same server , different login different query plan
I have a SQL query and it is using different query plans with different login.
Both the login have sa roles and other previliges. one login is "sa" and other login "application login" but has role too.
Both are binded to default tempdb but showing different query plan for same query.
How can i correct it, and ensure they use same query plan.
Both the login have sa roles and other previliges. one login is "sa" and other login "application login" but has role too.
Both are binded to default tempdb but showing different query plan for same query.
How can i correct it, and ensure they use same query plan.
Pavel Celba
If you show the query then we could help possibly. E.g. on SQL Server different query plans are generated when you omit table schema in the query. But I cannot say how it works for Sybase...
That is most likely due to "sa" having more elevated permissions than the "application login" which means metadata access is deeper in "sa" case - you could use forceplan as described here http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00743.1502/html/queryprocessing/queryprocessing44.htm but check other options too.
Is statement cache enabled in this ASE? Cached query plans are not shared or re-used between different logins unless a traceflag is used.
Is the query being run directly or through a stored procedure? There could be reuse issues with query plans there too although procedure cache can normally be re-used between different users.
Does the SQL involve local variables?
Are both logins connecting the same way? i.e. are both connecting with the Sybase client, or is the application using ODBC? Does the application connect from a middle tier with a connection pool?
Does either login have login triggers? Do they connect with exactly the same connection properties? It's possible in both places to specify session settings that affect optimizer decisions.
For a definitive answer we really need to see the SQL and the two different query plans.
With the greatest of respect to my learned colleague above, forceplan/forceindex are absolutely terrible ideas. Every decision the ASE optimizer makes is for a reason - it might be the wrong reason, but it isn't happening at random. There is a reason for this difference in behaviour. Let's find it and fix it.
Is the query being run directly or through a stored procedure? There could be reuse issues with query plans there too although procedure cache can normally be re-used between different users.
Does the SQL involve local variables?
Are both logins connecting the same way? i.e. are both connecting with the Sybase client, or is the application using ODBC? Does the application connect from a middle tier with a connection pool?
Does either login have login triggers? Do they connect with exactly the same connection properties? It's possible in both places to specify session settings that affect optimizer decisions.
For a definitive answer we really need to see the SQL and the two different query plans.
With the greatest of respect to my learned colleague above, forceplan/forceindex are absolutely terrible ideas. Every decision the ASE optimizer makes is for a reason - it might be the wrong reason, but it isn't happening at random. There is a reason for this difference in behaviour. Let's find it and fix it.
ASKER
Thanks Joe for your valuable response. I will try to answer as much as possible :
1. One login is "sa" and other is application login. both have same global login trigger for audit.
2. Its a stored procedure. Inside the stored there are SQL;s with views and tables being used in those sql's.
3. statement cache is enabled. Just to check how to verify this ? i checked through sp_configure 'statement cache size' and the run value is 10240 so it means it is enabled.
4. how to check this ? -- > there could be reuse issues with query plans there too although procedure cache can normally be re-used between different users.
5. yes both logins are connecting same, i used "isql" for both.
1. One login is "sa" and other is application login. both have same global login trigger for audit.
2. Its a stored procedure. Inside the stored there are SQL;s with views and tables being used in those sql's.
3. statement cache is enabled. Just to check how to verify this ? i checked through sp_configure 'statement cache size' and the run value is 10240 so it means it is enabled.
4. how to check this ? -- > there could be reuse issues with query plans there too although procedure cache can normally be re-used between different users.
5. yes both logins are connecting same, i used "isql" for both.
ASKER
i cannot share the query and the details but will try to share the plan.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.