June 2, 2018 at 9:56 am
Do they do the queries through SSMS? if so most likely it is the session settings they use.
check the different users on tools-->options-->Query Execution-->ANSI
If through another tool it again may be the same issue - but how to fix depends on the tool.
June 3, 2018 at 7:55 am
aistela - Saturday, June 2, 2018 8:27 AMHello,
I have the same problem. In our company about 20 users every day runs the query (select * from MyQuery). Query has complex where conditions. For all users it takes about 10 sec, but for one of them it takes 25 min. And it happening the same on different computers and only for one user.
I tried a lot of things:
1. I have explored execution plans - they are identical
2. I have recreated user on SQL server
3. I have recreated that user in Active Directory and then on SQL server
4. I have rewritten that query and got better performance. For all users it works perfect except that one user.
Nothing helps.
I created new user in the same way as i did it recreating then problematical user. For new user this query gets result in 10 sec.
It seems like SQL server somewhere saved some information about something for that user name and always uses it... I have no idea, what kind of information and where it did it.
Maybe you have some ideas?
I can't explain a thing about it but I have seen where dropping the user/login and rebuilding it sometimes works for problems like this.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2018 at 10:56 am
frederico_fonseca - Saturday, June 2, 2018 9:56 AMDo they do the queries through SSMS? if so most likely it is the session settings they use.
check the different users on tools-->options-->Query Execution-->ANSIIf through another tool it again may be the same issue - but how to fix depends on the tool.
Usually they do that through ODBC connection, but for that exact user I've tried and through SSMS on my computer.
Always problem only for one user.
June 4, 2018 at 7:44 am
aistela - Saturday, June 2, 2018 8:27 AMHello,
I have the same problem. In our company about 20 users every day runs the query (select * from MyQuery). Query has complex where conditions. For all users it takes about 10 sec, but for one of them it takes 25 min. And it happening the same on different computers and only for one user.
I tried a lot of things:
1. I have explored execution plans - they are identical
2. I have recreated user on SQL server
3. I have recreated that user in Active Directory and then on SQL server
4. I have rewritten that query and got better performance. For all users it works perfect except that one user.
Nothing helps.
I created new user in the same way as i did it recreating then problematical user. For new user this query gets result in 10 sec.
It seems like SQL server somewhere saved some information about something for that user name and always uses it... I have no idea, what kind of information and where it did it.
Maybe you have some ideas?
That's really weird. Are you sure the plans are 100% identical? How did you capture them for each? Actual plans or estimated?
Other than that, I'd be focused on their machine to see if there are odd connection settings or something along those lines. Maybe even refresh their IP address to see if it's going through a bad route or switch or something. Just guessing.
"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
June 4, 2018 at 7:55 am
Perhaps it's a default language thing. Suppose you have a line in your query WHERE DateCreated > '01-12-2017', this could be interpreted differently depending on whether a user's default language is, for example, English or British English.
Edit - although if, as you say, the execution plans are identical, that may not be it. Are you able to post the query, please? Does it involve any catalog views (sys.objects, for example)?
John
June 4, 2018 at 11:18 am
Grant Fritchey - Monday, June 4, 2018 7:44 AMaistela - Saturday, June 2, 2018 8:27 AMHello,
I have the same problem. In our company about 20 users every day runs the query (select * from MyQuery). Query has complex where conditions. For all users it takes about 10 sec, but for one of them it takes 25 min. And it happening the same on different computers and only for one user.
I tried a lot of things:
1. I have explored execution plans - they are identical
2. I have recreated user on SQL server
3. I have recreated that user in Active Directory and then on SQL server
4. I have rewritten that query and got better performance. For all users it works perfect except that one user.
Nothing helps.
I created new user in the same way as i did it recreating then problematical user. For new user this query gets result in 10 sec.
It seems like SQL server somewhere saved some information about something for that user name and always uses it... I have no idea, what kind of information and where it did it.
Maybe you have some ideas?That's really weird. Are you sure the plans are 100% identical? How did you capture them for each? Actual plans or estimated?
Other than that, I'd be focused on their machine to see if there are odd connection settings or something along those lines. Maybe even refresh their IP address to see if it's going through a bad route or switch or something. Just guessing.
I have compared and estimated and Actual plans using WinMerge application. I think they are identical. I attached both plans.
June 4, 2018 at 11:23 am
John Mitchell-245523 - Monday, June 4, 2018 7:55 AMPerhaps it's a default language thing. Suppose you have a line in your query WHERE DateCreated > '01-12-2017', this could be interpreted differently depending on whether a user's default language is, for example, English or British English.Edit - although if, as you say, the execution plans are identical, that may not be it. Are you able to post the query, please? Does it involve any catalog views (sys.objects, for example)?
John
I attached query. There are not involved any sysobjects... And there are no comparing columns with date format.
June 4, 2018 at 12:14 pm
ahhhhhh... all queries are based on which user runs it.
uzsakymai.dbo.L0_BP where L0_vart = suser_name()
the above style is all over the place - so first thing to look at is the volume of records on each table that are associated with the slow user. Volumes may be significantly different from the other users and that could make up for the difference in performance.
and "NOLOCK" --- should be prohibited!!!! Look at the possibility of using Read Committed Snapshot (RCSI) - may not be possible and may require lots of testing.
June 4, 2018 at 12:40 pm
frederico_fonseca - Monday, June 4, 2018 12:14 PMahhhhhh... all queries are based on which user runs it.
uzsakymai.dbo.L0_BP where L0_vart = suser_name()
the above style is all over the place - so first thing to look at is the volume of records on each table that are associated with the slow user. Volumes may be significantly different from the other users and that could make up for the difference in performance.and "NOLOCK" --- should be prohibited!!!! Look at the possibility of using Read Committed Snapshot (RCSI) - may not be possible and may require lots of testing.
No. No. No. I tested with the same criteria on different users. Actually users can choose of several values. And for one user it works very slow if he does not select any filter conditions.(when only those statements are checked like "where (select isnull(L0_lokacijos_idokid,0) from uzsakymai.dbo.L0_BP (nolock) where L0_vart = suser_name()) = 0")
Ok. I'll check about Read Committed Snapshot .
June 4, 2018 at 12:49 pm
There are tiny differences in the estimates, but the plan hash and the query hash are identical. To all intents & purposes, they're the same plans. I saw some implicit conversions taking place on some of the filtering. Might not help at all, but they're in there.
Yeah, I'm not seeing anything jumping out at me. Sorry.
"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
June 4, 2018 at 1:44 pm
Apologies for my previous post - Missed the fact that the plans were attached.
Agree with Grant - some implicit conversions around suser_name (all but one) but nothing jumping out.
June 4, 2018 at 2:00 pm
Could it be the Query Governor?
June 4, 2018 at 8:35 pm
Tom Bakerman - Wednesday, September 4, 2013 1:14 PMHey Gurus,I have a query that performs differently depending upon the user executing it. This was first brought to my attention because an Excel spreadsheet was taking a long time to load data (Excel 2010, Sql Server 2008 R2). The query was awful, I rewrote it and got better performance. But when the user tried running it from Excel, still had bad performance. After a bit of head scratching I saw that the connection from Excel had a username/password specified, whereas when I ran it from SSMS I was logging in using my username via windows integrated security (and I am dbo). So, I tried a few things:1. In two windows in the same SSMS, run the query simultaneously. One as me, the other using "Execute as Login =".2. Start up two instances of SSMS. Connect in one as me, in the other as the specified login.In any case, when I run the query as me, it takes about 7 1/2 minutes. When I run as the specified user, it takes 12+ minutes (today's measurements. Other days when I run as me I can have it run in as little as 2 minutes, but these times have been consistent today).In any case, I am always executing the exact same statement (SELECT * FROM a table valued function) with the same parameters. The only difference is "who" is logged in.Why would two different user logins have different performance profiles? What should I be looking at to track this down?Thanks in advance.Tom
Tom, I can only think of this behavior may be due to the user group or individual subset of users added to windows "lock pages in memory" property.
@JayMunnangi
June 11, 2018 at 7:44 pm
Can you modify the query to use two part naming standard [SchemaName].[ObjectName] and see if that helps?
June 12, 2018 at 11:04 am
shahm10 - Monday, June 11, 2018 7:44 PMCan you modify the query to use two part naming standard [SchemaName].[ObjectName] and see if that helps?
Ah... good call.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply