My favorite script to run across multiple servers (see blog entry). This allows me to get an 50,000 foot view of all my 2005+ servers. It’s a long script…some 175 lines or so.
/* 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())frommaster.dbo.sysprocesseswhere spid = 1
select
@mbrd1 =suM( fs.num_of_bytes_read)/1024 ,
@mbwt1 =sum( fs.num_of_bytes_written)/1024
fromsys.dm_io_virtual_file_stats(NULL,NULL) fs
SELECT @init = cntr_value , @start_time =getdate () FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘SQL Re-Compilations/sec%’;
SELECT @comp1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘SQL Compilations/sec%’;
SELECT @batc1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Batch Requests/sec%’;
SELECT @frls1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Free list Stalls/sec%’;
SELECT @pgrd1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Page Reads/sec%’;
SELECT @flsc1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Full Scans/sec%’;
SELECT @lzwr1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Lazy writes/sec%’;
SELECT @ckpt1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Checkpoint pages/sec%’;
SELECT @frec1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Forwarded Records/sec%’;
SELECT @send1 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Send I/O bytes/sec%’;
waitfordelay’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
fromsys.dm_io_virtual_file_stats(NULL,NULL) fs
SELECT
@final = cntr_value ,
@final_time =getdate ()
FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘SQL Re-Compilations/sec%’;
SELECT @comp2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘SQL Compilations/sec%’;
SELECT @batc2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Batch Requests/sec%’;
SELECT @frls2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Free list Stalls/sec%’;
SELECT @pgrd2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Page Reads/sec%’;
SELECT @flsc2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Full Scans/sec%’;
SELECT @lzwr2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Lazy writes/sec%’;
SELECT @ckpt2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Checkpoint pages/sec%’;
SELECT @frec2 = cntr_value FROM sys.dm_os_performance_countersWHERE counter_name LIKE‘Forwarded Records/sec%’;
SELECT @send2 = cntr_value FROM sys.dm_os_performance_countersWHERE 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;
insertinto @tbl execsp_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 )ASnumeric(20,2))
FROMsys.dm_os_wait_stats)‘%signal(cpu)waits’
,(SELECT
CONVERT (DEC(4,3),ROUND (CAST ( A.cntr_value1 ASnumeric)/CAST ( B.cntr_value2 ASnumeric), 3 ))AS BufCache_HitRatio
FROM
(SELECT cntr_value AS cntr_value1 FROMsys.dm_os_performance_counters
WHERE
object_nameLIKE‘%Buffer Manager%’
AND counter_name =‘Buffer cache hit ratio’)AS A ,
(SELECT cntr_value AS cntr_value2 FROMsys.dm_os_performance_counters
WHERE
object_nameLIKE‘%Buffer Manager%’
AND counter_name =‘Buffer cache hit ratio base’)AS B )AS [BufCache_Hit%]
,(select cntr_value Fromsys.dm_os_performance_counterswherertrim (counter_name)=’Target Server Memory (KB)’)/1024
as TargetMemMB
,(select cntr_value Fromsys.dm_os_performance_counterswherertrim (counter_name)=’Total Server Memory (KB)’)/1024
as TotlUsedMemMB –Used bpool_committed instead
,bpool_committed * 8 / 1024 as UsedMemMb
,(select cntr_value Fromsys.dm_os_performance_counterswherertrim (counter_name)=’Target Server Memory (KB)’)/1024
-(select cntr_value Fromsys.dm_os_performance_counterswherertrim (counter_name)=’Total Server Memory (KB)’)/1024 as AvlMemMB
,(select cntr_value Fromsys.dm_os_performance_counterswherertrim (counter_name)=’Page life expectancy’andobject_namelike‘%Buffer Manager%’)as PageLife
,CAST ( ( @frls2 – @frls1 )/ @count ASnumeric(20,2))AS‘Free List Stalls/sec’
,CAST ( ( @frls2 / @upminutes ) ASnumeric(20,2))AS‘Free List Stalls/SecHist’
,CAST ( ( @lzwr2 – @lzwr1 )/ @count ASnumeric(20,2))AS‘Lazy Writes/sec’
,CAST ( ( @pgrd2 – @pgrd1 )/ @count ASnumeric(20,2))AS‘Page Reads/sec’
,CAST ( ( @ckpt2 – @ckpt1 )/ @count ASnumeric(20,2))AS‘Checkpoint pages/sec’
,CAST ( ( @frec2 – @frec1 )/ @count ASnumeric(20,2))AS‘Forwarded Recs/sec’
,CAST ( ( @flsc2 – @flsc1 )/ @count ASnumeric(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
,( SELECTSUM(single_pages_kb + multi_pages_kb)/1024 FROMsys.dm_os_memory_clerksWHERE name =‘TokenAndPermUserStore’)as TokenPermUserStore
–The above should be < 200MB
— into memoryUsage
,cast( @mbrd2–@mbrd1 asint)as IO_readMb
,cast( @mbwt2–@mbwt1 asint)as IO_writeMb
fromsys.dm_os_sys_info