Memory problem to Execute Query

This article gives a simple demonstration to increase the Query memory size of SQL Server 2008.

SQL server 2008 takes 1024 KB of memory for executing the query. This memory is allocated generally by per session.

When we fall the lack of memory problem to execute the query we can increase the size for better performance.

We can set the size from 512 KB to 2 GB for SQL Server 2008.

If we increase the memory size the performance of processer-intensive operation such as sorting or hashing is improved.

To adjust the minimum query size we have to determine some points mentioned bellow.

1.    Total amount of free memory when SQL server is in running or idle mode.

2.    The average number of simultaneous query runs by per session.

3.     The average query size.

4.    The query responses time that you hope to achieve.

By this we can create a formula like this

Free memory / (AVG Query Size * AVG Number of Simultaneous Query)

Let's take an example:

Free Memory                                                  = 2200 MB

The Average query size                                 = 2 MB

The Average number of simultaneous query = 50

So

= 220 MB/(2 * 50)

= 22 MB

To allocate memory for Query

1.    Server property dialog box go to the Memory Page and set the value for the Minimum memory per Query (in KB)

2.    Click OK

3.    Restart the SQL Server services.

Hope you like it.

Posted by: MR. JOYDEEP DAS