Fun With your MSSQL Server.

I really wanted to call it something along the lines of fun with Dick and Jane. However the title got too long and wouldn’t have made any sense.

Yes yet another Microsoft product we are talking about. I gotta write scripts so this one man Web administrator can get some sleep at night.

Here is a fun little SQL script that will give you all sorts of info concerning IO and Buffers and well crap that will only bore most of you.

Works well on MSSQL 2005 and up:

 

DECLARE @TotalIO    BIGINT,
@TotalBytes BIGINT,
@TotalStall BIGINT

SELECT @TotalIO  = SUM(NumberReads + NumberWrites),
@TotalBytes = SUM(BytesRead + BytesWritten),
@TotalStall = SUM(IoStallMS)
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)

SELECT [DbName] = DB_NAME([DbId]),
(SELECT name FROM sys.master_files
WHERE database_id = [DbId]
and FILE_ID = [FileId]) filename,
[%ReadWrites]       = (100 * (NumberReads + NumberWrites) / @TotalIO),
[%Bytes]        = (100 * (BytesRead + BytesWritten) / @TotalBytes),
[%Stall]        = (100 * IoStallMS / @TotalStall),
[NumberReads],
[NumberWrites],
[TotalIO]       = CAST((NumberReads + NumberWrites) AS BIGINT),
[MBsRead]       = [BytesRead] / (1024*1024),
[MBsWritten]        = [BytesWritten] / (1024*1024),
[TotalMBs]      = (BytesRead + BytesWritten) / (1024*1024),
[IoStallMS],
IoStallReadMS,
IoStallWriteMS,
[AvgStallPerIO]     = ([IoStallMS] / ([NumberReads] + [NumberWrites] + 1)),
[AvgStallPerReadIO] = (IoStallReadMS / ([NumberReads] + 1)),
[AvgStallPerWriteIO]= (IoStallWriteMS / ( [NumberWrites] + 1)),

[AvgBytesPerRead]  = ((BytesRead) / (NumberReads + 1)),
[AvgBytesPerWrite] = ((BytesWritten) / (NumberWrites + 1))
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
ORDER BY dbname

copy paste and name it something that makes sense “*.sql” Run it and enjoy your output.

Send me a screenshot of your stats on twitter or facebook

This entry was posted in Fun, Microsoft, SQL/Database. Bookmark the permalink.

Leave a Reply