locked
Setting up Tempdb files in filegroup for Data Warehouse RRS feed

  • Question

  • Hi All,
    I have a question about setting up tempdb for data warehousing on sql server standard edition.

    We support a DW that has been in production for 5 years now. It is 6 TB with the largest table 3B rows and a few other fact tables in the 500M row range.

    In the last few days we are experiencing a so far unexplained slowdown that has been significant. With no statement changes run times are up 10-20% quite frequently.

    In watching how the machine is performing I can see tempdb files are very hot as is expected.

    What we did with tempdb and the log is to place them both on to one large drive. More importantly we created 8 files in the tempdb file group and placed them all on to the one drive knowing that sql server would round robin the writing of the data to the files. We do this with all the other database files in file groups for large tables only we round robin them to different drives so sql server writes to different drives as it writes out for the large fact tables.

    I am beginning to suspect that this round robin writing of data to the tempdb files is creating excessive head movement on the underlying drives. (the underlying drive is actually a set of 4 drives made to look like one virtual drive).

    Contention for head movement on the underlying drives for the tempdb and log files would explain the rather sudden increase in run times. 

    What I am thinking is that if we undo the practice of having tempdb broken up over 8 files on one "virtual drive" to being just one large file and allow SQL Server to use it as one large file this would actually perform better for the large and complex batch stream we have.

    However, we don't really have a similar production machine to test this out on so we would have to test this on a weekend.

    Before we go ahead with such an effort and do the testing I was wondering if anyone here has experience and an opinion on changing a tempdb database from being split up in 8 files to being just one very large file on one drive.

    Is SQL Server going to do a better job on tempdb if it is just one large file as opposed to 8 split files?

    All thoughts on this would be most welcome.

    Best Regards 

    Peter 


    Thursday, July 27, 2017 1:43 AM

Answers

  • Hi Peter,

    >>Is SQL Server going to do a better job on tempdb if it is just one large file as opposed to 8 split files?

    Unlikely, that would lead you to PFS contention as described in this blog. In this case, I’d suggest you start by following the instruction here to see if you need to add more tempdb file or not. Furthermore, if you the head movement(physical limitation) is the real issue here, please try to spread temped files on different sets of physical disk and test again.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 28, 2017 3:11 AM

All replies

  • Hi Peter,

    >>Is SQL Server going to do a better job on tempdb if it is just one large file as opposed to 8 split files?

    Unlikely, that would lead you to PFS contention as described in this blog. In this case, I’d suggest you start by following the instruction here to see if you need to add more tempdb file or not. Furthermore, if you the head movement(physical limitation) is the real issue here, please try to spread temped files on different sets of physical disk and test again.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 28, 2017 3:11 AM
  • Hi Peter,

    It is better if you can analyse the performance of the whole system as a whole to investige the issue. 

    If the tempdb files are configured as per the best practice recommendations then you can ignore it. 

    https://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/

    Regards,

    kccrga


    -kccrga http://dbatrend.blogspot.com.au/

    Friday, July 28, 2017 5:10 AM
  • Hi Lin,

    "The best guidance I’ve seen is from a great friend of mine, Bob Ward, who’s the top Escalation Engineer in Microsoft SQL Product Support. Figure out the number of logical processor cores you have (e.g. two CPUS, with 4 physical cores each, plus hyperthreading enabled = 2 (cpus) x 4 (cores) x 2 (hyperthreading) = 16 logical cores. Then if you have less than 8 logical cores, create the same number of data files as logical cores. If you have more than 8 logical cores, create 8 data files and then add more in chunks of 4 if you still see PFS contention. Make sure all the tempdb data files are the same size too. (This advice is now official Microsoft guidance in xxxxx)"

    I saw this recommendation when I set up the server before migrating our data warehouse to it. I created 8xfiles in the tempdb all 32GB. We have 7x 1.2TB disks and we put data on 6 of the drives and log/tempdb on 7th drive. Obviously that 7th drive is hot. We are seeing normal wait times be 10ms but spikes up to 1,000 ms. That is in performance manager on the win2012R2 server.

    This is sql server 2014 SE on a 12 core single CPU VM with 164GB memory total and 132GB allocated to SQL Server.

    Performance has been excellent until this very sudden drop off.

    I will create a new file in tempdb on each of the other 6 drives all 32GB and spread the tempdb load over them and test that on the weekend when no one else is using the machine.

    We do have news on the drop off in performance which you and others at MSFT may be interested in.

    We rebooted the Win2012R2 server before last nights ETL run and the ETL ran in normal times....within 10 minutes of the standard run time over a 6 hour ETL batch window which is perfect as far as I am concerned.

    So it would appear that this issue was a memory fragmentation problem of some sort where the reboot cleared the memory and sql server could use it all properly again.

    The thing is we used to see this on very heavily loaded win2003/sql server 2005 systems. After about a month uptime we would see slow degradation so we used to reboot very heavily loaded DWH servers every 2 weeks as a standard.

    This was solved in win2008/sql server 2008 and we have not been routinely rebooting DWH servers since then. One of my customers is a MSFT gold partner that hosts DWs on SQL Server in their own data center so we have mountains of experience with DWH on SQL servers.

    So to see a win2012r2/sql server 2014 machine suddenly drop 20% of performance and then get it back on a reboot is a surprise to me. 

    We will introduce a routine to reboot it each week.

    But, I thought that MSFT support would be interested to know we have seen this sort of drop off from 27 days up time for an SQL server being run very hard in ETL for a DWH.
    Friday, July 28, 2017 9:34 AM