SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Performance Transparency


Database Performance Transparency

Author
Message
GreyBeard
GreyBeard
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 331
Comments posted to this topic are about the item Database Performance Transparency
Dirk.Hondong
Dirk.Hondong
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 1141
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
jose.g.korndorffer
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 73
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
RichB
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3096 Visits: 1067
Slight weakness for mount points rather than explicit drive letters though.



Ajay Prakash
Ajay Prakash
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 237
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,
www.bhaved.com


Kindest Regards,

Ajay Prakash
cckaa5
cckaa5
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 163
Great! To subscribe to universal opinion.
Thanks

Anton Kruglikov
mtassin
mtassin
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7512 Visits: 72521
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
For tips on how to post your problems
jamunadas45
jamunadas45
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1
Its really good......

böcker | eböcker | kurslitteratur | online böcker | studentlitteratur | bøker | ebøker
Mark Catoe
Mark Catoe
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 164
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
crazy4sql
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4467 Visits: 4514
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 :-D

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search