none
Tempdb data and log files on same drive RRS feed

  • Question

  • Good morning,

    A quick question.

    In a nutshell here is how my files are stored on the SAN drives.

    E:\ Stores the database data files.
    F:\ Stores the database log files.
    G:\ Stores both the tempdb data files and log files.

    I've read a lot about splitting up data and log files on to different drives. In my drive layout should I put the tempdb log file on drive F:\ with the other log files or should i request another drive to solely store the tempdb log file?

    Many thanks,

    Ben 

    P.S I hope i have posted this in the correct forum.


    Mr Shaw

    Wednesday, February 25, 2015 12:12 PM

Answers

  • Good Call...

    Sounds like it is going to be heavily utilised.  Set up an extended data file for each CPU; and host the NDF's on a separate drive.  If you can get away with it ask for additional drives so that each drive is hosting no more than for NDF files.

    Are you backing up your databases on this Server?  You will need another drive setting up for your backups if you are.


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Wednesday, February 25, 2015 12:39 PM
    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 1:07 PM
    Wednesday, February 25, 2015 12:37 PM
  • sorry, you are talking about tempdb. I thought you meant a separate drive for each regular DB!

    My server has only 2 cpu...though it does look like in total there are 24 cores.


    Mr Shaw

    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 1:07 PM
    Wednesday, February 25, 2015 12:57 PM
  • Hello All

    It is true that the advent of Disk Arrays and SAN's has reduced the performance issues of single devices and RAID systems of old.

    However it is still preferable to have your database files spread across different volumes, this is mainly due to management and to spread your risk.  There are dozens of scenarios for this which I'm not going to document here but it is still best practice to spread the load.

    I have also noted that someone has noted that there is a chance that a data file may be damaged if a transaction fails due to tempdb being full.  This one scenario alone is enough justification to introduce separate volumes for TempDb; especially as it is intended that this  Server is to be used for Reporting and Data Warehousing.


    Please click "Mark As Answer" if my post helped. Tony C.

    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 2:14 PM
    Wednesday, February 25, 2015 2:11 PM

All replies

  • It all depends on how large G:\ is and how heavily utilised SQL Server is.  But I've always had the approach of having at least two separate drives for Tempdb on a Production Platform and have the mdf on one of the drives and at least two extended files (ndf) on the second drive.  This Approach allows for a spread of the load for tempdb and prevents tempdb from filling up other drives where there is other content.

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, February 25, 2015 12:25 PM
  • Thanks,

    The SQL Server which I am working on will be used to consolidate database for lots of other servers. This includes reporting databases, data warehouse database and staging databases. From what you have said it sounds like I should push for another drive just to host the Tempdb log file.



    Mr Shaw

    Wednesday, February 25, 2015 12:31 PM
  • Good Call...

    Sounds like it is going to be heavily utilised.  Set up an extended data file for each CPU; and host the NDF's on a separate drive.  If you can get away with it ask for additional drives so that each drive is hosting no more than for NDF files.

    Are you backing up your databases on this Server?  You will need another drive setting up for your backups if you are.


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Wednesday, February 25, 2015 12:39 PM
    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 1:07 PM
    Wednesday, February 25, 2015 12:37 PM
  • If you can get away with it ask for additional drives so that each drive is hosting no more than four NDF files.

    Having no more than 4 NDF files per drive would require a lot of drive. Do you mean, not having more than four data files per data base?

    P.S backups are stored on a separate server.

    Thanks,

    Ben 


    Mr Shaw

    Wednesday, February 25, 2015 12:44 PM
  • Ok, push for two additional drives.  If your Server has eight CPU's then host the MDF, LDF and three NDF files on one Drive and the other five on the Other Drive.

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, February 25, 2015 12:52 PM
  • sorry, you are talking about tempdb. I thought you meant a separate drive for each regular DB!

    My server has only 2 cpu...though it does look like in total there are 24 cores.


    Mr Shaw

    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 1:07 PM
    Wednesday, February 25, 2015 12:57 PM
  • No problems...

    You should get away with the one drive then to host tempdb with an extra extended data file.


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, February 25, 2015 1:01 PM
  • In a nutshell here is how my files are stored on the SAN drives.

    Hello Ben,

    In common you don't have any control on which "spindle" (physical disk) of a SAN your files will be stored, so this far it doesn't matter how many drives you are using and where you locate your database files; even if you have several drive the files could be on the same or a different spindle.

    The only Advantage of different drives is that e.g. the log files / TempDB growths fast and fill up the drive, it won't damage you more important database files


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 25, 2015 1:06 PM
    Moderator
  • Well, now  you need to monitor to see if there are any performance problems. How many data files  does the tempdb have? What does the return the below statement

     Select session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    resource_description,
          ResourceType = Case
    When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
                When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
                When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
                Else 'Is Not PFS, GAM, or SGAM page' 
                End
    From sys.dm_os_waiting_tasks
    Where wait_type Like 'PAGE%LATCH_%'
    And resource_description Like '2:%'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, February 25, 2015 1:09 PM
    Answerer
  • The only Advantage of different drives is that e.g. the log files / TempDB growths fast and fill up the drive, it won't damage you more important database files

    What do you mean by damage?

    Mr Shaw

    Wednesday, February 25, 2015 1:15 PM
  • There is only one data file for the tempdb.

    At the moment the server is still being built and there is no activity to record. 


    Mr Shaw

    Wednesday, February 25, 2015 1:17 PM
  • What do you mean by damage?
    In worst case if the disk is full then the next write to the data file could damage the file.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 25, 2015 1:32 PM
    Moderator
  • What do you mean by damage?

    In worst case if the disk is full then the next write to the data file could damage the file.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    So when using a SAN all the theory of splitting log and data files onto separate drive gone out the window :)

    Mr Shaw

    Wednesday, February 25, 2015 2:03 PM
  • Hello All

    It is true that the advent of Disk Arrays and SAN's has reduced the performance issues of single devices and RAID systems of old.

    However it is still preferable to have your database files spread across different volumes, this is mainly due to management and to spread your risk.  There are dozens of scenarios for this which I'm not going to document here but it is still best practice to spread the load.

    I have also noted that someone has noted that there is a chance that a data file may be damaged if a transaction fails due to tempdb being full.  This one scenario alone is enough justification to introduce separate volumes for TempDb; especially as it is intended that this  Server is to be used for Reporting and Data Warehousing.


    Please click "Mark As Answer" if my post helped. Tony C.

    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 2:14 PM
    Wednesday, February 25, 2015 2:11 PM
  • Thanks everybody.

    You answers were 5 star :)


    Mr Shaw

    Wednesday, February 25, 2015 2:15 PM