Capture the Memory Performance report for SQL Server

Capture the Memory Performance report for SQL Server

Below queries will capture the Memory details which will be used for Performance report and analysis:

select * from master.dbo.sysperfinfo

where [OBJECT_NAME] like '%Manager%' and

 counter_name

 IN (

 'Buffer cache hit ratio',

 'Buffer cache hit ratio base',

 'Checkpoint pages/sec',

 'Database pages',

 'Free list stalls/sec',

 'Free pages',

 'Lazy writes/sec',

 'Page life expectancy',

 'Page lookups/sec',

 'Page reads/sec',

 'Page writes/sec',

 'Readahead pages/sec',

 'Reserved pages',

 'Stolen pages',

 'Target pages',

 'Total pages'

 )

 GO

 select [OBJECT_NAME],[counter_name],instance_name,cntr_value as Value from master.dbo.sysperfinfo

where [OBJECT_NAME] like '%Partition%' and

 counter_name

 IN (

 'Free list empty/sec',

 'Free list requests/sec',
 'Free pages'

 )

 GO

  select [OBJECT_NAME],[counter_name],cntr_value as Value from master.dbo.sysperfinfo

 where [OBJECT_NAME] like '%Memory Manager%' and

 counter_name

 IN (

 'Connection Memory (KB)',

 'Granted Workspace Memory (KB)',

 'Memory Grants Outstanding',

 'Memory Grants Pending',

 'Optimizer Memory (KB)',

 'SQL Cache Memory (KB)',

 'Target Server Memory (KB)',

 'Total Server Memory (KB)'

 )

Hope the above information helps

Thanks