|
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
(comments are locked)
|
|
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 to see how to find out the real memory usage of your SQL Server instance.
(comments are locked)
|
|
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.
(comments are locked)
|
|
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...
(comments are locked)
|
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?