Re: tempdb on C drive?

As always the optimal configuration can vary based on individual conditions,
usage, hardware etc. But in general (most cases) you will get optimal
performance from having the tempdb data files, User db data files and Log
files all on different physical drives or drive arrays. But in reality that
isn't always practical for several reasons. One is that if you had 20 user
databases most people can't afford 20 different drives just for the log
files of each db. Second you would have to have a pretty heavy load to
justify that as well. So the rule is at the very least separate the log
files from the data files onto two separate physical drive arrays. Meaning
both user and tempdb files. If you have more drives and the load warrants it
you can separate the tempdb data files from the user data files as well onto
its own drive. This is especially true for SQL2005 where tempdb can be much
more heavily used than in 2000. The separation of data from log files does a
lot reduce contention of the heads since data access is mostly random where
as logs are mostly sequential. But to get to your question can you put more
than 1 log file (including the tempdbs) on a disk? The answer is in most
cases yes. While adding more log files to the log drive array decreases the
thruput to a degree with each active file a RAID 1 or 4 disk RAID 10 can
handle quite a bit of log type traffic with no random type access such as
data files do. Since tempdb is usually one of the most heavily active
databases in terms of transactions you want to ensure you have the log file
separated from the tempdb data or user data files by placing it on the same
drive as the other log files. If the system gets to be very intensive with
thousands or 10's of thousands of transactions per second you may need to
bump up to a 4 disk Raid 10 if you were on a Raid 1 or move some of the
files to a new Raid 1 or 10. But the average system will do fine with a Raid
1 or 10 for all the log files, a Raid 1 or 10 for tempdb and a Raid 10 for
the data files. In your case I would put tempdb data files on the C: drive
and leave all the log files on the log drive and the user data on the data
drive. The types of disk arrays for those drive may warrant swapping some of
those but the C: drive usually only has the OS and swap file (which
hopefully is seldom used) and can typically support log files or tempdb on
small to moderate systems. Let me know if that answered your questions
properly.

--
Andrew J. Kelly SQL MVP

"Andre" <nospam@xxxxxxxx> wrote in message
news:e12zRbeuHHA.3816@xxxxxxxxxxxxxxxxxxxxxxx
Andrew,

I have a similar situation, with a C drive, a Data drive and a Log drive.
My thought was to put tempdb's data and log files on the C drive, to
separate them completely from the Data and Log drives, but you're
recommending putting all the log files together (db and tempdb's). From a
performance standpoint, is this really better? I don't mean to imply you
don't know what you're doing, so please don't infer that - I have a lot of
respect for you from many posts of yours that I've read over the years. I
just want me server to be as optimally configured as possible, with as
little disk contention as possible.

Thanks, Andre

"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:OFojRoMnHHA.4428@xxxxxxxxxxxxxxxxxxxxxxx
No you only need to add 3 more files to the primary file group. Just make
sure all 4 files are the same size. You can put tempdb data files on the
C: drive but do not put them on the E: drive. Only log files should go
there. You can put the Tempdb log file on the E: drive though. If you
have HT processors it is recommended that you set MAXDOP at the server
level to no more than the # of physical procs.

--
Andrew J. Kelly SQL MVP




.

Relevant Pages

  • Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
    ... You'll not be using most of the system databases intensively so you don't need to seperate them, I mean locating them on different physical disks. ... If it's being used intensively in your environment then you should locate it's log and data files on different physical disks. ... For this question you must understand the reason why we should seperate data and log files. ... Of course these drives must be physically seperated so that you'll gain performance benefits. ...
    (microsoft.public.sqlserver.setup)
  • Re: Building New Server - What RAID should be used and how to divi
    ... different drives. ... Never use Raid 0 if you are concerned with data loss. ... Raid 10 is a better choice for data files. ... RAID 1 is a starting point for Log files. ...
    (microsoft.public.sqlserver.setup)
  • Re: Building New Server - What RAID should be used and how to divide t
    ... Creating multiple Logical drives such as C:, D: & E: on one physical array does nothing for performance but gives false impressions of files residing on different drives. ... Raid 5 should only be considered if there is mostly Read activity or very low write activity. ... Raid 10 is a better choice for data files. ... RAID 1 is a starting point for Log files. ...
    (microsoft.public.sqlserver.setup)
  • Re: Building New Server - What RAID should be used and how to divide t
    ... Creating multiple Logical drives such as C:, D: & E: on one physical array does nothing for performance but gives false impressions of files residing on different drives. ... Raid 5 should only be considered if there is mostly Read activity or very low write activity. ... Raid 10 is a better choice for data files. ... RAID 1 is a starting point for Log files. ...
    (microsoft.public.sqlserver.setup)
  • Re: tempdb on C drive?
    ... My thought was to put tempdb's data and log files on the C drive, ... separate them completely from the Data and Log drives, ... You can put tempdb data files on the ... HT processors it is recommended that you set MAXDOP at the server level to ...
    (microsoft.public.sqlserver.setup)
Loading