SQL Server Memory - what's your method on Configuration and Troubleshooting Issues

Memory - an important aspect of system performance within a RDBMS platform, not specific to a database product or application.

Coming to Microsoft related products such as Windows Server and SQL Server so on, various resources available on web such as MSDN blogs, Books Online and articles, frequently viewed concept for memory is such as /3GB switch and optimum memory settings. In any case the performance will be sufferred if there is a memory shortage, adding fule to the situation with insufficient resources. So to dig further into this issue always consider 2 aspects of troubleshooting, available physical memory and usage of available virtual memory by any process on the server. Taking the first one into consideration say when a system has little RAM (physical) available the system will switch to get resources from the virtual memory manager with an increased workload to give recently accessed virtual memory pages for that process, this is where you see lots of paging activity to the disk on the server. IN this case when you get statistics with SYSMON (PERFMON) the behaviour can be retrieved that will confirm the downtrend of application availability on the resources. The second one talked about virtual memory occupation, which will lead to memory leak throwing lots of exception errors & warnings having another load to paging until the system excperience the shortage, completely.

So how you can take the configuration and troubleshooting of such an important aspect, take this into SQL Server coding aspect within the usage of cursors or retrieving large result set with queries that will occupy the memory location and will be removed only when new request for physical memory addresses, this also because of the manner in which virtual memory address space is mapped to physical memory on demand. The slow performance investigation should continue from memory usage by identifying whether excessive paging is occurring or not. However, remember that excessive paging may occur even if there is plenty of available memory - for example if an application is leaking memory.  As referred above  it is important to have a baseline of your system's performance and SQLMemoryConfiguration_Gotchas to compare to any new performance statistics that you gather.  So the next question will arise what kind of PERFMON counters you should consider in gathering such data, that we talk as follows (typical to Windows Server setup):

(These are gathered from the web resources such as Technet blogs & SQL Server performance website websites and not to mention my own experience)

PERFMON Counter - Memory:

• Pages / sec - important to identify the rate at which pages are read from disk to resolve hard page faults. This means the disk usage will be very high and when a fault is occurred then the system tries to read multiple contiguous pages into memory to maximize the benefit of the read operation. In this case make sure to capture the 'Pages Input/Sec' & 'Page Reads/Sec' counters too that will give better idea on average number of pages read into memory during this operation.

Bear in mind if the disk hardware is not efficient then you will see spikes even for smaller operations, in general on a SQL Server installation during the backup operations (such as transaction log & database) you will observe a spike which is a common behaviour and better RAID configuration will do much better. A reference on relevant disk blog posts here:

Utilizing RAID is beneficial to both the security of your data and the performance of your shop

Is RAID5 better for performance when SQL Server Clustering and SAN is involved?

Best way to deploy SQL Server 2005 with SAN

How to gain Disk related performance with few simple steps

What's your practice on Disk Defragmentation methods - specific to data file and indexes drives?

Determining the required capacity of an I/O subsystem before deploying the database

• Pool Nonpaged Bytes - as referred they show the values in bytes that relates to area of system memory (physical) by the operating system for objects (threads) that cannot be written to disk. The life of these processes will be in memory only as long as they are allocated. So if you see the value more than 85% (constantly) then refer to this NonPaged-Depletion-issue blog post.

•  %Committed Bytes in Use - is the space that has been reserved in paging file, to be written to disk. AskPerf team given much insight on this information here - "What is the Page File for anyway?".

• Available Bytes - this is required to calculate the immediate availability of memory allocation to a process and make sure to monitor this constantly, as any value lower than 10% of installed memory (physical) then it indicates the system is sufferring a lot.

Based on above counters if you see a high spikes on page file then consider to resize the pagefile.sys on your system. Also one of the best practices is to keep the page file on a seperate drive to avoid any excessively busy & overall system performance may be impacted.  Memory leak is another point to raise here that means an application will show up as a gradual increase in the value of the Private Bytes counter listed above.  So going further to address the issues is to gather the data & perform  a baseline analysis as per above blog posts.

First and foremost solution is to add more RAM (physical) by checking the compatibility such as SQL Server editions & memory configuraiton (such as More than 3GB memory is not used even though boot.ini has these switches on /3GB /PAE /AWE. & /3GB /PAE and AWE blurb what is it you hear within your environment? posts). Also it is  a good practice to design the application to get short & quick results for better performance on a latency based network, such as to offload some of the workload to another system, aka data partitioning.  Say if your data is not a problematic occurrence then make sure to perform a thorough checkout of hardware along with other components that may be experiencing issues based on the data you have collected.

Taking the general scenario of accessing the servers using a Remote Desktop Process (terminal services), the memory will be an issue as more and more applications are added to the Terminal Server environment, the amount of resources required by the various processes increases.  Such as adding more physical memory to that central monitoring server to address all those applications processes requirements. For instance if you see an increase value in Virtual Memory then increase the size of page file for a better allocation.

There is no internal mechanism to correct the application inability, such as consuming more memor and never levels off causing more grief to the performance. Here it is good to mention about disk fragmentation where the page file is stored, as it is usual case. Due to the fact that occurs when the page file is located on a disk that is heavily used by other application,  the disk itself is probably fragmented as well.  Recent blog post here about What's your practice on Disk Defragmentation methods - specific to data file and indexes drives? talks about patterns and practices you need to follow in defragmenting the disk. Some of the tools may not alleviate some of the issues sch as defragment paging files, you may want to consider moving the page file to another drive temporarily, setting the page file on the fragmented disk to 0 MB.  Reboot the system to enable the other page file to be used and perform the disk defragmentation on the original drive.  Once the defragmentation is complete, you can reset the page file on the original drive to the necessary values, zero out the page file size on the temporary drive and reboot the system again.