Post reply

sql server IO threads and number of files/filegroups - Lots of confusion

  • June 2, 2012 at 1:56 pm

    #258964

    Ive been researching this online and im confused with all the contradicting articles.

    I want to know how sql server works from an IO thread perspective.

    If i have a 4 core server, thats 4 schedulers, so potential for 4 threads to work in paralell on a particular query.

    If i have only 1 database file, then in a paralell query il get waits as one thread is reading/updating rows in a table A on my file, the others have to wait.

    But if i have 4 files, do i get 4 IO threads?

    And does sql server allocate IO Threads based on Files, or Filegroups, or is it based on the drives the server can see, as i read in some articles?

  • June 2, 2012 at 3:47 pm

    #1496217

    Nope, that's a complete myth (and an irritatingly persistent one)

    The only time there is a specific number of threads writing per file is when SQL is creating a database and zeroing out the files, then it's one thread per underlying fdrive. Otherwise any thread can issue any IO to any file at any time.

    http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 2, 2012 at 4:40 pm

    #1496219

    thanks for the clarification gail. does that mean that if i have 20 core server, and 20 individual drives attached to my server, i create a filegroup with a file on each drive, i can potentially get 20 IO threads in paralell.

    SO performance wise this would be a good idea, not so much from a managability and administration POV.

  • June 3, 2012 at 2:03 am

    #1496257

    If you have a 20 core server and 1 drive with one data file and SQL is allowed to use all 20 cores, you could potentially get 20 threads doing IO in parallel. It has NOTHING to do with the number of files or drives except (and only except) when SQL is creating and zeroing out files. Please read the link I provided.

    The one thread per file is a huge myth and nothing more than a myth.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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