IF OBJECT_ID('tempdb..#SQL_DBIOTotal') IS NOT NULL drop table #SQL_DBIOTotal IF OBJECT_ID('tempdb..#SQL_DBIO') IS NOT NULL drop table #SQL_DBIO IF OBJECT_ID('tempdb..#SQL_MemCollection') IS NOT NULL drop table #SQL_MemCollection IF OBJECT_ID('tempdb..#SQL_CPUCollection') IS NOT NULL drop table #SQL_CPUCollection IF OBJECT_ID('tempdb..#SQL_CollectionStatus') IS NOT NULL drop table #SQL_CollectionStatus declare @CollectionTime int declare @Current_Sample_ID Bigint             set @collectiontime=5 -- in minutes /*******Memory collection table ****/ create table #SQL_MemCollection ( SQL_ColletionTime Datetime, SQLCurrMemUsageMB decimal(12,2), SQLMaxMemTargetMB int, OSTotalMemoryMB int, OSAVAMemoryMB int, PLE int ) /*****Cpu collection table *****/ create table #SQL_CPUCollection ( SqlSerCpuUT int, SystemIdle int, OtherProCpuUT int, Collectiontime datetime ) /****** Create SQL_CollectionStatus Table ******/ CREATE TABLE #SQL_CollectionStatus( [JobStatus] [nvarchar](10) NOT NULL, [SPID] [int] NOT NULL, [CollectionStartTime] [datetime] NOT NULL, [CollectionEndTime] [datetime] NULL, [Max_Sample_ID] [bigint] NOT NULL, [Current_Sample_ID] [bigint] NOT NULL ) ON [PRIMARY] /****** Insert Data -- INCLUDES VARIABLE FROM PARENT SCRIPT -- ******/ Declare @Total_Samples bigint Select @Total_Samples = @collectiontime INSERT #SQL_CollectionStatus (JobStatus, SPID, CollectionStartTime, Max_Sample_ID, Current_Sample_ID) SELECT 'Running',@@SPID,GETDATE(),@Total_Samples,0; -- /****** Create SQL_DBIOTotal Table ******/ CREATE TABLE #SQL_DBIOTotal( [Sample_ID] [bigint] NOT NULL, [Database_ID] [int] NULL, [DBName] [nvarchar](400) NOT NULL, [Read] [bigint] NOT NULL, [Written] [bigint] NOT NULL, [BRead] [bigint] NOT NULL, [BWritten] [bigint] NOT NULL, [Throughput] [bigint] NOT NULL, [TotalIOPs] [bigint] NOT NULL, [NetPackets] bigint, [CollectionTime] [datetime] NOT NULL ) ON [PRIMARY] -- /****** Create SQL_DBIO Table ******/ CREATE TABLE #SQL_DBIO( [Sample_ID] [bigint] NOT NULL, [Database_ID] [bigint] NOT NULL, [DBName] [nvarchar](400) NOT NULL, -- [MBRead] [real] NOT NULL, --[MBWritten] [real] NOT NULL, [Read] [bigint] NOT NULL, [Written] [bigint] NOT NULL, [BRead] [bigint] NOT NULL, [BWritten] [bigint] NOT NULL, [TotalB] [bigint] NOT NULL, [TotalIOPs] [bigint] NOT NULL, [Throuput] [bigint] Not Null, [Netpackets] bigint , [CollectionTime] [datetime] NOT NULL ) ON [PRIMARY] while (Select Max_Sample_ID - Current_Sample_ID from #SQL_CollectionStatus) > 0 BEGIN update #SQL_CollectionStatus set Current_Sample_ID = Current_Sample_ID + 1 Set @Current_Sample_ID = (Select Current_Sample_ID from #SQL_CollectionStatus); INSERT #SQL_DBIOTotal                                                  SELECT mf.Database_ID,       @Current_Sample_ID,       physical_name AS [Physical Name], --mf.name, SUM(fs.num_of_reads ), SUM(fs.num_of_writes), SUM(fs.num_of_bytes_read ), SUM(fs.num_of_bytes_written), SUM((fs.num_of_bytes_read)+(fs.num_of_bytes_written)) , SUM(fs.num_of_reads + fs.num_of_writes) , (select Sum(net_packet_size) as Total_net_packets_used from sys.dm_exec_connections), GETDATE() FROM sys.dm_io_virtual_file_stats(default, default) AS fs --INNER JOIN sys.databases d (NOLOCK) ON d.Database_ID = fs.Database_ID inner jOIN sys.master_files AS mf ON fs.file_id = mf.file_id and mf.Database_ID = fs.Database_ID WHERE DB_NAME(mf.database_id) NOT IN ('master','model','msdb', 'distribution', 'ReportServer','ReportServerTempDB') -- and d.state = 0 GROUP BY physical_name ,mf.Database_ID;     -- SELECT -- @Current_Sample_ID, -- d.Database_ID, -- d.name, -- SUM(fs.num_of_reads ), -- SUM(fs.num_of_writes), -- SUM(fs.num_of_bytes_read ), -- SUM(fs.num_of_bytes_written), -- SUM((fs.num_of_bytes_read)+(fs.num_of_bytes_written)) , -- SUM(fs.num_of_reads + fs.num_of_writes) , -- (select Sum(net_packet_size) as Total_net_packets_used from sys.dm_exec_connections), -- GETDATE() --FROM sys.dm_io_virtual_file_stats(default, default) AS fs -- INNER JOIN sys.databases d (NOLOCK) ON d.Database_ID = fs.Database_ID --WHERE d.name NOT IN ('master','model','msdb', 'distribution', 'ReportServer','ReportServerTempDB') --and d.state = 0 --GROUP BY d.name, d.Database_ID; Insert into #SQL_DBIO Select @Current_Sample_ID, DR1.Database_ID, DR1.DBName, DR2.[Read] - DR1.[Read], DR2.[Written] - DR1.[Written], DR2.[BRead] - DR1.[BRead], DR2.[BWritten] - DR1.[BWritten], DR2.Throughput - DR1.Throughput, DR2.TotalIOPs - DR1.TotalIOPs, ((DR2.TotalIOPs - DR1.TotalIOPs)*64)/1024, DR2.NetPackets - DR1.NetPackets, DR2.CollectionTime from #SQL_DBIOTotal as DR1 Inner Join #SQL_DBIOTotal as DR2 ON DR1.Database_ID = DR2.Database_ID where DR1.Sample_ID = @Current_Sample_ID -1 and DR2.Sample_ID = @Current_Sample_ID; DECLARE @ts_now bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info WITH (NOLOCK)); insert into #SQL_CPUCollection SELECT TOP(1) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WITH (NOLOCK) WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%%') AS x) AS y ORDER BY record_id DESC insert into #SQL_MemCollection select x.*,y.*,z.* from (SELECT getdate() as collectionTime,(committed_kb/1024) as Commited,(committed_target_kb/1024) as targetcommited FROM sys.dm_os_sys_info) as x, ( SELECT (total_physical_memory_kb/1024) as totalMem,(available_physical_memory_kb/1024) as AvaiMem FROM sys.dm_os_sys_memory) as y, (SELECT sum(cntr_value)/count(*) as PLE FROM sys.dm_os_performance_counters WHERE counter_name = 'Page Life expectancy' AND object_name LIKE '%buffer node%') as Z waitfor delay '00:00:59' end declare @cpuutilization int declare @one_or_zero int declare @cpurecomm varchar(50) declare @Memrecomm Varchar(50) declare @memutilization int declare @Cpupercentile int declare @MaxMemory int declare @Throughput int declare @totaliops int declare @edition varchar(2) declare @ProductVersion int              declare @servername varchar(100)              select @servername=@@servername ; with Cpu_util (one_or_zero) as ( SELECT case when sqlsercpUut>=80 then 1 else 0 end FROM #SQL_CPUCollection ) select @cpuutilization=count(*)*100/(select count(*) from Cpu_util ) , @one_or_zero=one_or_zero from cpu_util where one_or_zero=1 group by one_or_zero order by 2 desc set @cpuutilization=isnull(@cpuutilization,0) if @cpuutilization>=80 select @cpurecomm='Need To scale compute UP' --,@cpuutilization else If @cpuutilization<80 and @cpuutilization >=30 select @cpurecomm='compute Load is acceptable' --,@cpuutilization else select @cpurecomm='compute can be scaled down ' -- ,@cpuutilization --select @cpuutilization=sum([sqlsercpUut])/count(*) FROM #SQL_CPUCollection -- if @cpuutilization>=80 -- select @cpurecomm='Need To scale compute UP' -- ,@cpuutilization -- else If @cpuutilization<80 and @cpuutilization >=30 -- select @cpurecomm='compute Load is acceptable' -- ,@cpuutilization -- else select @cpurecomm='compute can be scaled down '-- ,@cpuutilization declare @95Percentile int select @95Percentile=count(*)* 0.95 from #SQL_CPUCollection ;with cpupercentile as (SELECT row_number () over (order by sqlsercpuUt asc) as rownum, [SqlSerCpuUT] ,[SystemIdle] ,[OtherProCpuUT] ,[Collectiontime] FROM #SQL_CPUCollection ) select @Cpupercentile=[SqlSerCpuUT] from cpupercentile where rownum =@95Percentile declare @Logical_CPU_Count int SELECT @Logical_CPU_Count=cpu_count FROM sys.dm_os_sys_info SELECT top 1 @MaxMemory =[SQLMaxMemTargetMB] FROM #SQL_MemCollection declare @count int --declare @MemUtilization int ;with Memory_intensive (one_or_zero) as ( select ((case when ([SQLCurrMemUsageMB]*100)/[SQLMaxMemTargetMB]>=80 then 1 else 0 end) &(case when (([SQLCurrMemUsageMB]/1024)/4)*300< PLE then 0 else 1 end )) FROM #SQL_MemCollection ) select @count=count(*) from Memory_intensive where one_or_zero=1 group by one_or_zero select @memutilization=isnull(@count*100/(select count(*) from #SQL_MemCollection),0) if @MemUtilization>=80 select @Memrecomm='Need To scale Memory UP' --,@MemUtilization as utilization else If @MemUtilization<80 and @MemUtilization >=50 select @Memrecomm='Memory Load is acceptable' --,@MemUtilization as utilization else select @Memrecomm='Memory can be scaled down ' --,@MemUtilization as utilization SELECT @totaliops=isnull(sum(Totaliops)/60,0) ,@Throughput=isnull(((sum(bread+bwritten)/60)/1048576),0) FROM #SQL_DBIO SELECT @Edition=case WHEN CONVERT(VARCHAR(128),SERVERPROPERTY('Edition')) like 'Standard%' Then 'SE' WHEN CONVERT(VARCHAR(128),SERVERPROPERTY('Edition')) like 'Enterprise%' Then 'EE' end , @ProductVersion=CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN '11' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN '12' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN '13' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN '14' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN '15' Else '12' end select @servername as Servername ,@cpurecomm as cpurecomm, @cpuutilization as cpuUtilization,@Cpupercentile as cpu95percentile,@Logical_CPU_Count as CU_count,@MaxMemory as MaxMemory ,@Memrecomm as Memecomm,@MemUtilization as Memutilization,@Throughput as throughput,@totaliops as Totaliops,@edition as edition, @ProductVersion as ProductVersion,@collectiontime as collectiontime