Post reply

How will DBA utilize other 8 ndf tempdb files for low memory utilization.

  • November 25, 2011 at 4:29 am

    #247396

    On one site I have memory high utilization issue.

    DBA has created 8 NDF files for tempdb still my 8 ndf files are all showing 95232 Kb fixed size and MDF file is going to be around 8 GB.

    All data flles are set as unrestricted growth.

    How will he utilize other 8 ndf tempdb files for low memory utilization.

    MSSqlServer is 2008 sp2 64 bit.

    Thanks

  • November 25, 2011 at 4:32 am

    #1412952

    I don't understand what multiple tempdb data files have to with memory usage.

    Can you explain in more detail what's your issue exactly?

    -- Gianluca Sartori

  • November 25, 2011 at 4:43 am

    #1412954

    The issue is that the server's RAM is utilizing up to 59 GB out of 64 GB.So the another DBA has discussed from somewhere and on the dedicated drive of tempdb he has created 8 more ndf files.Still he has problem.He has kept the Tempdb Log files and data files on different folder but on same drive.

    I want to know what should I suggest him for low memory utilization.

    Thanks

  • November 25, 2011 at 6:13 am

    #1412973

    Multiple tempdb data files allow mitigating contention issues, but memory allocation is a whole different story.

    Read this page on Paul Randal's blog for more information on tempdb file I/O contention.

    For what memory is concerned, you should first of all determine if memory is really a bottleneck. A meaningful symptom is "Page Life Expectancy" perfomance counter going over "Total Server Memory" / 4GB * 300.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • November 25, 2011 at 6:29 am

    #1412977

    Article for Concurrency enhancements for the tempdb database

    - http://support.microsoft.com/kb/328551

  • November 25, 2011 at 10:43 am

    #1413042

    Sounds like your DBA has allocated 59GB of memory for the SQL server. If it's allocated it will eat it up (not necessarily using all of it, but SQL holds it). Other than using excessive parameters tables for large datasets I can't see any correlation between RAM usage and your tempdb. As viiki as already offered, check out that article from MS...worth the read.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • November 27, 2011 at 9:50 pm

    #1413232

    FYI...

    The SQL server max memory setting is 56 GB.still it is using 59 GB.

    How It's possible..

    Thanks

  • November 27, 2011 at 9:52 pm

    #1413235

    If tempdb has same directory with 8 ndf files will it mitigate contection issues?

    Multiple tempdb data files allow mitigating contention issues

    Thanks

  • November 27, 2011 at 11:34 pm

    #1413243

    Dear Gianluca,

    My Pagelifeexpectancy value is coming around 410 in perfmon.

    My total Server memory is 64 GB.So what will be calculated value from your formula.and please guide is PLF value is withing range or not.

    The server is 64 bit..

    Thanks

  • November 28, 2011 at 2:27 am

    #1413284

    This means that your SQL Server instance will read 59 GB of data every 410 seconds (it's a simplification, search Page Life Expectancy in Google and read something on the subject for a detailed explanation. However, should give you the big picture).

    Your instance is under (moderate) memory pressure. Tempdb has probably nothing to do with this, but you can check it using the advice in Paul Randal's blog (did you read the article I suggested?).

    You probably need better indexes, partitioning, filtered indexes, indexed views... who knows? There are lots of tuning measures and I can't suggest one without looking at the instance.

    My humble suggestion is to hire a database professional and let him tune the database.

    -- Gianluca Sartori

  • November 29, 2011 at 6:58 am

    #1414036

    Based on the questions the OP is asking my best advice is to get a professional on board for a system/performance review because there are some serious gaps in knowledge here that lead me to believe there will be MANY things suboptimally or misconfigured or done improperly in this SQL Server environment.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 20, 2012 at 3:13 am

    #1476478

    HI IAM ALSO FACING THE SAME PROBLEM SIMILARLY

    IAM A INITIAL LEVEL DBA NEARLY( 2 YEARS )

    BUT IDONT KNW WHETHER MY SUGGESTION HELPS U OR NOT

    1] IN OUR ORGANIZATION WE WORK FOR CLIENT IN THE TEMPDB UTILIZATION SCENARIO WE ARE HAVING 10GB DRIVE DUE TO THIS WHAT EVER THE UPDATES DONE BY THE APPLICATION TEAM TEMPDB LOG GRADUALLY INCREASES

    IN THIS SITUATION WE TRY TO SHRINK THE TEMP DB FILES (.MDF AND .LDF ) AND RESTRICTED NOT TO GROW

    LIKE THIS WE FACED TWICE IN THIS MEAN WHILE WE RAISED A CALL TO DL AND TL ASKING FOR A NEW DRIVE OF 50 GB IF IT IS ALLOCATED THEN THE ISSUE WILL BE SOLVED FOR NEXT ONE YEAR

    SO NOW YOU DECIDE BY UR CALCULATIONS HOW MUCH SIZE U NEED FOR MOVING THE DATA AND LOG FILES INTO SECONDARY GROUP

    2] AS PER THE MICROSOFT RECOMMENDATION THE RAM SIZE SHOULD BE MINIMUM 24 GB SO CHECK THIS ALSO IN UR SERVER HOW MUCH U R MAINTAINING

    THANKS & REGARDS

    NAGA.ROHITKUMAR.GMD

    Thanks
    Naga.Rohitkumar

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply