Increasing the mimimum memory used per query

SQL Server’s sp_configure settings contains a setting named ‘min memory per query (KB)’. This setting allows you to set the default minimum amount of RAM that’s allocated for each query — but should be used with care. Like many SQL Server settings, the default setting is best for most situations. However, if you have RAM to burn, BOL offers that increasing this setting could have a positive impact on small queries:

“Increasing the value of min memory per query may improve performance for some small to medium-sized queries, but doing so could lead to increased competition for memory resources.”

Updates to the ‘min memory per query (KB)’ setting can be done like this:

--first, turn on advanced options
SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GO
--then, set new value - default value is 1024KB, this increases it to 2MB
SP_CONFIGURE 'min memory per query (KB)',2048
GO
RECONFIGURE WITH OVERRIDE
GO
--now, display new value
SP_CONFIGURE 'min memory per query (KB)'
GO
RECONFIGURE WITH OVERRIDE
GO
___________________________________________________________________________________________________

http://msdn.microsoft.com/en-us/library/ms181047.aspx

About these ads

Posted on October 27, 2011, in SQL Server and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: