server-stats-summary.sql

USE MASTER 
GO 
 
 
DECLARE @DML1 nvarchar(MAX), 
@DML2 nvarchar(MAX), 
@DML3 nvarchar(MAX), 
@DML4 nvarchar(MAX) 
 
DECLARE @Aggregate_IO_Statistics TABLE 
( 
[I/O Rank] [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, 
[I/O Percent] [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 [I/O Rank], [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 [I/O Percent] 
FROM Aggregate_IO_Statistics 
ORDER BY [I/O Rank] 
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 SERVERPROPERTY('MachineName') AS [ComputerName],
@@SERVERNAME as [ServerName], 
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.[I/O Percent],cs.CPUPercent,cm.[Cached Size (MB)]desc 
 
SELECT SERVERPROPERTY('MachineName') AS [ComputerName],
@@SERVERNAME as [ServerName], 
* FROM @Aggregate_IO_Statistics WHERE [I/O Rank] < 6
 
DROP TABLE #VLFInfo; 
DROP TABLE #VLFCountResults;