none
SQL2008服务器参数设置 RRS feed

  • 问题

  • 请教版主:

    有如下配置的服务器,为让SQL的性能发挥到最好,下面的参数要如何设置:

    2台DELL R910服务器+1台EMC CX480存储 6块光纤硬盘
    DELL R910服务器:CPU:4颗Intel Xeon 四核E7520,1.86GHz主频,18MB缓存;内存:64GB

    操作系统:
    WINDOWS SERVER 2008 R2 64B,SQL SERVER 2008 标准版

    2台服务器做群集:WINDOWS SERVER 2008 CLUSTER

    1.WINDOWS SEVER2008 R2 系统的虚拟内存应该设置多大才能符合以上硬件(64G内存),可发挥最大的性能?默认是自动管理,用了约50G左右空间。

    2.在SQL服务器属性中,内存一页:AWE是否要使用?

    3.在SQL服务器属性中,处理器一页:线程一项,最大工作线程数要设多少才能符合以上硬件发挥最佳性能?纤程是否更好?如何启用?

    4.在SQL服务器属性中,高级一页:并行及网络的参数用默认的参数好吗?

    5.如何用数据收集及资源调控器进行各性能参数分析?

    6.现在服务器的任务管理器中CPU的使用率在30%以下,但一运行一个转存作业就马上CPU用了100%,并且SQL锁数量明显增高,客户端表现为使用速度变慢,这个作业主要是把一年的数据转到另一个数据库中,在同一个服务器上的,不知为什么会这样?

    7.SQL锁能否完全没有?当硬件配置为极高时。上面的服务器配置能否算为极高?

    谢谢!

    2012年4月2日 10:27

答案

  • 1. page file size should be same as memory size at least.

    2. don't need awe if it's 64-bit sql2k8.

    3. use default, sql will decide based on number of processors.

    4. is it NUMA machine? If say each NUMA has 8 processors, maxdop should be <= 8. Better to set it <=8 also on non-NUMA machine. Leave default package size unless you see network waiting on the server.

    5. it's big topic, you can start with perfmon.

    6. you should check that job to find out what it does and why causes high cpu time, we can't tell you why without details.

    7. can't get rid of locking completely because of data integrity, but there are ways to reduce locking.

    2012年4月2日 14:03
  • Default settings work fine in most case, you can tune it later when have performance issue. I'll get enterprise edition of OS and sql, because they manage memory, multi-tasking and parallelism better. 
    2012年4月5日 13:20

全部回复

  • 1. page file size should be same as memory size at least.

    2. don't need awe if it's 64-bit sql2k8.

    3. use default, sql will decide based on number of processors.

    4. is it NUMA machine? If say each NUMA has 8 processors, maxdop should be <= 8. Better to set it <=8 also on non-NUMA machine. Leave default package size unless you see network waiting on the server.

    5. it's big topic, you can start with perfmon.

    6. you should check that job to find out what it does and why causes high cpu time, we can't tell you why without details.

    7. can't get rid of locking completely because of data integrity, but there are ways to reduce locking.

    2012年4月2日 14:03
  • 感谢回复!

    请问:有哪些方法减少锁的发生?

    针对上面的硬件配置,一般的优化性能,设置哪些参数,SERVER2008及SQL的?还是默认就可以?我想这么好的硬件配置, 要设置一下,不能浪费啊...

    2012年4月5日 10:48
  • Default settings work fine in most case, you can tune it later when have performance issue. I'll get enterprise edition of OS and sql, because they manage memory, multi-tasking and parallelism better. 
    2012年4月5日 13:20