March 5, 2012 at 7:47 am
Naive question from developer (not administrator!):
Is there stored procedure(s) that return(s):
1) overall disk space allocated to my server
2) current used/available memory size?
Thanks
March 5, 2012 at 8:48 am
valeryk2000 (3/5/2012)
Naive question from developer (not administrator!):Is there stored procedure(s) that return(s):
1) overall disk space allocated to my server
2) current used/available memory size?
Thanks
1) Depends on what you want to monitor: database size or disk space?
-- returns space used by database objects
EXEC sp_spaceused
-- returns free space for all drives
EXEC master.sys.xp_fixeddrives
2) This should do:
-- used / available memory
SELECT *
FROM (
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)')
) AS s
PIVOT (MAX(cntr_value) FOR counter_name IN ([Total Server Memory (KB)], [Target Server Memory (KB)])) AS p
Hope this helps
Gianluca
-- Gianluca Sartori
March 5, 2012 at 9:08 am
Thank you.
Both 'Total Server Memory (KB)' and 'Target Server Memory (KB)' gave the same result. Is it ok?
March 5, 2012 at 9:43 am
This means that your instance is using all the memory you reserved to it.
Target server memory = Total amount of memory in KB that the server can consume.
Total server memory = Amount of memory currently in use.
Is it ok? Well, it depends, but you won't see it using these counters.
You need to monitor some other counters that can help you identify memory pressure, such as "Page Life Expectancy".
SELECT *
FROM (
SELECT cntr_value AS current_PLE
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:Buffer Manager%'
AND counter_name = 'Page life expectancy'
) A,
(
SELECT CAST(300 * cntr_value / (4.0 * 1024 * 1024) AS int) AS PLE_threshold
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)'
) B
As long as "current_PLE" remains above "PLE_threshold" you likely have no memory pressure.
-- Gianluca Sartori
March 5, 2012 at 10:20 am
PLE is ok.
Thanks a lot!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply