This is my favorite script to run against multiple servers to get a 50,000 foot view of what’s happening across my domain.
/* Steve Schneider Designed for SQL 2005+
MemMB: MB of Memory assigned to the BOX.
Memory Object: Available Bytes: On a server dedicated to SQL Server, SQL Server attempts to maintain from 4-10MB of free physical memory. The remaining physical RAM is used by the operating system and SQL Server.
Memory Object: Pages/Sec: Measures the number of pages per second that are paged out from RAM to disk. Higher the value, higher will be I/O activities and will result in decrease in performance. If you have only SQL server application running on the server then in most cases this value should be near zero. However you don’t see much performance degradation until it is 20, when SQL Server is not the only application. Above 20, it is an indication to have more RAM on the server.
SQLServer:Memory Manager: Total Server Memory (KB):Tells you how much memory your SQL Server is currently using.
SQLServer:Memory Manager: Target Server Memory (KB):Tells you howmuch memory SQL Server would like to use to operate efficiently. If the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory.
SQL Server Buffer Manager Object: Cache Size (pages): Multiply this value by 8K to get the value of physical RAM devoted to SQL Server data cache. If this value is less than what you expect than SQL Server is starving on the memory and is not utilizing the available physical RAM.
Process: Working Set: shows the amount of memory used by process. If this number is consistently below Min Server Memory and Max Server Memory then SQL Server is configured to use too much memory.
When the system is running out of memory, it will have higher paging and disk I/O. You can measure the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. You can also monitor SQL Server:Buffer Manager:Page reads/sec and SQL Server:Buffer Manager:Page writes/sec performance counters. Check more at: http://msdn2.microsoft.com/en-us/library/ms175903.aspx.
There is a relationship between SQL Server disk I/O and CPU usage. Check the article Monitoring CPU Usage – http://msdn2.microsoft.com/en-us/library/ms178072.aspx for more details.
Following are other very useful articles for troubleshooting SQL Server performance.
SQL Server Memory Monitoring – http://msdn2.microsoft.com/en-us/library/ms176018.aspx. This article also contains the list of performance counters for monitoring SQL Server memory. The Process: Working Set counter shows the amount of memory that is used by a process.
Troubleshooting Performance Problems in SQL Server 2005 – http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
*/
–USE master;
–GO
–EXEC sp_configure ‘show advanced option’, ‘1’;
–reconfigure;
/*
SQLServer:Memory Manager: Target Server Memory (KB) tells you how much memory SQL Server would like to have
in order to operate efficiently. This is based on the number of buffers reserved by SQL Server when it is first started up.
SQLServer:Memory Manager: Total Server Memory (KB) tells you how much the mssqlserver service is currently using.
This includes the total of the buffers committed to the SQL Server BPool and the OS buffers of the type “OS in Use”.
If, over time, the SQLServer:Memory Manager: Total Server Memory (KB) counter is less than the
SQLServer:Memory Manager: Target Server Memory (KB) counter, then this means that SQL Server has enough
memory to run efficiently. On the other hand, if the SQLServer:Memory Manager: Total Server Memory (KB)
counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this
indicates that SQL Server may be under memory pressure and could use access to more physical memory.
[7.0, 2000] Updated 5-25-2005
*/
–EXEC sp_configure ‘show advanced option’, ‘1’;
–RECONFIGURE;
set nocount on;
DECLARE @init numeric(20,2) , @final numeric(20,2) ;
DECLARE @pgrd1 numeric(20,2) , @pgrd2 numeric(20,2) ;
DECLARE @comp1 numeric(20,2) , @comp2 numeric(20,2) ;
DECLARE @batc1 numeric(20,2) , @batc2 numeric(20,2) ;
DECLARE @frls1 numeric(20,2) , @frls2 numeric(20,2) ;
DECLARE @flsc1 numeric(20,2) , @flsc2 numeric(20,2) ;
DECLARE @lzwr1 numeric(20,2) , @lzwr2 numeric(20,2) ;
DECLARE @ckpt1 numeric(20,2) , @ckpt2 numeric(20,2) ;
DECLARE @frec1 numeric(20,2) , @frec2 numeric(20,2) ;
DECLARE @send1 numeric(20,2) , @send2 numeric(20,2) ;
DECLARE @start_time datetime , @final_time datetime , @count numeric(20,2) ;
DECLARE @upminutes bigint , @MaxSrvMB_running int;
declare @mbrd1 numeric (20,2), @mbwt1 numeric(20,2);
declare @mbrd2 numeric (20,2), @mbwt2 numeric(20,2);
declare @tbl Table (opt nvarchar(35), minimum int, maximum int, config_value int , MaxSrvMB_running int)
select @upminutes = datediff(mi,login_time,getdate()) from master.dbo.sysprocesses where spid = 1
select
@mbrd1 = suM( fs.num_of_bytes_read)/1024 ,
@mbwt1 = sum( fs.num_of_bytes_written)/1024
from sys.dm_io_virtual_file_stats(NULL, NULL) fs
SELECT @init = cntr_value , @start_time = getdate ( ) FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘SQL Re-Compilations/sec%’ ;
SELECT @comp1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘SQL Compilations/sec%’ ;
SELECT @batc1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Batch Requests/sec%’ ;
SELECT @frls1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Free list Stalls/sec%’ ;
SELECT @pgrd1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Page Reads/sec%’ ;
SELECT @flsc1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Full Scans/sec%’ ;
SELECT @lzwr1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Lazy writes/sec%’ ;
SELECT @ckpt1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Checkpoint pages/sec%’ ;
SELECT @frec1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Forwarded Records/sec%’ ;
SELECT @send1 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Send I/O bytes/sec%’ ;
waitfor delay ’00:00:02′;
–#8 … Get all the /sec counters’ final values…
select
@mbrd2 = suM( fs.num_of_bytes_read)/1024 ,
@mbwt2 = sum( fs.num_of_bytes_written)/1024
from sys.dm_io_virtual_file_stats(NULL, NULL) fs
SELECT
@final = cntr_value ,
@final_time = getdate ( )
FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘SQL Re-Compilations/sec%’ ;
SELECT @comp2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘SQL Compilations/sec%’ ;
SELECT @batc2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Batch Requests/sec%’ ;
SELECT @frls2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Free list Stalls/sec%’ ;
SELECT @pgrd2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Page Reads/sec%’ ;
SELECT @flsc2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Full Scans/sec%’ ;
SELECT @lzwr2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Lazy writes/sec%’ ;
SELECT @ckpt2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Checkpoint pages/sec%’ ;
SELECT @frec2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Forwarded Records/sec%’ ;
SELECT @send2 = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE ‘Send I/O bytes/sec%’ ;
SET @count = datediff ( ss , @start_time , @final_time ) ;
SELECT @COUNT = CASE @COUNT WHEN 0 THEN 1 ELSE @count END;
insert into @tbl exec sp_configure ‘max server memory (MB)’
select @MaxSrvMB_running = MaxSrvMB_running from @tbl
–insert memoryUsage
select
getdate() DataDate,
@MaxSrvMB_running Config_MaxSrvMB,
physical_memory_in_bytes/1024/1024 as MemMB,virtual_memory_in_bytes/1024/1024 as virMemMB,
( SELECT
‘%signal (cpu) waits’ = CAST ( 100.0 * SUM ( signal_wait_time_ms ) / SUM ( wait_time_ms ) AS numeric(20,2) )
FROM sys.dm_os_wait_stats ) ‘%signal(cpu)waits’
,( SELECT
CONVERT ( DEC(4,3),ROUND ( CAST ( A.cntr_value1 AS numeric ) / CAST ( B.cntr_value2 AS numeric ) , 3 ) ) AS BufCache_HitRatio
FROM
( SELECT cntr_value AS cntr_value1 FROM sys.dm_os_performance_counters
WHERE
object_name LIKE ‘%Buffer Manager%’
AND counter_name = ‘Buffer cache hit ratio’ ) AS A ,
( SELECT cntr_value AS cntr_value2 FROM sys.dm_os_performance_counters
WHERE
object_name LIKE ‘%Buffer Manager%’
AND counter_name = ‘Buffer cache hit ratio base’ ) AS B ) AS [BufCache_Hit%]
,(select cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) =’Target Server Memory (KB)’ ) /1024
as TargetMemMB
,(select cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) =’Total Server Memory (KB)’ ) /1024
as TotlUsedMemMB –Used bpool_committed instead
,bpool_committed * 8 / 1024 as UsedMemMb
,(select cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) =’Target Server Memory (KB)’ ) /1024
-(select cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) =’Total Server Memory (KB)’ ) /1024 as AvlMemMB
,(select cntr_value From sys.dm_os_performance_counters where rtrim (counter_name) =’Page life expectancy’ and object_name like ‘%Buffer Manager%’) as PageLife
,CAST ( ( @frls2 – @frls1 ) / @count AS numeric(20,2) ) AS ‘Free List Stalls/sec’
,CAST ( ( @frls2 / @upminutes ) AS numeric(20,2) ) AS ‘Free List Stalls/SecHist’
,CAST ( ( @lzwr2 – @lzwr1 ) / @count AS numeric(20,2) ) AS ‘Lazy Writes/sec’
,CAST ( ( @pgrd2 – @pgrd1 ) / @count AS numeric(20,2) ) AS ‘Page Reads/sec’
,CAST ( ( @ckpt2 – @ckpt1 ) / @count AS numeric(20,2) ) AS ‘Checkpoint pages/sec’
,CAST ( ( @frec2 – @frec1 ) / @count AS numeric(20,2) ) AS ‘Forwarded Recs/sec’
,CAST ( ( @flsc2 – @flsc1 ) / @count AS numeric(20,2) ) AS ‘Full scans/sec’
— ,CAST ( ( @send2 – @send1 ) / @count / 1024 / 1024 AS numeric(20,2) ) AS ‘Sent I/O Mbytes/sec’ ;
,max_workers_count as Wrkrs
,( SELECT SUM(single_pages_kb + multi_pages_kb) /1024 FROM sys.dm_os_memory_clerks WHERE name = ‘TokenAndPermUserStore’)as TokenPermUserStore
–The above should be < 200MB
— into memoryUsage
,cast( @mbrd2-@mbrd1 as int) as IO_readMb
,cast( @mbwt2-@mbwt1 as int) as IO_writeMb
from sys.dm_os_sys_info
/*
drop table memoryUsage
select * From memoryUsage order by datadate desc
select getdate()
select distinct object_name FROM sys.dm_os_performance_counters
select * from sys.dm_os_performance_counters where object_name like ‘MSSQL$I1A:Memory Manager%’
SELECT object_name, counter_name, cntr_value AS cntr_value1
, * FROM sys.dm_os_performance_counters
WHERE
–object_name LIKE ‘%Buffer Manager%’
object_name LIKE ‘%Process%’
–counter_name like ‘%private%’
SELECT object_name, counter_name, cntr_value AS cntr_value1
, * FROM sys.dm_os_performance_counters
where cntr_type = 272696576
*/