vb.net 2d barcode generator Real World in Visual C#

Creator EAN-13 Supplement 5 in Visual C# Real World

Assuming that the database is large, adding memory to a SQL Server system almost always provides a benefit. The value of sizing is in determining how much memory is required and what value it provides. Although the price has dropped in the past few years, memory can still be very expensive. In addition, the larger the memory module, the more expensive it can be. In order to put 32 GB or 64 GB of RAM in your system, you might have to purchase very large modules. Since data is stored in 8-KB pages both on disk and in memory, you might have a problem with page allocation. The number of pages in the buffer cache is equal to the amount of memory divided by the page size (which is 8 KB). Although data is stored in the buffer cache in pages, it is usually used in rows. By keeping together rows that are used together, you can more effectively use the buffer cache.

6

For example, if you have 800 MB of memory allocated to the SQL Server buffer cache, this equates, at 8 KB per page, to 102,400 pages in memory. If you are only using one row per page, this gives you 102,400 rows in memory. If you can actually use an average of 10 rows per page, then you have 1,024,000 useful rows in memory. Since data is typically sorted into clustered indexes, it is important that you cluster on a useful cluster key that might result in efficient memory usage.

As discussed in the previous section, Windows Server 2003 Task Manager can be a convenient tool for displaying the amount of memory used and available in the system. The memory utilization can be found in the Performance tab of Task Manager, as shown previously in Figure 6-3. Memory can also be monitored effectively in perfmon. One of the most important perfmon counters is the Pages/sec counter under the Memory object. This counter tells you whether you are experiencing excessive paging. It is okay to have some paging, but if this number is 100 or greater, you have a big problem. In addition, the Percent Usage and Percent Usage Peak counters in the Paging File performance object can also indicate excessive paging in the operating system. Memory counters include the following:

Pages/sec Found under the Memory object, this counter provides information on the amount of paging that is happening in the system. This is a key counter that indicates memory is over-allocated. Available Mbytes Also found under the Memory object, this counter indicates how much memory is available for programs to use. Percent Usage Found under the Paging File object, this counter indicates the percentage of the paging file that is currently used. Significant usage indicates serious paging. Percent Usage Peak Also found under the Paging File object, this counter indicates the highest percentage of the paging file that has been used. This can indicate whether you have ever had a significant amount of paging.

These important Windows memory counters are shown in Figure 6-6. The SQL Server cache-hit ratio can be monitored in perfmon via the buffer cache-hit ratio counter, available through the Buffer Manager performance object. Your goal should be 100 percent; however, anything above 98 percent is acceptable. If you are consistently seeing a much lower cache-hit ratio, you should take steps to attempt to improve this.

Figure 6-6

Achieving this goal should be your target, however, it is not always possible to reach a 98 percent cache-hit ratio. With very large databases that are performing large queries, you might not achieve this; however, you should still strive for it.