Find CPU, Memory, No Of Connections/databases and VLF’s and I/O details in one single script.
You just need to run the below T-SQL in SSMS
Download the code here Metrics
*****************************************************************************
USE MASTER
GO
DECLARE @DML1 nvarchar(MAX),
@DML2 nvarchar(MAX),
@DML3 nvarchar(MAX),
@DML4 nvarchar(MAX)
DECLARE @Aggregate_IO_Statistics TABLE
(
[bigint] NULL,
[Database Name] [nvarchar](128) NULL,
[physicalName] [nvarchar](1) NULL,
[total_num_of_writes] [bigint] NULL,
[total_num_of_bytes_written] [bigint] NULL,
[total_num_of_reads] [bigint] NULL,
[Total I/O (MB)] [decimal](12, 2) NULL,
[decimal](5, 2) NULL
)
SET @DML1=’WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(DM_IO_STATS.database_id) AS [Database Name],
left(f.physical_name, 1) physicalName,
SUM(DM_IO_STATS.num_of_writes) AS total_num_of_writes,
SUM(DM_IO_STATS.num_of_bytes_written) AS total_num_of_bytes_written,
SUM(DM_IO_STATS.num_of_reads) AS total_num_of_reads,
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS [DM_IO_STATS]
inner join sys.master_files f on f.database_id=DM_IO_STATS.database_id and f.file_id=DM_IO_STATS.file_id
GROUP BY DM_IO_STATS.database_id,left(f.physical_name, 1))
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS , [Database Name],physicalName,total_num_of_writes,
total_num_of_bytes_written,total_num_of_reads,io_in_mb AS [Total I/O (MB)],
CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS
FROM Aggregate_IO_Statistics
ORDER BY
OPTION (RECOMPILE)’
INSERT INTO @Aggregate_IO_Statistics
EXEC sp_executesql @DML1
–select * from @Aggregate_IO_Statistics
–SELECT * FROM @Aggregate_IO_Statistics
DECLARE @Userconnections TABLE
(
[DatabaseName] [nvarchar](128) NULL,
[NumberOfConnections] [int] NULL
)
SET @DML2=’
SELECT DB_NAME(dbid),COUNT(*) DatabaseName FROM sys.sysprocesses –where kpid>0
group by DB_NAME(dbid)
ORDER BY DB_NAME(dbid) DESC OPTION (RECOMPILE)
‘
–SELECT DB_NAME(dbid),COUNT(*) DatabaseName FROM sys.sysprocesses –where kpid>0
–group by DB_NAME(dbid)
–ORDER BY DB_NAME(dbid) DESC OPTION (RECOMPILE)
INSERT INTO @Userconnections
EXEC sp_executesql @DML2
–SELECT * FROM @Userconnections
DECLARE @CacheMemoryDB TABLE(
[Database Name] [nvarchar](128) NULL,
[Cached Size (MB)] [decimal](10, 2) NULL
)
SET @DML3=’SELECT DB_NAME(database_id) AS [Database Name],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 — system databases
AND database_id <> 32767 — ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE)’
INSERT INTO @CacheMemoryDB
EXEC sp_executesql @DML3
–SELECT * FROM @CacheMemoryDB
DECLARE @DB_CPU_Stats TABLE (
[row_num] [bigint] NULL,
[DatabaseName] [nvarchar](128) NULL,
[CPU_Time_Ms] [bigint] NULL,
[CPUPercent] [decimal](5, 2) NULL
)
SET @DML4=’WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N”dbid”) AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 — system databases
AND DatabaseID <> 32767 — ResourceDB
ORDER BY row_num OPTION (RECOMPILE)’
–How many Virtual Log Files or VLFs are present in your log file.
INSERT INTO @DB_CPU_Stats
EXEC sp_executesql @DML4
–SELECT * FROM @DB_CPU_Stats
CREATE TABLE #VLFInfo (FileID int,
FileSize bigint, StartOffset bigint,
FSeqNo bigint, [Status] bigint,
Parity bigint, CreateLSN numeric(38));
CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);
EXEC sp_MSforeachdb N’Use [?];
INSERT INTO #VLFInfo
EXEC sp_executesql N”DBCC LOGINFO([?])”;
INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;
TRUNCATE TABLE #VLFInfo;’
–SELECT DatabaseName, VLFCount FROM #VLFCountResults ORDER BY VLFCount DESC;
SELECT
cs.DatabaseName DatabaseName,
isnull(cs.CPU_Time_Ms,0) CPUTimeMs,
isnull(cs.CPUPercent,0) CPUPercent,
isnull(cm.[Cached Size (MB)],0) CachedSizeMB,
isnull(uc.NumberOfConnections,0) NumberOfConnections,
VR.VLFCount VirtualLogCnt
FROM @DB_CPU_Stats cs
left join @CacheMemoryDB CM on cm.[Database Name]=cs.[DatabaseName]
left join @Userconnections uc on uc.DatabaseName=cs.[DatabaseName]
left join #VLFCountResults VR on Vr.DatabaseName=cs.[DatabaseName]
–order by io.,cs.CPUPercent,cm.[Cached Size (MB)]desc
SELECT * FROM @Aggregate_IO_Statistics
DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;
********************************************************************************
Output -
