cost of different collations on server and DB???

Dynamics 365 Business Central/NAV User Group Technical Forum
#1

Hi Guys,

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.

#2

…bump…

I am also interested to know this.

#3

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.

#4

So does this mean that if you are using different collations that TEMPDB location in the server becomes important?

#5

TempDB location is always important. On really big servers, we recommend to put the tempdb on separate drives, especially on sql 2k5.

#6

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.

#7

Hi Guys,

Thanks a lot for the input…

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”.

Would that be a fair conclusion on this issue?

#8

Yes.
I don’t even think will be a performance decrease, even if happens it will minor.

#9

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.

#10

Actually this probably deserves a new thread.

#11

Cursor are keept in tempdb.
For exanmple FINDSET(TRUE,FALSE) uses a Dynamic Cursor.

For SQL Server Help.

#12

I agree with you. David do you want to start this new thread ? [:)]