I might be asking a rather stupid questuion… but here goes…
Is there any noticable performance-cost on the server-side, if the collation-setting for the Database is other than the collation-setting on the server?
e.g.
Server-collation: Latin1_General_CI_AS
Database-collation: Danish_Norwegian_CS_AS
([^o)] Could this be an “enter enter enter”-installation of SQL-server on english OS, and a “Cronus Denmark”-demoDB… [:D])
Today I have read a lot of articles covering collation, how great it is that we can define an alternative collation on a database if needed, instead of having to change collation on the whole server etc. etc…
But not one (of the articles I found) covered what performance-cost it causes (if any).
In my logic there has to be some performance-cost from this.
sql server uses the temp db for a lot of internal processing and the temp db collation is different than the database, then I’m guessing there will be some processing done to translate queries using COLLATE statement.
Tempdb it’s a really an important database. Some critical operations are done in this database, even some kind of sql cursor run in tempdb.
But I don’t think a database collation configured to be different from server collocation will have a performance decrease. SQL 2005 has some optimizations in this segment, but these performance optimizations are related to mixing collations in the same database and tables.
Sounds to me like there will/might be a minor performance-cost, if tempdb have different collation than user-db, all things equal.
But the performance-cost will be negligible in relation to most other “performance-buttons”.
Thanks for this information, and can you explain it a bit. All the information I have been given says that TempDB is hardly used by Navision. Maiinly it is used to sort keys, during a restore and when adding a new key to a table. Neither of hich are importatn in a production environment.
I am interested that you say “especially on sql 2k5” is there something specific you had in mind?
And in real terms, if there was a specific limit to number of spindles, then would you say they would best be used for a dedicated TempDB or to expand the database.