Maximizing Data Throughput setting for SQL Server

• Igor Marchenko

  Hall of Fame

  Points: 3281

  More actions

  May 14, 2009 at 6:01 pm

  #209773

  Hello!

  I have stumbled across following information:

  http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/357/An-Often-Overlooked-Windows-Setting-for-the-DBA.aspx

  http://msdn.microsoft.com/en-us/library/ms191471(sql.90).aspx

  It sounds like by default Microsoft Server installation should set 'Maximize data throughput for network applications'. This is not the case for our production SQL Server 2005 64-bit cluster. Our current setting is 'maximize data throuhgput for file sharing'.

  I was wondering if this indeed a recommended setting for SQL Server environment. Does this require a service restart? Our server has plenty of space (26GB of RAM) and is not under any pressure (memory, IO,CPU). Will I see performance improvements if I change this setting?

  Thanks,

  Igor

• Andrew Hatfield

  SSCrazy

  Points: 2241

  More actions

  May 14, 2009 at 6:55 pm

  #994990

  If you are not currently experiencing performance pressure you will probably not see much if any benefit. In saying that, while your existing setup may be meeting your performance requirements; it may be performing very badly and you just aren't noticing.

  However, in saying that - the correct settings for SQL Server on a dedicated server instance are as follows

  Network Optimisation

  - Maximize throughput for network applications (default File Sharing)

  Processor Scheduling

  - Background Services (default Programs)

  Memory Usage

  - Programs (default System Cache)

  From memory, none of these require a reboot to take effect. But if you can, it won't hurt just to make sure.

  These settings will tune your Windows instance for SQL Server - so if you are experiencing performance problems; they should help.

  --
  Andrew Hatfield

• Igor Marchenko

  Hall of Fame

  Points: 3281

  More actions

  May 14, 2009 at 8:49 pm

  #995024

  Thanks a lot, Andrew!