none
TempDB size increasing - SQL Server 2008 R2

    Question

  • Hi All,

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    8 core processor , 24 GB RAM.

    We have doen file partitioning of the tempdb and created 8 + 1 = 9 data files with initial size 512 MB for each file and

    4+1 = 5 log files 1024 MB for each file.

    Growth rate 10 % unrestricted.

    By having these details, we started the application and put some load on the application, with 25 users for 1 hour .. tempdb has grown to 40 GB by occupying majority of the C drive space. Is there any optimization that we can do at server or tempdb level. Also are there any statistics that we can fetch from the DMV/DMF , so that we can optimize the processing further . Please suggest.

     

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Saturday, June 11, 2011 7:54 AM

Answers

  • I would start with http://msdn.microsoft.com/en-us/library/ms176029.aspx
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Slaven Sola Saturday, June 11, 2011 10:42 AM
    • Marked as answer by Reddy Balaji C Monday, June 13, 2011 4:17 AM
    Saturday, June 11, 2011 8:11 AM
  • You said you configured your tempdb to C:\ drive , thats not recommended you would have to move the tempdb files to a faster storage array and have them on a proper raid configuration if possible. Though you have used MSFT recommended practice for number of tempdb files equal to number of logical cores , Paul randal suggests with explanation to start from 1/4 to 1/2 files per core and start increasing from there rather using a plain formula for 1:1 -http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx . I doubt your tempdb log file configuration too no matter how many files you have only one will be active at any time, having extra files won’t necessarily increase performance.

    There are lot of reasons why tempdb files grow enormously as mentioned in the link posted by Balmukund.


    Thanks, Leks
    Sunday, June 12, 2011 12:30 AM

All replies

  • I would start with http://msdn.microsoft.com/en-us/library/ms176029.aspx
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Slaven Sola Saturday, June 11, 2011 10:42 AM
    • Marked as answer by Reddy Balaji C Monday, June 13, 2011 4:17 AM
    Saturday, June 11, 2011 8:11 AM
  • Hi Balmukund,

                          Thank you for the information ... The link is very usefull .. I have the other query rrelated to the file partitioning as i mentioned ... The Distribution of the TempDB files is fine according to the available configuration ..

    please suggest.


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Saturday, June 11, 2011 3:08 PM
  • You said you configured your tempdb to C:\ drive , thats not recommended you would have to move the tempdb files to a faster storage array and have them on a proper raid configuration if possible. Though you have used MSFT recommended practice for number of tempdb files equal to number of logical cores , Paul randal suggests with explanation to start from 1/4 to 1/2 files per core and start increasing from there rather using a plain formula for 1:1 -http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx . I doubt your tempdb log file configuration too no matter how many files you have only one will be active at any time, having extra files won’t necessarily increase performance.

    There are lot of reasons why tempdb files grow enormously as mentioned in the link posted by Balmukund.


    Thanks, Leks
    Sunday, June 12, 2011 12:30 AM
  • Another thought

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, June 12, 2011 6:58 AM