August 7, 2013 at 5:00 am
Hi all,
We've just 'upgraded' one of our 3rd party apps which required a new version of the vendors database (Using SQL 2008 R2 SP1 Standard Edition running on Windows Server 2008 R2 Enterprise edition).
Upon familiarising myself with the new database (no they didn't bring me in on the initial testing and implementation!) I discovered that the collation is set to 'Latin1_General_100_CI_AI' which is different to the instances TempDB collation of 'SQL_Latin1_General_CP1_CI_AS'.
The app is running into performance problems (speed issues mostly when logging in and with certain transactions), my question is two-fold:
1. Could this be related to the difference in collations if the app uses the TempDB?
2. Should I recommend that the collation on the app db be amended by the vendor to reflect that of TempDB?
Thanks for any suggestions.
M
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
August 8, 2013 at 1:49 am
Bump
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
August 8, 2013 at 6:39 am
Hi Mark
This article has some useful information on collations. In their recommendations at the end, it says:
If you are considering a SQL collation based only on the performance characteristics of a SQL collation, realize that the performance of most applications does not benefit significantly from a change in collation. Make sure that you have isolated queries that show a benefit from a SQL collation.
Whether or not the performance is affected by the difference in collation is likely dependent on how tempdb is used. The biggest impact of different collations is when for example tables are joined on columns with different collations and the collation is explicitly stated in the "ON" clause. This makes the query non-SARG-able but is necessary to avoid the "collation conflict" errors. If tables are created in tempdb using its collation and then used in joins to the database with the COLLATE expression then that could create performance issues.
Anyway, the first thing to do is to determine what exactly is not performing. You can use SQL Trace / Profiler to capture performance data and look for slow-running queries. It should then be possible to determine if collation has anything to do with it.
Duncan
August 8, 2013 at 6:51 am
Duncan Pryde (8/8/2013)
Hi MarkThis article has some useful information on collations. In their recommendations at the end, it says:
If you are considering a SQL collation based only on the performance characteristics of a SQL collation, realize that the performance of most applications does not benefit significantly from a change in collation. Make sure that you have isolated queries that show a benefit from a SQL collation.
Whether or not the performance is affected by the difference in collation is likely dependent on how tempdb is used. The biggest impact of different collations is when for example tables are joined on columns with different collations and the collation is explicitly stated in the "ON" clause. This makes the query non-SARG-able but is necessary to avoid the "collation conflict" errors. If tables are created in tempdb using its collation and then used in joins to the database with the COLLATE expression then that could create performance issues.
Anyway, the first thing to do is to determine what exactly is not performing. You can use SQL Trace / Profiler to capture performance data and look for slow-running queries. It should then be possible to determine if collation has anything to do with it.
Duncan
Thanks Duncan, really appreciate your response and suggestions. I've been using SQL trace and profiler to capture various performance counters and have been monitoring the most expensive/long running queries on the DB as well so I'll be having a good look into it next week when I've got some useful data to compare.
Thanks again.
M
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
August 8, 2013 at 6:59 am
WWDMark (8/8/2013)
Thanks Duncan, really appreciate your response and suggestions. I've been using SQL trace and profiler to capture various performance counters and have been monitoring the most expensive/long running queries on the DB as well so I'll be having a good look into it next week when I've got some useful data to compare.Thanks again.
M
No problem, hope you get on ok.
Duncan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply