SQL Tips by Namwar Rizvi

January 15, 2009

How to use more than 4GB RAM on SQL Server?

SQL Server is an highly optimized and well tuned piece of code. It try to utilise Operating System resources as optimum as possible. Due to this, most DBA think that if they will increase RAM on the server then SQL Server will automatically start using it, right? Wrong, SQL Server can only use RAM more than 4GB if and only if,

  • You are running Enterprise Editions of Windows 2003 Servers orAdvanced/Datacenter version of Windows 2000
  • You have enabled Operating System to use the extra RAM you have installed. Please check here for the details and how to do it.
  • You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server
  • you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server
Advertisements

August 28, 2008

Resource Governor in SQL Server 2008 – Beware of catches

Resource governor is one of the best additions in SQL Server 2008. It gives you a real control on your environment as a DBA and can be very useful for better utilization of your database server resources. So far so good, but don’t just start using it without knowing how it actually restricts resource utilization.  Following are some points to remember for better utilization of this feature:

  1. Normally, most of us assume that if we restricts a memory usage for a user A upto 10% only then he will never be able to utilize more than 10% of server in any case. Right? Wrong. Actually, Resource governer only restricts user to utilize not more than 10% if it feels that remaining memory is not available but if memory is available and there are no pending workload then it will allow the user to use more than its allowed quota of 10%. This is there to optimize the utilization of memory and avoids wastage of resources. But it can have worse effects also because if User A fires it query before other users then server will start utilizing all the available memory and all other users which came afterwards will suffer the consequences.
  2. Please note that Resource Governor is for database engine not for other services in SQL Server 2008. It means you can not control usage of Reporting Services, Analysis Services or Integration Services.
  3. If you have multiple instances running on same machine then you can not use Resource Governor to manage load between these instances.

Keeping these points in your mind will help you to better understand how to use resource governor and what to expect. It is one of the best tools to manage your load and highly recommended but make sure you know the pros and cons of it.

Create a free website or blog at WordPress.com.