Tuning SQL Server configurations

One place to sometimes view configuration for SQL Server is the full disclosure documents for TPC benchmarks, yes I know I should get out more, however, it does sometimes make for interesting reading on exactly how some fantastic throughput is achieved in a test environment.

I'm still researching x64 configs, especially for windows 2003 rather than SQL Server and I just happened to find this KB article. Now I'm not suggesting we all rush out and implement any of this, but, it does make for interesting reading and maybe indicates there's more to tweaking SQL performance than meets the eye.

Were any of these implemented for the TPC benchmark I viewed, yes -T834 , and yes it was a x64 SQL Ent with 65GB ram with 8 cores. Makes you think!

As part of the disclosure there is an output of sp_configure - changes I could see ( the output was unformatted ! ) 

  • min memory per query (KB) = 512kb
  • awe enabled = 1 ( ! )
  • lightweight pooling = 1
  • max server memory (MB) = 63488
  • min server memory (MB) = 61440
  • max worker threads = 1024
  • priority boost = 1 ( ! )
  • recovery interval (min) = 32767
  • network packet size (B) = 32767

http://support.microsoft.com/kb/920093

http://msdn2.microsoft.com/en-us/library/aa366720.aspx

 

Published 10 January 2008 12:32 by GrumpyOldDBA
Filed under: , ,

Comments

# re: Tuning SQL Server configurations

11 January 2008 14:20 by Christian

Hi Grumpy, another nice topic :) The problem with TPC benchmarks is that they have no correlation to the real-world.

Priority Boost for example I beleieve was only made available because the TPC rules state that only generally available features can be used for the benchmark so they had to add it.  I've never heard of a customer being recommended to switch it on by Microsoft.

The Recovery Interval affects how often checkpoints occur but this setting is only part of the algorithm to determine that.  For the TPC benchmark they want to avoid checkpoints as much as possible to gain a performance advantage but you'd never want that in a working system.

Lightweight Pooling disables the schedulers and uses "fiber mode" to execute threads.  Its been recommended in the past for systems with huge numbers of context switches but its not really used much any more and will stop SQLXML, SQLMail and CLR from working (maybe FTS as well actually).

Enabling awe in 64-bit is achieved by granting the SQL Service account the "Lock pages in memory" advanced user right and is used because AWE memory (the buffer pool) can't be paged paged out by the OS.  Its been knocking as almost a best practice for a little while by Microsoft PSS have recently decided that its shouldn't be a default BP because it can introduce other issues.

Regards,

Christian

Database Architect

htpp://www.coeo.com