Database Performance Transparency

  • GreyBeard

    Mr or Mrs. 500

    Points: 535

    Comments posted to this topic are about the item Database Performance Transparency

  • Dirk.Hondong

    Ten Centuries

    Points: 1024

    Hey Ron,

    first of all: thanks for your article.

    Only one little thing. The pdf you´ve mentioned (SQL Server Instance Health Monitoring Tool) cannot be opened.

    I get a message from Adobe Reader that the file does not start with "%-PDF"

    Regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • jose.g.korndorffer

    SSC Rookie

    Points: 44

    Hi,

    nice article and I must say that you are right about performance transparency. In the past I've created WMI providers to feed SCOM and automatize the gathering of such information.

    Congrats on your article,

    Gerardo Korndorffer

  • RichB

    SSCrazy Eights

    Points: 9651

    Slight weakness for mount points rather than explicit drive letters though.

  • Ajay Prakash

    SSC Eights!

    Points: 891

    Very nice article and great output. Just a suggestion

    In your script, to caluculate uptime (for variable @vOnline_Since), it might be better to use 'tempdb' instead of 'perfstat' database in the WHERE clause because tempdb gets created everytime SQL Server Service gets started, and therefore you can rely on this for SQL Server uptime.

    Regards,

    http://www.bhaved.com


    Kindest Regards,

    Ajay Prakash

  • cckaa5

    Valued Member

    Points: 71

    Great! To subscribe to universal opinion.

    Thanks

    Anton Kruglikov

  • mtassin

    SSC-Insane

    Points: 23099

    Doesn't work with Clustered instances. Or with default instances

    SET @instanceName = CONVERT (VARCHAR,SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')) + '\' + CONVERT(VARCHAR,SERVERPROPERTY('InstanceName'))

    Will return Null for a default instance and NULL out @instanceName

    It will also return the physical node the SQL instance is running on in a cluster, which is not the name on the network to connect to.

    Sadly this works better for clustered default instances

    SET @instanceName = @@SERVERNAME + ISNULL('\' + CONVERT(VARCHAR,SERVERPROPERTY('InstanceName')),'')



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • jamunadas45

    Grasshopper

    Points: 21

    Its really good......

  • Mark Catoe

    SSC Enthusiast

    Points: 156

    I would like to get some information (windows service) on how to post the output to a web server and into a particular folder if possible. Thank you for Performance Transparency advice.

  • crazy4sql

    SSCoach

    Points: 19590

    Modified this script a bit,

    1)

    to give the correct information on the sql server up time in two variable mentioned in script.

    So in variable "@vOnline_Since" OR "OnlineSince" you will get the result in datetime as when the SQL was last came online.

    and

    "@vUptime_Days" OR "UptimeDays" is again based on the same calculation but tells the stamina of the server as since when its running 😀

    2) You will get problem in bcp syntax if folder "c:\dbahtml\" doesn't exist. So either create the folder structure first or modify the value to C:, as I did in my below syntax

    ok, here is the script :-

    IF OBJECT_ID('tempdb..#TEMPhtml2') IS NOT NULL

    BEGIN

    DROP TABLE #TEMPhtml2

    END

    CREATE TABLE #TEMPhtml2

    (

    [columns] VARCHAR (MAX)

    )

    DECLARE @finalhtmlout VARCHAR(MAX)

    DECLARE @columns VARCHAR(8000)

    DECLARE @colHeader VARCHAR(8000)

    DECLARE @Final VARCHAR(8000)

    DECLARE @clientName VARCHAR(50)

    DECLARE @instanceName VARCHAR(50)

    DECLARE @col VARCHAR(MAX)

    /******************************************************************************************/

    /* DEBUG OUTPUT CONTROL */

    DECLARE @DEBUG SMALLINT = 0 --> 0 == OFF 1 == VERBOSE 2 == FINAL HTML ONLY

    /******************************************************************************************/

    /******************************************************************************************/

    /* CLIENT NAME */

    SET @clientName = 'Test Client'

    /******************************************************************************************/

    SET @instanceName = CONVERT (VARCHAR,SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')) + '\' + CONVERT(VARCHAR,SERVERPROPERTY('InstanceName'))

    --initialize HTML page

    SET @finalhtmlout = ' <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <body> '

    SET @finalhtmlout = @finalhtmlout + '<h1>' + @clientName + '</h1>' + '<h2>SQL Server Performance Snapshot</h2>'

    SET @finalhtmlout = @finalhtmlout + '<h3>'+ CONVERT(VARCHAR,GETDATE(),100) + '</h3><br />'

    /*********************************************************************************************/

    --get General Info

    --dump contents of the temp table

    TRUNCATE TABLE #TEMPhtml2

    --drop the temp table for this data if it already exists

    IF OBJECT_ID('tempdb..#DBA_GenInfo') IS NOT NULL

    BEGIN

    DROP TABLE #DBA_GenInfo

    END

    --create the temp table for this data

    CREATE TABLE #DBA_GenInfo

    (

    NetbiosName VARCHAR(50),

    SERVERNAME VARCHAR(50),

    Edition VARCHAR(50),

    [VERSION] VARCHAR(50),

    [LEVEL] VARCHAR(50),

    OnlineSince VARCHAR(50),

    UptimeDays VARCHAR(9)

    )

    --declare any variables needed for data collection

    DECLARE @vDate_Now AS DATETIME

    DECLARE @vOnline_Since AS VARCHAR (19)

    DECLARE @vUptime_Days AS INT

    DECLARE @vDate_24_Hours_Ago AS DATETIME

    --insert the data into the temp table defined above

    SELECT

    @vOnline_Since = CONVERT (NVARCHAR (19), sqlserver_start_time, 120)

    ,@vUptime_Days = DATEDIFF(day,sqlserver_start_time,getdate())

    FROM

    sys.dm_os_sys_info DB

    SET @vDate_24_Hours_Ago = GETDATE ()-1

    SET @vDate_Now = @vDate_24_Hours_Ago+1

    INSERT INTO #DBA_GenInfo

    SELECT

    CONVERT (VARCHAR,SERVERPROPERTY ('ComputerNamePhysicalNetBIOS'))

    ,CONVERT (VARCHAR,@@SERVERNAME)

    ,REPLACE (CONVERT (VARCHAR, SERVERPROPERTY ('Edition')),' Edition','')

    ,CONVERT (VARCHAR,SERVERPROPERTY ('ProductVersion'))

    ,CONVERT (VARCHAR,SERVERPROPERTY ('ProductLevel'))

    ,CONVERT (VARCHAR,@vOnline_Since)

    ,CONVERT (VARCHAR,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, @vUptime_Days), 1)), 4, 15)))

    IF @DEBUG = 1

    BEGIN

    SELECT * FROM #DBA_GenInfo

    END

    --get the column names and store them in @columns

    SELECT @columns = COALESCE(@columns + ' + '' </td><td> '' + ', '') +

    'convert(varchar(100),isnull(' + c.name +','' ''))'

    FROM tempdb.sys.columns c

    INNER JOIN tempdb.sys.tables t ON c.object_id = t.object_id

    WHERE t.name LIKE '#DBA_GenInfo%'

    IF @DEBUG = 1

    BEGIN

    SELECT @columns AS [columns]

    END

    --Setup the html column header

    SET @colHeader = '<tr bgcolor=#EDFEDF align=center> <TR><TH><H3>

    Server / Instance Information</H3></TH></TR>'

    SELECT @colHeader = @colHeader + '<td><b> ' + c.name + '</b></td>'

    FROM tempdb.sys.columns c

    INNER JOIN tempdb.sys.tables t ON c.object_id = t.object_id

    WHERE t.name LIKE '#DBA_GenInfo%'

    SET @colHeader=@colHeader + '</tr>'

    IF @DEBUG = 1

    BEGIN

    SELECT @colHeader AS [columnHeader]

    END

    --get the data selection and insertion statements ready

    SET @Final = 'insert into #TEMPhtml2 Select ''<tr><td>'' + ' + @columns + '+ ''</td></tr> '' from #DBA_GenInfo '

    IF @DEBUG = 1

    BEGIN

    SELECT @final AS FINAL

    END

    --execute the select

    EXECUTE( @Final )

    IF @DEBUG = 1

    BEGIN

    SELECT @colHeader AS COLHEADER

    SELECT * FROM #TEMPhtml2 AS TEMPHTML2

    END

    --initialize the HTML table

    SET @finalhtmlout = @finalhtmlout + ' <style type="text/css" media="all"> table { margin-bottom: 2em; border-collapse: collapse } td,th {border= 1 solid #999; padding: 0.2em 0.2em; font-size: 12;} </style> <table width="100%"> ' + @colHeader

    IF @DEBUG = 1

    BEGIN

    SELECT @finalhtmlout AS HTMLoutput1

    END

    -- insert values for retrieved metric in HTML format

    SET @col = NULL

    DECLARE HTML_Cur CURSOR

    FOR SELECT [columns] FROM #TEMPhtml2

    OPEN HTML_Cur

    FETCH NEXT FROM HTML_Cur

    INTO @col

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @finalhtmlout= @finalhtmlout + @col + '</td></tr>'

    FETCH NEXT FROM HTML_Cur

    INTO @col

    END

    CLOSE HTML_Cur

    DEALLOCATE HTML_Cur

    -- clean up locals

    DROP TABLE #DBA_GenInfo

    SET @columns = NULL

    SET @colHeader = NULL

    SET @Final = NULL

    /***********************************************************************************************/

    -- get io stats

    --dump contents of the temp table

    TRUNCATE TABLE #TEMPhtml2

    IF OBJECT_ID('tempdb..#DBA_PLEStats') IS NOT NULL

    BEGIN

    DROP TABLE #DBA_IOStats

    END

    --get IO stats and generate HTML for data

    CREATE TABLE #DBA_IOStats

    (

    [Fname] VARCHAR(25),

    [Platter] VARCHAR(5),

    [Database_Name] VARCHAR(75),

    [Avg_IO_Stall_ms] VARCHAR(15),

    [Number_of_Reads] VARCHAR(15),

    [Number_of_Writes] VARCHAR(15),

    [Number_of_Bytes_Read] VARCHAR(15),

    [Number_of_Bytes_Written] VARCHAR(15),

    [Total_IO_Stall] VARCHAR(15),

    [Total_IO] VARCHAR(15)

    )

    DECLARE @TotalIO BIGINT ,

    @TotalBytes BIGINT ,

    @TotalStall BIGINT

    SELECT @TotalIO = SUM(num_of_reads + num_of_writes) ,

    @TotalBytes = SUM(num_of_bytes_read + num_of_bytes_written) ,

    @TotalStall = SUM(io_stall)

    FROM sys.dm_io_virtual_file_stats(-1, -1)

    INSERT INTO #DBA_IOStats ([Fname],[Platter],[Database_Name],[Avg_IO_Stall_ms],[Number_of_Reads],

    [Number_of_Writes],[Number_of_Bytes_Read],[Number_of_Bytes_Written],[Total_IO_Stall],[Total_IO])

    SELECT LOWER(SUBSTRING(physical_name, LEN(physical_name) - 2, 4)) ,

    UPPER(SUBSTRING(physical_name, 1, 3)) ,

    [DbName] = DB_NAME([f].[database_id]) ,

    CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads

    + num_of_writes ) AS NUMERIC(10,

    1)) ,

    [num_of_reads] ,

    [num_of_writes] ,

    [num_of_bytes_read] ,

    [num_of_bytes_written] ,

    [io_stall] ,

    [TotalIO] = ( num_of_reads + num_of_writes )

    FROM sys.dm_io_virtual_file_stats(-1, -1) [IO]

    INNER JOIN sys.master_files f ON [IO].database_id = f.database_id

    AND [IO].[file_id] = f.[file_id]

    IF @DEBUG = 1

    BEGIN

    SELECT * FROM #DBA_IOStats

    END

    ------Prepare column statement

    SELECT @columns = COALESCE(@columns + ' + '' </td><td> '' + ', '') +

    'convert(varchar(100),isnull(' + c.name +','' ''))'

    FROM tempdb.sys.columns c

    INNER JOIN tempdb.sys.tables t ON c.object_id = t.object_id

    WHERE t.name LIKE '#DBA_IOStats%'

    IF @DEBUG = 1

    BEGIN

    SELECT @columns AS [columns]

    END

    ----Prepare column Header

    SET @colHeader = '<tr bgcolor=#EDFEDF align=center> <TR><TH><H3>

    File IO Statistics</H3></TH></TR>'

    SELECT @colHeader = @colHeader + '<td><b> ' + c.name + '</b></td>'

    FROM tempdb.sys.columns c

    INNER JOIN tempdb.sys.tables t ON c.object_id = t.object_id

    WHERE t.name LIKE '#DBA_IOStats%'

    SET @colHeader=@colHeader + '</tr>'

    IF @DEBUG = 1

    BEGIN

    SELECT @colHeader AS [columnHeader]

    END

    ------prepare final output

    SET @Final = 'insert into #TEMPhtml2 Select ''<tr><td>'' + ' + @columns + '+ ''</td></tr> '' from #DBA_IOStats '

    IF @DEBUG = 1

    BEGIN

    SELECT @final AS FINAL

    END

    EXECUTE( @Final )

    IF @DEBUG = 1

    BEGIN

    SELECT @colHeader AS COLHEADER

    SELECT * FROM #TEMPhtml2 AS TEMPHTML2

    END

    --initialize table

    SET @finalhtmlout = @finalhtmlout + ' <style type="text/css" media="all"> table { margin-bottom: 2em; border-collapse: collapse } td,th {border= 1 solid #999; padding: 0.2em 0.2em; font-size: 12;} </style> <table width="100%"> ' + @colHeader

    IF @DEBUG = 1

    BEGIN

    SELECT @finalhtmlout AS HTMLoutput1

    END

    -- insert values for retrieved metric in HTML format

    SET @col=NULL

    DECLARE HTML_Cur CURSOR

    FOR SELECT [columns] FROM #TEMPhtml2

    OPEN HTML_Cur

    FETCH NEXT FROM HTML_Cur

    INTO @col

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @finalhtmlout= @finalhtmlout + @col + '</td></tr>'

    FETCH NEXT FROM HTML_Cur

    INTO @col

    END

    CLOSE HTML_Cur

    DEALLOCATE HTML_Cur

    -- clean up locals

    DROP TABLE #DBA_IOStats

    SET @columns = NULL

    SET @colHeader = NULL

    SET @Final = NULL

    /**********************************************************************************************/

    -- closes the HTML file

    SET @finalhtmlout= @finalhtmlout + ' </table></body></htmL>'

    IF OBJECT_ID('tempdb..##tempOut') IS NOT NULL

    BEGIN

    DROP TABLE ##tempOut

    END

    CREATE TABLE ##tempOut

    (

    html VARCHAR(MAX)

    )

    INSERT INTO ##tempOut

    SELECT @finalhtmlout

    -- write html file to disk

    DECLARE @filename VARCHAR(75)

    /******************************************************************************************/

    /* File Name */

    SET @filename = 'c:\'

    /******************************************************************************************/

    SET @filename = @filename + CONVERT(VARCHAR,SERVERPROPERTY('InstanceName')) + '_' + CONVERT(VARCHAR,GETDATE(),112) + '.html'

    print 'filename is :-'+@filename

    DECLARE @string AS NVARCHAR(4000)

    SELECT @string = 'bcp ##tempOut out ' + @filename + ' -T -c -S ' + @instanceName

    print 'string is :-'+@string

    EXEC master.dbo.xp_cmdshell @string -- , no_output

    IF @DEBUG >= 1

    BEGIN

    SELECT @string AS [BCP command]

    SELECT @filename AS [FileName]

    SELECT @finalhtmlout AS [Final HTML OUTPUT]

    END

    DROP TABLE ##tempOut

    ----------
    Ashish

  • byoyo143

    Grasshopper

    Points: 13

    The services differentiate over all banks Missed call banking is valuable for primary banking works out. Banks don’t charge you for the missed calls. In any case, since you need to first profit the SMS office,

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply