Export SQL Server info about disk, cpu and memory utilization

Question

I am trying to use logstash to read log files. When it come to monitor SQL Server, is there anyway to export SQL Server Database Disk Usage data to a log file?

Answer 1

Disk usage (per database):

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
      database_id INT PRIMARY KEY
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT
      d.database_id
    , d.name
    , d.state_desc
    , t.total_size
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
FROM (
    SELECT
          database_id
        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
ORDER BY t.total_size DESC

Disk activity:

IF OBJECT_ID('tempdb.dbo.#tt', 'U') IS NULL
    CREATE TABLE #tt (
        tm DATETIME PRIMARY KEY,
        total_read NUMERIC(28, 2),
        total_written NUMERIC(28, 2),
        total_io_count BIGINT,
        total_io_wait BIGINT
    )

DECLARE @current_collection_time DATETIME = GETDATE()

INSERT INTO #tt
SELECT
    @current_collection_time,
    SUM(num_of_bytes_read / 1024.0 / 1024.0 ),
    SUM(num_of_bytes_written / 1024.0 / 1024.0 ),
    SUM(num_of_reads + num_of_writes),
    SUM(io_stall)
FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT)

DECLARE @previous_collection_time DATETIME
SELECT @previous_collection_time = tm
FROM #tt
WHERE tm < @current_collection_time

DECLARE @interval_ms INT = DATEDIFF(MILLISECOND, @previous_collection_time, @current_collection_time);

SELECT
    read_mb_sec = (c.total_read - p.total_read) * 1000 / @interval_ms,
    write_mp_sec = (c.total_written - p.total_written) * 1000 / @interval_ms,
    response_time_ms =
        CASE
            WHEN (c.total_io_count - p.total_io_count) = 0
                THEN 0
            ELSE (c.total_io_wait - p.total_io_wait) / (c.total_io_count - p.total_io_count)
        END
FROM #tt c
CROSS JOIN #tt p
WHERE c.tm = @current_collection_time
    AND p.tm = @previous_collection_time

DELETE FROM #tt
WHERE tm < @current_collection_time;

Memory utilization:

DECLARE @sql_memory_mb INT
SELECT @sql_memory_mb = physical_memory_in_use_kb / 1024
FROM sys.dm_os_process_memory

SELECT
    total_memory_mb = total_physical_memory_kb / 1024,
    memory_in_use_mb = (total_physical_memory_kb - available_physical_memory_kb) / 1024,
    sql_memory_mb = @sql_memory_mb
FROM sys.dm_os_sys_memory

CPU utilization:

SELECT
    cpu_total =
        CASE WHEN cpu_sql > cpu_total AND cpu_sql <= 99.
            THEN cpu_sql
            ELSE cpu_total
        END,
    cpu_sql
FROM (
    SELECT cpu_total = 100 - x.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle/text())[1]', 'TINYINT')
    FROM (
        SELECT TOP(1) [timestamp], x = CONVERT(XML, record)
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%'
    ) t
) x
CROSS JOIN (
    SELECT
        cpu_sql = (
                MAX(CASE WHEN counter_name = 'CPU usage %' THEN t.cntr_value * 1. END) /
                MAX(CASE WHEN counter_name = 'CPU usage % base' THEN t.cntr_value END)
            ) * 100
    FROM (
        SELECT TOP(2) cntr_value, counter_name
        FROM sys.dm_os_performance_counters
        WHERE counter_name IN ('CPU usage %', 'CPU usage % base')
            AND instance_name = 'default'
    ) t
) t

This queries you can run from jobs and put required information into tables.

How to load data into file:

/*
    EXEC sys.sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    EXEC sys.sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
*/

DECLARE @sql NVARCHAR(4000) = 'bcp "SELECT * FROM dbo.disk_activity" queryout "D:\sample.html" -S ' + @@servername + ' -T -w -r -t'
EXEC sys.xp_cmdshell @sql