July 23, 2012 at 1:17 pm
We have encountered a strange behavior in performance. A query seems to perform worse when the default language of a user is German.
User A has sysadmin rights, user B public and is owner of the database. When running the query with user A in ssms, performance is good, within 1 second returning 2000 rows. When running the query from an application as user B, it times out after 30 seconds (the threshold in the app). Capturing the query from B in profiler shows a huge amount of reads. Running the query as user B in ssms gives an error about date conversion. Part of the where clause is date comparison against a hard coded date i.e. <= '2012-07-23'.
When either user B becomes sysadmin or its default language is changed from German to English, and running the query from the app, performance is the same as for user A. The bad performance returns when the language is reset to German or the sysadmin role is revoked.
Anyone ever seen such behavior or has some hints where to look for an explanation?
I could post the query but its lengthy and at the moment it does not seem related to the query itself but more to an issue related to date format and privileges.
Bouke
July 23, 2012 at 1:28 pm
Bouke Bruinsma (7/23/2012)
We have encountered a strange behavior in performance. A query seems to perform worse when the default language of a user is German.User A has sysadmin rights, user B public and is owner of the database. When running the query with user A in ssms, performance is good, within 1 second returning 2000 rows. When running the query from an application as user B, it times out after 30 seconds (the threshold in the app). Capturing the query from B in profiler shows a huge amount of reads. Running the query as user B in ssms gives an error about date conversion. Part of the where clause is date comparison against a hard coded date i.e. <= '2012-07-23'.
When either user B becomes sysadmin or its default language is changed from German to English, and running the query from the app, performance is the same as for user A. The bad performance returns when the language is reset to German or the sysadmin role is revoked.
Anyone ever seen such behavior or has some hints where to look for an explanation?
I could post the query but its lengthy and at the moment it does not seem related to the query itself but more to an issue related to date format and privileges.
Bouke
Since you didn't post the error message regarding the Date, I can guess what it is. Just for s&g's, modify the query in SSMS so that the hard coded date is '20120723' (in other words, take out the dashes).
What happens then?
July 23, 2012 at 1:52 pm
Default date format is probably the problem. German default is DD-MM-YYYY where English is MM-DD-YYYY. If the date column isn't stored as DateTime (or one of its variants) and is instead a string, or anything like that, then you'll get all kinds of weird behavior due to implicit conversion rules and default date formats.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2012 at 2:25 pm
The date format I can understand but why would granting sysadmin without changing the language make the query perform better? Is there a date format for the sysadmin role that overrules the user's?
July 25, 2012 at 7:33 am
If you check sys.server_principals, you'll see a "default_language_name" column in there. Changing logins changes some defaults based on that, if I'm not mistaken.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply