September 26, 2012 at 5:36 am
We have 2 SQL servers here at work. Both "appear" to be configured the same. But when I look at Task manager I see that one of them is using 1.7GB of the 4GB on the server. The other is using about 400MB of its 4GB. These are Win2k3 SP2 (32 Bit).
We are looking at possibly moving to a different machine and I started digging more into the configs to make sure the new machine can handle it when I found this.
I do see where Min Memory in Properties is set to 0 and max is the "default" 2147483647. Which I will be changing.
We do have different apps on the two machines, but I rebooted the one using 1.7GB this morning and no one is online yet and it shot right back up to the max memory it can get. They are performing "Ok" from what I can tell. But I would have thought they both would do the same.
Thoughts? What am I missing? Thanks!
September 26, 2012 at 6:56 am
It totally depends on the applications that you have on each server. When you fire up the first server and it just right to 1.4GB, there are most likely Agent Jobs or applications connecting to the database that are causing SQL to buffer data.
As for the other server, the database may be small, there may not be many processes requesting info from SQL.
Since you have two different applications on two different servers, you can't compare they as you are.
Hope this helps.
-
Jason
September 26, 2012 at 7:10 am
TimParker (9/26/2012)
We have 2 SQL servers here at work. Both "appear" to be configured the same. But when I look at Task manager I see that one of them is using 1.7GB of the 4GB on the server. The other is using about 400MB of its 4GB. These are Win2k3 SP2 (32 Bit).We are looking at possibly moving to a different machine and I started digging more into the configs to make sure the new machine can handle it when I found this.
I do see where Min Memory in Properties is set to 0 and max is the "default" 2147483647. Which I will be changing.
We do have different apps on the two machines, but I rebooted the one using 1.7GB this morning and no one is online yet and it shot right back up to the max memory it can get. They are performing "Ok" from what I can tell. But I would have thought they both would do the same.
Thoughts? What am I missing? Thanks!
The server using 1.7GB means it need that much memory or you might have given the min server memory to that value. Once SQL server crosses the min server value configured it will keep that always with it.
September 26, 2012 at 7:11 am
Yeah, we're comparing apples to hammers to a degree. If there are varying loads on the servers, other differing executables, etc., then you can't really compare resource usage between them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2012 at 7:30 am
Thanks Grant, love the analogy. I may have to swipe that one...
Guess I thought (or at least hadn't paid attention previously) that it would take the max ram and then give back as the OS asked for it. So I guess I assumed they should both be at 1.7GB until the OS needed it (even if it wasn't needed by SQL). They definately are two different loads and programs.
Performance as I stated is still good. We are just looking at condensing them to one SQL server and I want to make sure I have enough power on it. We have to condense or do something as these both have 20GB C: partitions (and that is where they installed SQL to....Whoops). :w00t:
September 26, 2012 at 7:36 am
Do not use task manager to check SQL's memory usage. Task manager can be extremely wrong as it does not include locked pages in its memory count.
Use the perfmon counter Total Server Memory under the SQL counters to check a SQL instance's memory usage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2012 at 7:51 am
Guess I am a bit confused now. I just loaded up perfmon on both these machines. We are looking at apples and hammers as Grant said, in the fact that one of these was just rebooted a few hours ago. I am assuming the counter will get reset with a restart of service/reboot of server.
I have one that is returning 5712(KB) which I believe converts to just under 6MB. The other one is returning 312,832(KB) which would be 305 MB. The first one, is the one that Task Manager is showing at 1.7GB. Seems backwards.
Guess I have not had to get this technical with SQL in my past. Time to do some learning.....I am all ears on info, links, books. Thank you in advance for any help and guidance.
September 26, 2012 at 8:06 am
Not at all backwards. As I said, Task manager shows memory incorrectly as it does not account for memory allocated via the locked pages mechanism.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply