question

nitink avatar image
nitink asked

Settings of Server and SQL Server when using Windows 2003 (64 bit) and SQL Server 2005 (32 bit)

Hi Team, I have a question related to best settings possible that should be done on Server and SQL Server when using Windows 2003 (64 bit) and SQL Server 2005 (32 bit). Configuration is as follows: - Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2). - Windows Server 2003 R2 x64 Enterprise Edition. - RAM - 47.9 GB I have searched a lot on net and found few settings which can be done, but nothing concrete and its really confusing for a newbie like me. First is - Lock Pages In Memory Setting - Should I go for it or not and why? Second is - Enable AWE in SQL Server along with Specify Max and Min Memory - I am sure that this should be enabled through following link: http://sqlblog.com/blogs/argenis_fernandez/archive/2012/12/30/the-myth-around-32-bit-sql-server-instances-on-64-bit-operating-systems-and-awe.aspx Third is - Set 'Maximum Data throughput to network applications' in the LAN properties - Should I go for it or not and why? Going through the blogs i also learned about PAE and 3GB settings as well but they are not required to be done when windows is 64 bit. Am I Correct ? On using DBCC memorystatus floowing result is found - Memory Manager KB VM Reserved 3620088 VM Committed 3619108 AWE Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0 Memory in use gives - 3574688 KB using following query select sum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb] from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL' Kindly suggest. I am not a DBA or expert in this line (just a SQL Developer) but have been assigned with this task to increase the performance of the server and SQL Server. Regards, Gopal
64-bitwindows-server-2003sql server 2005 32 bit
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Lock Pages in Memory: Yeah, probably. But only because you are running on very old hardware. For more details, [read this article][1]. AWE: No. You're running 64 bit. That setting is only needed for 32 bit systems. Set Max Data throughput: ? Don't modify that one unless you really know what it is and why you need to deviate from the defaults PAE, etc. 32 bit only. Not applicable to you. Also, be sure you set max server memory. And, change the Cost Threshold for Parallelism from the default value of 5 to something else. I'd suggest 50. Others suggest 35. Even more important than all this, make darned sure you have backups running. [1]: https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.