Post reply

64KB and 32KB cluster size for log drives and tempdb

  • August 16, 2024 at 10:06 am

    #4446494

    Hi,

    I was told that sometimes log and tempdb drives benefit from 32KB cluster size as opposed the traditional 64KB.  How can I check this theory for a specific system?  I guess different database servers have different write patterns. How do I find out what write sizes a particular server uses the most and if 32KB cluster will improve performance compared to a 64KB cluster?

    Thanks.

     

  • August 16, 2024 at 2:33 pm

    #4446565

    SQL data files could in theory gain from 64K cluster size, since SQL often writes data in 64K chunks.

    SQL log files are different, so 32K should be just fine for log files.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 17, 2024 at 4:35 am

    #4446705

    Before you mess with those things...

    ... try setting the following...

    1. Make sure Compressed backups are enabled (UNLESS your database is encrypted https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server ).
    2. Set the "buffercount" to 17 as a starting point.
    3. Set the "maxtransfersize" to 1048576 as a starting point.

    I say "starting point" but I've never had to change those settings nor do that blocksize thingy.

    YMMV.

    --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)

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

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