October 27, 2016 at 5:01 am
Hello,
I have a strange performance issue.
I noticed, that if SQL Server user (SQL login) default language is Finnish, the query is not optimized.
When language is English, the performance is as expected.
I ran the same query by changing the login language, and by logging in again.
There's a query, which I have optimized by adding indexes and statistics by using Database Engine Tuning.
When I log in with the same account having English as login language, I get these IO Statistics.
Table A. Scan count 230, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table B. Scan count 46, logical reads 138, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table C. Scan count 372, logical reads 1129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table D. Scan count 4, logical reads 149, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table E. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table F. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If I change the login language for "Finnish", the IO statistics are the following.
Practically no index is used, I guess. I haven't run the SHOWPLAN yet, becuase this account does not have permission on that.
Finnish login is required for correct date conversions.
Table A. Hakukertoja 230, loogisia lukukertoja 490, fyysisiä lukukertoja 0, ennakoivia lukukertoja 0, loogisia LOB-lukukertoja 0, fyysisiä LOB-lukukertoja 0, ennakoivia LOB-lukukertoja 0.
Table B. Hakukertoja 44868, loogisia lukukertoja 89736, fyysisiä lukukertoja 0, ennakoivia lukukertoja 0, loogisia LOB-lukukertoja 0, fyysisiä LOB-lukukertoja 0, ennakoivia LOB-lukukertoja 0.
Table C. Hakukertoja 44868, loogisia lukukertoja 135004, fyysisiä lukukertoja 0, ennakoivia lukukertoja 0, loogisia LOB-lukukertoja 0, fyysisiä LOB-lukukertoja 0, ennakoivia LOB-lukukertoja 0.
Table D. Hakukertoja 229916, loogisia lukukertoja 698447, fyysisiä lukukertoja 0, ennakoivia lukukertoja 0, loogisia LOB-lukukertoja 0, fyysisiä LOB-lukukertoja 0, ennakoivia LOB-lukukertoja 0.
Table E. Hakukertoja 1, loogisia lukukertoja 136020, fyysisiä lukukertoja 0, ennakoivia lukukertoja 0, loogisia LOB-lukukertoja 0, fyysisiä LOB-lukukertoja 0, ennakoivia LOB-lukukertoja 0.
Table F. Hakukertoja 0, loogisia lukukertoja 2, fyysisiä lukukertoja 0, ennakoivia lukukertoja 0, loogisia LOB-lukukertoja 0, fyysisiä LOB-lukukertoja 0, ennakoivia LOB-lukukertoja 0.
The funniest thing is, that when I change the login language "on the fly", the old connections are still behaving with the performance based on language: Finnish->Slow, English->fast.
If create a new connection, I get the speed accordingly.
Versions (Running in developer's laptop having Windows 7)
Microsoft SQL Server Developer (64-bit)
Microsoft Windows NT 6.1 (7601)
Version 12.0.4213.0
Language: English (United States)
Memory: 7619 MB
Br,
Arto Ojala
October 27, 2016 at 5:45 am
There are some CONVERT_IMPLICIT-statements in execution plan when login is Finnish.
I guess this has got something to do by conversions, when having numbers in char or varchar-column, and using those values as if they were integer values.
This CONVERT_IMPLICIT does not happen when language is ENGLISH, and the clustered index word like a charm.
Any good ideas /articles / topics on this issue?
October 27, 2016 at 5:49 am
arto.ojala (10/27/2016)
There are some CONVERT_IMPLICIT-statements in execution plan when login is Finnish.I guess this has got something to do by conversions, when having numbers in char or varchar-column, and using those values as if they were integer values.
This CONVERT_IMPLICIT does not happen when language is ENGLISH, and the clustered index word like a charm.
Any good ideas /articles / topics on this issue?
My suspicion is that a varchar column is being implicitly converted to nvarchar, rendering an index useless for the query, can you post the query here?
😎
October 27, 2016 at 6:46 am
Thanks for you assistance, but no need at this point.
I was able to optimize it. I realized that there was an error on join, where having multiple-column foreign key-> The result was actually wrong!
After I fixed it and ran tuning advisor again, it suggested me a new index and adding some statistics.
I created them, and now it works for both logins! O_o
I would like to understand the depth of this, but unfortunately I do not have time for it now - I am just glad it worked out.
Cheers,
Arto
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply