should i make as many datafiles for temdb as many cpus i have

  • hi,

    q1 As people said if u ghave GAM conention then u can go for adding as many datafiles of equal size in temdb as many cpus u have is it correct in sqlserver 2019 vesion also.

    q2) right click on sqlserver instance then select reprots->perfomance dashboard report then click io statistics . i have attched mine , i have read that it should be less than 10 15 ms per transact(avrage of read and wirte). pls correct me.

    yours sincerley

    iostatistics

  • To be honest, I've found that most such contention is because of what is appropriately referred to as "crap code". 😉

    I recommend that you figure out what's beating on TempDB and fix that before you add any more files.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • second question is unanswered.

  • one more thing  my log file of database (this is not tempdb this is reular db) is increasing like this , should i increase then auto increment?

    file is attached.

    Attachments:
    You must be logged in to view attached files.
  • my second query is. the place i am looking is correct  place for (10 to 15 ms)  or it is telling diffrent  story?

  • rajemessage 14195 wrote:

    one more thing  my log file of database (this is not tempdb this is regular db) is increasing like this , should i increase then auto increment?

    file is attached.

    looks like you have the increase set to 10% - CHANGE it to be a fixed amount (multiple of 64KB - for that small log file likely a 32 MB increment would work fine. - so way to do it is change increment to 32MB then shrink it to the minimum possible and then let it increase to 128MB - this would leave some space free before growing again.

    have a look at https://www.brentozar.com/blitz/blitz-result-percent-growth-use/ for info on growth - obviously increment should be adjusted to size of db - I have some db's with a data growth of 5GB (with IFI enabled) but these are multi terabyte sizes where growth happens during nightly batch window when it needs.

  • edit: dup post

  • artical does not say any thing about critaria , like the increment of datafile or log file should be once in a day or twoice in day?

    like if i know 1 gb data is required per day by my datafile or log file so should I keep the increment to 500mb or 1 gb ?

  • I'm confused.... the log file name of your latest graphic doesn't appear to match or relate to any database names in your first graphic.  You've also not said anything about how big your current TECHINTFDC database is or the largest index or heap you have.  Do you have any idea how big this new database is going to get in the next year?

    Also, do you know what the "IFI" that Frederico was talking about is?

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • i think he said it should increment to the size of the database. but still u can answer on my meterics like onece in day or twoice in a day or in a week?

    q2) in prerfomace monitor of windows i can check Disk Write Bytes/Sec and in datacollector, can i see or collect this using any dmv?

Viewing 10 posts - 1 through 9 (of 9 total)

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