Memory and tempDB on SQL server

  • Hello guys,

    notice : my boss finally went out and I can focus to optimalizing. 😀 So its his work.

    I have ask about tempdb, using memory and IO disk .

    We have 16CPU AND 32GB memory

    I read that

    - number of temp db files like CPU - 16 ? true or false ?

    - it is possible memory should be like count of size all of databases what we use ?True? and if this true , it is with ldf? or just mdf?

    Memory is still full - standby is 1000 and free is about 1000

    tempDB has just 12files - 1024MB with autogrowth by 128MB unlimited + log

    and I think that can be problem with performance, because some operations will wait for disk instead memory _ true ?

    Minimum server memory 16384MB, Maximum 28000MB

    we have few DB, but primary has about 9GB and increases (1per month we move to another, which has 91GB mdf, ldf11GB)

    and primary database has ldf file about 42GB

    I am little confuse from this, you know I started read about this few weeks ago .

    Thx you for response

  • tony28 (5/21/2013)


    I read that

    - number of temp db files like CPU - 16 ? true or false ?

    Rule of thumb before we starting getting rediculous amounts of cores. At that point usually you'd use half unless you're getting very particular errors based on being unable to create new temp tables.

    - it is possible memory should be like count of size all of databases what we use ?True? and if this true , it is with ldf? or just mdf?

    If possible, yes, and usually you'd use the mdfs. It's all about Page Life Expectency and when the buffers are forced to flush and replace memory pages with new data. The ldfs are your log files and they track changes to the data, and must be written to disk, so that's not as critical when you're discussing memory.

    Memory is still full - standby is 1000 and free is about 1000

    Um, can you clarify what you mean here?

    tempDB has just 12files - 1024MB with autogrowth by 128MB unlimited + log

    I wouldn't worry about adjusting TempDB unless you're getting the errors I mentioned.

    and I think that can be problem with performance, because some operations will wait for disk instead memory _ true ?

    You'll have to tell me how you've detected you have I/O performance concerns. If you mean the PAGEIOLATCH wait, then that's actually not tempdb, that's data from the mdfs. It's standard to get some of that, particularly with very large queries. It depends, as usual, on what you're doing and what you're working against.

    and primary database has ldf file about 42GB

    You ARE doing log backups, right?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • tony28 (5/21/2013)

    I read that

    - number of temp db files like CPU - 16 ? true or false ?

    Rule of thumb before we starting getting rediculous amounts of cores. At that point usually you'd use half unless you're getting very particular errors based on being unable to create new temp tables.

    - I wanted increases temp files, because we use lot of #temp tables in procedure and next in some procedures is used also cursor

    - and what with this setting ? I read something about problem with autogrowth, that You must set the appropriate size of tempdb and dont rely on auto-grow , you know we are car company so I found some text

    "If the production environment cannot tolerate the potential for application time-outs that may occur during autogrow operations, preallocate space to allow for the expected workload."

    And I think that it can be our problem. http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/what-is-allocation-bottleneck.aspx

    Memory is still full - standby is 1000 and free is about 1000

    Um, can you clarify what you mean here?

    hehe sorry, these are values from resource monitor - all are about circa

    -IN USE 31 571 MB - SQL server has Commit 30 151 MB and Working 29 794 MB

    -Standby 672 MB

    - Free 509MB

    and I think that can be problem with performance, because some operations will wait for disk instead memory _ true ?

    You'll have to tell me how you've detected you have I/O performance concerns. If you mean the PAGEIOLATCH wait, then that's actually not tempdb, that's data from the mdfs. It's standard to get some of that, particularly with very large queries. It depends, as usual, on what you're doing and what you're working against.

    we still have problem with few programs, because sometimes is delay, and we dont know why, and I was at training with sql and trainer told about this, that tempdb can be increases and in some load can be problem with another procedure...

    and in windows, where is the program we found message problem with IO device

    and primary database has ldf file about 42GB

    You ARE doing log backups, right?

    yes I know, it increases according number of backups

Viewing 3 posts - 1 through 2 (of 2 total)

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