Click here to Skip to main content
14,880,638 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.

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
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900