Skip to main content

How to optimize memory for indexing in SQL Server 2008

By default, SQL Server 2008 is able to manage the amount of memory allocated for index creation operations. The Server will be able to allocate additional memory for index creation operations, if additional memory is required for the process and the memory is available based on the “server memory configuration settings”. If additional memory is required but not available, the index creation will use the memory allocated to perform index creation. Usually, SQL Server self-tuning is able to work very well with this feature. However, the main exception in cases in which you can use partitioned tables and indexes and have nonaligned partitioned indexes. If there is a high degree of parallelism (lots of simultaneous index creation operations), you can encounter problems creating indexes. In this situation, you can allocate a specific amount of index creation memory.

If you want to use a specific index creation memory allocation, follow the following simple steps.

1. In the Server Properties dialog box, go to the Memory page and set a value in the Index Creation Memory box. This value is set in kilobytes.
2. Click OK.

You can also use the stored procedure sp_configure to set the index creation memory size. The related commands are as follows:
exec sp_configure "index create memory",

Windows PowerShell
Invoke-Sqlcmd -Query "exec sp_configure 'index create memory',
"-ServerInstance "Server\Instance"

Here, you have to note that the amount of the allocated memory to index creation operations should be at least as large as the minimum memory per query.
If it is not, SQL Server will use the amount of memory specified as the minimum memory per query and display a warning.


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