x

sudden memory usage decrease and slowdown in MSSQL2008 R2

I simultaneously moved from MSSQL2008 to MSSQL2008R2, and grew my database from 1.1GB to 1.5GB. I noticed that whereas before the sqlserver process used about 1GB of RAM when I ran my app, it now used at most 70MB. Also it was about 2.5 times slower. Is there some caching setting somewhere that has changed by default that I need to reenable/disable?

Thanks Markus
more ▼

asked Jan 03, 2011 at 04:20 AM in Default

markus3000 gravatar image

markus3000
11 1 1 1

I should have been clearer.. The app still uses the old database (MSSQL2008), but after growing the database to 1.5GB and doing a side by side installation of MSSQL2008R2, sqlserver uses LESS memory (65MB instead of 1GB), and is 2.5 times slower for use by the app (even after I stop all the R2 services). So I'm thinking either the new installation changed some caching settings on the old MSSQL2008 or after growing the database it now realizes it can't cache all of it and doesn't even try. I want it to use lots of memory so it's faster.. are there other caching settings I can enable to get this?
Jan 03, 2011 at 06:39 AM markus3000
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The server will take as much memory as you allow it to.

I suggest you take a look at the max memory settings for each instance and set it to something sensible.

E.G. You have a server with 16GB, both instances are to be used about the same and have the same size databases, I suggest you leave 4GB for the O/S and split the remaining 12GB between the two instances.

SQL Server will then allocate up to this amount of RAM to each instance (not instantly, it can take some time to get there).

Do not be put off by the fact that Task Manager displays a different (very low) RAM usage. This can be rather misleading. Also, if you are comparing the performance of a SQL Server that has a nice warm cache of a couple of days, compared to a newly restarted instance there can be some large differences in performance. You I/O subsystem may not be up to much and therefore show a dog-slow system while the cache is quite empty.

If possible, find the queries that are running slow and take a look at them running against an empty cache or a fully warmed cache. You can see huge differences here - and I really mean huge.

Take a look at [this][1] to see how to find out the real memory usage of your SQL Server instance.

[1]: http://ask.sqlservercentral.com/questions/5258/how-to-get-exact-dynamic-memory-usage-of-sql-server-2005
more ▼

answered Jan 03, 2011 at 07:05 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

During SQL Server startup it usually loads the data pages to its buffer cache. i.e As your Database grows SQL Server accommodated new data pages to the Buffer Cache to improve the query performance because accessing memory is faster than accessing Disk. In short your buffer cache might be expanded thats why SQL Server process is consuming more memory.

May be someone will have better answer.
more ▼

answered Jan 03, 2011 at 04:44 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left
Is this an upgrade or side-by-side install? If you have now increased the number of instances installed you will need to reduce the memory allocated to the older instances so that the new 2008 R2 instance has some that it can use. In my experience this needs to start of as a ratio in favour of the older instance(s) as they are supporting most activity and changes to favour the newer instances as systems upgrade to the newer versions...
more ▼

answered Jan 03, 2011 at 05:40 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x59

asked: Jan 03, 2011 at 04:20 AM

Seen: 1745 times

Last Updated: Jan 03, 2011 at 04:20 AM