September 7, 2010 at 10:45 am
Ok, so I am running SQL 2005 Standard edition. I am trying to schedule an application update with one of our vendors and when I try to use performance monitor to view RAM usage by SQL it tells me that it is only using 8MB of RAM. This can't be actually what it is using b/c our application is running plenty fast. I have AWE turned on with a 2-4GB setting. Clearly the performance monitor isn't giving me an accurate reading. I've verified that the correct files are in the boot.ini folder. The only thing I've found is that it looks like our SQL 2005 server is running an older version of SQL Agent (2000). So the question is WHY? This was never an upgraded SQL, it was a clean install originally with 2005. So is there an easy way to upgrade/update the agent to 2005 w/o having to completely remove/re-install SQL and do DB restores? I am curretly going to update it to SP3. Will this fix my issue?
September 7, 2010 at 11:00 am
Where are you seeing 8MB in use? What counter?
What's this about 2000's agent and why do you think that?
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 7, 2010 at 11:01 am
Same response as Gail: Which counter in Perfmon are you using?
SQL Server 2005 DMVs include a sys.dm_os_performance_counters that allows you to read in the counters that are added with the SQL installation. You can try something like this to get an idea of memory usage:
select object_name, counter_name, cntr_value from sys.dm_os_performance_counters
where counter_name = 'Total Server Memory (KB)' Never mind, just saw this does not show total memory used by SQL Server 😀
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 7, 2010 at 11:23 am
I am using SQL:Memory Manager: Total and Target. I am just using the Windows Perf. Mon. using the counters above. My target memory is showing up on the graph as 1692112KB (last, average, min/max) and my total is only showing up as 10448KB. This is what is confusing me b/c AWE is set as 2-4GB. It looks like SQL 2000 service manager is the default instance and is in conflict with the SQL 2005 service (being the named instance) for reporting to perfmon.
I just completed a SP3 update and I still see the same results w/ perfmon. It is showing that my total mem. usage is on 10mb which clearly can't be correct.
September 7, 2010 at 1:23 pm
The counters are specific to the instance they are monitoring. I don't know that I have ever seen them conflict with another instance.
If you just applied SP3, how long has the instance been up and running? If total is significantly lower than what Target is this usually means that the buffer pool may be experiencing memory pressure and cannot grow. However if you just restarted the server I would give it a while and setup an perfmon log to capture these events over time.
If you enabled AWE did you add the service account to the Lock Pages in Memory in Group Policy?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply