Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

SQL – Find CPU,Memory,Connections, VLF’s,I/O of all the Databases

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
(
[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
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 * FROM @Aggregate_IO_Statistics

DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;

********************************************************************************

Output -

Metrics


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...