Archive

Posts Tagged ‘SQL Script’

BI SQL # 200 : SQL Server DBA Scripts : Various SQL Server IO Statistics

November 16, 2013 Leave a comment

Hi Folks,

In this article we are going to cover the Various SQL Server IO Statistics.

In this post we are going to discuss following points:

  • Problem Statement of SQL Script:
  • Description of SQL Script:
  • SQL Script Output Column
  • SQL Script Code
  • SQL Script Output Screenshot
  • User Level to execute

    Problem Statement of SQL Script:

    Find SQL Server IO Statistics

    Description of SQL Script:

    This script will help in maintain and check the drive\disk space and read\write information of current system.

    SQL Script Output Column

    image

SQL Script Code 

WITH IOT
AS -- Total sums of all properties. 
    (
    SELECT SUM(IOS.num_of_reads) AS Reads
        ,SUM(IOS.num_of_bytes_read) BytesRead
        ,SUM(IOS.io_stall_read_ms) AS IoStallReadMs
        ,SUM(IOS.num_of_writes) AS Writes
        ,SUM(IOS.num_of_bytes_written) AS BytesWritten
        ,SUM(IOS.io_stall_write_ms) AS IoStallWritesMs
        ,SUM(IOS.io_stall) AS IoStall
        ,SUM(IOS.size_on_disk_bytes) SizeOnDisk
    FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) AS IOS
    )
    ,IOF
AS (
    SELECT DBS.NAME AS DatabaseName
        ,MF.NAME AS [FileName]
        ,MF.type_desc AS FileType
        ,SUBSTRING(MF.physical_name, 1, 3) AS Drive
        ,CASE 
            WHEN DBS.NAME IN (
                    'master'
                    ,'model'
                    ,'msdb'
                    ,'tempdb'
                    )
                THEN 1
            ELSE 0
            END AS IsSystemDB
        ,IOS.*
    FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) AS IOS
    INNER JOIN sys.databases AS DBS ON IOS.database_id = DBS.
        database_id
    INNER JOIN sys.master_files AS MF ON IOS.database_id = MF.
        database_id
        AND IOS.file_id = MF.file_id
    )
SELECT IOF.Drive
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.num_of_reads / IOT.
            Reads)) AS [Reads%]
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.num_of_bytes_read / 
            IOT.BytesRead)) AS [BytesRead%]
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.io_stall_read_ms / IOT
            .IoStallReadMs)) AS [IoStallReadMs%]
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.num_of_writes / IOT.
            Writes)) AS [Writes%]
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.num_of_bytes_written 
            / IOT.BytesWritten)) AS [BytesWritten%]
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.io_stall_write_ms / 
            IOT.IoStallWritesMs)) AS [IoStallWritesMs%]
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.io_stall / IOT.IoStall
        )) AS [IoStall%]
    ,CONVERT(NUMERIC(5, 2), SUM(100.0 * IOF.size_on_disk_bytes / 
            IOT.SizeOnDisk)) AS [SizeOnDisk%]
FROM IOF
CROSS APPLY IOT
GROUP BY IOF.Drive
ORDER BY IOF.Drive;

SQL Script Output Screenshot

image

User Level to execute

Categories: Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar Tags: , , , , , , , , , , , ,