SQLServerCentral Best Practices Clinic: Part 3

Question

SQLServerCentral Best Practices Clinic: Part 3

Brad McGehee

SSCertifiable

Points: 5282
More actions
May 11, 2011 at 12:01 am

#240076

Comments posted to this topic are about the item SQLServerCentral Best Practices Clinic: Part 3
Brad M. McGehee
DBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tgarland Hall of Fame Points: 3367 More actions · Answer 1

Is there any possibility with having different collations across the databases that you could run into issues when running queries that cross databases especially involving joins? What about any issues with TempDB if the collations are different? Thanks.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

Brad, re: the 8 TempDB files, I have no direct info here, but yesterday's SSC newsletter had a link to a blog by Eric Stephani (http://ericstephani.com/?p=166) where he was recapping the SQL Skills immersion training, and he mentions multiple data files with a "sweet spot" around 8.

I'm sure Paul/Kim could provide much more info about that.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Brad McGehee SSCertifiable Points: 5282 More actions · Answer 3

tgarland (5/11/2011)
Is there any possibility with having different collations across the databases that you could run into issues when running queries that cross databases especially involving joins? What about any issues with TempDB if the collations are different? Thanks.

Good point. As far as I can tell, there are no cross database queries running, although I will ask the developer about this and find out.

Brad M. McGehee
DBA

Brad McGehee SSCertifiable Points: 5282 More actions · Answer 4

jcrawf02 (5/11/2011)
Brad, re: the 8 TempDB files, I have no direct info here, but yesterday's SSC newsletter had a link to a blog by Eric Stephani (http://ericstephani.com/?p=166) where he was recapping the SQL Skills immersion training, and he mentions multiple data files with a "sweet spot" around 8.
I'm sure Paul/Kim could provide much more info about that.

I also attended the same training from Paul and Kim. Generally speaking, they are recommending using a number of files equal to 1/4 to 1/2 of the number of cores, with 8 being the maximum. I will follow this article with a single article discussing this exact topic.

Brad M. McGehee
DBA

Steve Jones - SSC Editor SSC Guru Points: 734416 More actions · Answer 5

tgarland (5/11/2011)
Is there any possibility with having different collations across the databases that you could run into issues when running queries that cross databases especially involving joins? What about any issues with TempDB if the collations are different? Thanks.

I ran into this recently when I created a table with database defaults to test something, joined with an existing table, and had collation issues. Very annoying, but it's something that we have documented and have to deal with.