cost of different collations on server and DB???

[sander7]

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.

[David_Singleton]

…bump…

I am also interested to know this.

[ara3n]

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.

[David_Singleton]

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.

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

[ara3n]

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

[nunomaia]

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.

[sander7]

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?

[nunomaia]

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

[David_Singleton]

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

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.

[David_Singleton]

Actually this probably deserves a new thread.

[nunomaia]

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

For SQL Server Help.

The four API server cursor types supported by SQL Server are:

Static cursors
Dynamic cursors
Forward-only cursors
Keyset-driven cursors

Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in tempdb. Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb. Keyset-driven cursors lie in between, detecting most changes but at less expense than dynamic cursors.

[nunomaia]

Actually this probably deserves a new thread.

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