Hi Friends,

Please help me I saw lot of script over internet but useless , as I am searching for query to find total memory allocated to sqlserver,  and how much being used utilized as wel cpu utilization percentage . 

than

If you want to know what the max server memory is set at: 

EXEC sp_configure 'max server memory';

If you want to know what it is currently actually allocated to SQL Server use: 

SELECT object_name, cntr_value 
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';

thank you for reply, I want to  get both information using query as well the cpu utilization  on other query

 

SELECT * FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Total Server Memory (KB)'
   OR counter_name = 'Target Server Memory (KB)'; 

--sql server uses the memory
select 
CONVERT(VARCHAR,CAST(bpool_committed *8  AS MONEY),1)AS [SIZE],
bpool_committed,bpool_commit_target 
  from 
sys.dm_os_sys_info

-----CPU and more

CREATE TABLE #ServerStats (create_time datetime,
                           component_type sysname,
                           component_name sysname,
                           state int,
                           state_desc sysname,
                           data xml)
INSERT INTO #ServerStats execute sp_server_diagnostics
-- System
select 'System' as "System",
       data.value('(/system/@systemCpuUtilization)[1]','bigint') as "System CPU",
       data.value('(/system/@sqlCpuUtilization)[1]','bigint') as "SQL CPU",
       data.value('(/system/@nonYieldingTasksReported)[1]','bigint') as "Non-yielding Tasks",
       data.value('(/system/@pageFaults)[1]','bigint') as "Page Faults",
       data.value('(/system/@latchWarnings)[1]','bigint') as "LatchWarnings"
  from #ServerStats 
 where component_name like 'system'

 -- Memory
select 'Memory' as "Memory",
       data.value('(/resource/memoryReport/entry[@description="Working Set"]/@value)[1]',
          'float')/1024/1024 as "Memory Used by SQL Server (MB)",
       data.value('(/resource/memoryReport/entry[@description="Available Physical Memory"]/@value)[1]',
          'float')/1024/1024 as "Physical Memory Available (MB)",
       data.value('(/resource/@lastNotification)[1]','varchar(100)') 
          as "Last Notification",
       data.value('(/resource/@outOfMemoryExceptions)[1]','bigint') 
          as "Out of Memory Exceptions"
  from #ServerStats 
 where component_name like 'resource'


 select 'Non Preemptive by duration' as "Wait",
       tbl.evt.value('(@waitType)','varchar(100)') as "Wait Type",
       tbl.evt.value('(@waits)','bigint') as "Waits",
       tbl.evt.value('(@averageWaitTime)','bigint') as "Avg Wait Time",
       tbl.evt.value('(@maxWaitTime)','bigint') as "Max Wait Time"
from #ServerStats CROSS APPLY 
     data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS tbl(evt)
 where component_name like 'query_processing'

 -- CPU intensive queries
select 'CPU Intensive Queries' as "CPU Intensive Queries",
       tbl.evt.value('(@sessionId)','bigint') as "Session ID",
       tbl.evt.value('(@command)','varchar(100)') as "Command",
       tbl.evt.value('(@cpuUtilization)','bigint') as "CPU",
       tbl.evt.value('(@cpuTimeMs)','bigint') as "CPU Time (ms)"
  from #ServerStats CROSS APPLY
       data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS tbl(evt)
 where component_name like 'query_processing'

 -- IO report
select 'IO Subsystem' as "IO Subsystem",
       data.value('(/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as "Latch Timeouts",
       data.value('(/ioSubsystem/@totalLongIos)[1]','bigint') as "Total Long IOs"
from #ServerStats 
 where component_name like 'io_subsystem'



 TRUNCATE TABLE #ServerStats

thank you Shanky, as you mention sqlserver can use more than set in such case the same query will work and give correct result, Please further for cpu  utilization help me