Getting to Know Virtual File Stats- Additions

Last Post 28 Aug 2007 03:06 PM by keppro. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
keppro
New Member
New Member

--
28 Aug 2007 03:06 PM
The file_stats_report_2000 contains some of traces from SQL Server 2005' system objects, as well as different name and place for the table. Here is slightly modified version, proven to work:

----------------------
CREATE PROCEDURE [dbo].[file_stats_report_2000] (@DBID INT = NULL)

AS

SET NOCOUNT ON
DECLARE @BeginTime DATETIME, @EndTime DATETIME, @DB VARCHAR(10)

SET @DB = COALESCE(CAST(@DBID AS VARCHAR(10)),'%')

SELECT @BeginTime = MIN([CaptureTime]), @EndTime = MAX([CaptureTime])
FROM [dbo].[FileStats]

SELECT
CONVERT(varchar(50),@BeginTime,120) AS [Start Time]
, CONVERT(varchar(50),@EndTime,120) AS [End Time]
, CONVERT(varchar(50),@EndTime - @BeginTime,108) AS [Duration (hh:mm:ss)]

SELECT fs.[DbId] AS [Database ID]
, fs.[FileId] AS [File ID]
, (fs.[NumberReads] - a.[NumberReads]) AS [NumberReads]
, CONVERT(VARCHAR(20),CAST(((fs.[BytesRead] - a.[BytesRead]) / 1048576.0) AS MONEY),1) AS [MBs Read]
, (fs.[NumberWrites] - a.[NumberWrites]) AS [NumberWrites]
, CONVERT(VARCHAR(20),CAST(((fs.[BytesWritten] - a.[BytesWritten]) / 1048576.0) AS MONEY),1) AS [MBs Written]
, (fs.[IoStallMS] - a.[IoStallMS]) AS [IoStallMS]
, (SELECT c.[name]
FROM [master].[dbo].sysdatabases AS c
WHERE c.[DbId] = fs.[DbId]) AS [DB Name]
, (SELECT RIGHT(d.[filename],CHARINDEX('\',REVERSE(d.[filename]))-1)
FROM master.dbo.sysaltfiles AS d
WHERE d.[FileId] = fs.[FileId]
AND d.[DbId] = fs.[DbId]) AS [File Name]
, fs.[CaptureTime] AS [Last Sample]
FROM [dbo].[FileStats] AS fs
INNER JOIN
(SELECT b.[DbId],b.[FileId],b.[NumberReads],b.[BytesRead],b.[NumberWrites],b.[BytesWritten],b.[IoStallMS]
FROM [dbo].[FileStats] AS b
WHERE b.[CaptureTime] = @BeginTime) AS a
ON (fs.[DbId] = a.[DbId] AND fs.[FileId] = a.[FileId])
WHERE fs.[CaptureTime] = @EndTime AND CAST(fs.[DbId] AS VARCHAR(10)) LIKE @DB
ORDER BY fs.[DbId], fs.[FileId]
GO
---------------------


Regards,
Kep



Acceptable Use Policy
---