Skip to main content

Tips: Steps to customize Memory Allocation for Queries in SQL Server2008

In order to perform a query execution, SQL Server 2008 allocates a minimum of 1024 KB of memory; it is a default action. As it is a guaranteed allocation per user, you can set it anywhere from 512KB to 2GB. By increasing the minimum query size, you can improve the performance of queries that perform processor-intensive operations, such as sorting or hashing. Even if you set a higher value, you can degrade the overall performance of the system. Therefore, it is required to adjust the minimum query size only when there is any trouble in quickly executing the queries.

You can see the default setting of 1024 KB of RAM works in most cases. If the server works at an extreme busy environment, you would still have to change the value accordingly. You can do it with lots of simultaneous queries running in separate user connections, or in a relatively slow environment, with few (but large or complex) queries. There are four factors that determine your decision to adjust the minimum query size as follow.

•    The total amount of free memory (when the system is idle and SQL Server is running)
•    The average number of simultaneous queries running in separate user connections
•    The average query size
•    The query response time you hope to achieve
The fact is that you should be ready for a compromise while setting these values as you can’t always get an instant response. However, you can still optimize the performance based on the available resources. You can go with the following equation to get a starting point for an optimization.

FreeMemory / (AvgQuerySize * AvgNumSimulQueries)

For an instance, a system has 2200 MB of free memory, average query size is 2MB and the average number of simultaneous queries is 50, then the optimal value for the query size is 2200 MB / (2 * 50), or 22 MB.

You can follow the following steps to allocate memory for queries:
1.    First, it is required to open the Memory Page from the Server Properties dialog box.
2.    Then, set a value for the Minimum Memory Per Query box.
3.    This value will be set in kilobytes.
4.    Now, click OK. That’s it.
5.    If you wish, you can still use the stored procedure sp_configure to set the minimum query size. The related command is:
exec sp_configure "min memory per query",
6.    Try working on this.
B  y

Popular posts from this blog

New Micro battery, power packed for high performance

For a size of a few millimetres, the new micro-batteries are powerful enough to jump start a car battery and then quickly charge the cell phone. Researchers from the University of Illinois have developed the micro batteries which can be used to drive compact electronic devices and new applications for radio communications. The micro batteries are so good that they can even out power the super capacitors. The results were published in ‘Nature Communications’ in the April 16 issue. The micro batteries have energy and power and with the researchers tweaking the structure of the micro batteries a little bit, its use can be for wide range applications. The high performance of the micro battery is based on its 3-dimensional micro-structure. Based on the design of fast charging cathode by Professor Paul Braun’s group, researchers developed the matching anode and integrated it with the fast charging cathode at a micro-scale and made a complete battery with high performance. These batteries cou

HTC Touch Diamond SmartPhone!

The phones design is an innovative in which the user has the geometric form language to make sure a clutter-free environment for screening content. The San Francisco based studio One & Co collaborated with HTC Taiwan Design team to create Diamond HTC smartphone. The HTC phone is quite wonderful with design and the rapid development in the smartphone market the HTC has taken innovative steps to capture the SmartPhone market HTC develops these kind of new inventive designs. Inspired

Google Health Login Page is Ready!

Google's Marissa Mayer announced that the Google Health will be launched on 2008. The service of the Google Health Login Page is ready. It's only the first intro page is displayed and I didn't get passed to the next page. In the same page y0u can see the information displaying on the Google Health. If you point your mouse on the below link you can visit the Google Health Page With Google Health, you can: * Build online health profiles that belong to you * Download medical records from doctors and pharmacies * Get personalized health guidance and relevant news * Find qualified doctors and connect to time-saving services * Share selected information with family or caregivers This will be a nice development about caring health and this will helps in having some cautious about health. Inspired