Kayaking and tempdb file placement

So, no posts for 10 weeks, but that's because sometimes there are other things going on in Stockholm as opposed to SQL Server support :-) Here's what I've been doing over the summer....

 

I've been fortunate enough to have several weeks paternity leave off to spend with the family and enjoy the Swedish summer. But I'm back to the technical stuff this week and of course to the release of SQL 2008.

This week I've been working on some proactive advisory cases with Swedish customers, reviewing their installations for best practices and providing some tips and tricks. We do this quite frequently for Premier customers and it's good for all involved. Customers get some of their servers checked out in advance of any potential technical problems, and I get to stop things happening in advance, as opposed to my normal "fix it urgently" role :-)

I looked back on my past few reports of this nature today, and I thought I'd just mention the one point that has come up in every review that I've done this year. This may just be co-incidence but maybe word isn't spread as well as it could be.

In SQL 2005 there were significant numbers of enhancement to the implementation of tempdb, both from an implementation and a supportability standpoint. We now recommend that by default you create one tempdb data file per visible scheduler within the SQLOS, or in other words (for most people) one per logical processor core that you expose or affinitize to SQL Server. Then test and benchmark the results and do some fine tuning if you need be.

Remember, you can look at the schedulers that have been created by looking in the following DMV

select

*

from

sys.dm_os_schedulers

Here are the 2 main articles that I always point customers to on this tempdb issue:

https://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

https://msdn.microsoft.com/en-us/library/ms175527(SQL.90).aspx

I'll be back in coming weeks with "proper troubleshooting".