May 7, 2015 at 4:22 pm
Hi,
I want to find the reads/writes for the db. I use the below queries to find db &table reads & writes It gave me 28% reads & 58 % writes for a particular db.
My question is: If total table reads 85% and writes are 15% then how the database reads/writes are different?
Query1: To find DB reads & Writes
CREATE TABLE #FileStatsPerDb
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[databaseName] [NVARCHAR](128) NULL,
[FileType] [NVARCHAR](60) NULL,
[physical_name] [NVARCHAR](260) NOT NULL,
[DriveLetter] VARCHAR(5) NULL,
[READS] [BIGINT] NOT NULL,
[BytesRead] [BIGINT] NOT NULL,
[Writes] [BIGINT] NOT NULL,
[BytesWritten] [BIGINT] NOT NULL,
[BIGINT] NOT NULL,
[InsertDate] [DATETIME] NOT NULL DEFAULT GETDATE()
) ON [PRIMARY]
DECLARE @Counter TINYINT
DECLARE @DelaySeconds INT
DECLARE @TestTime DATETIME
--Set Parameters
/*
The counter is just to initialize the number to 1
The Delayseconds Tells SQL Server how long to wait before it runs the second data point.
How long you want this depends on what your needs are. If I have a load test running for
5 minutes and I want to know what the read and Write percents were during those 5 minutes
I set it to 300. If I just want a quick look at the system I’ll usually set it to 60 seconds,
To give me a one minute view. This depends on if it’s a busy time and what’s going on during that time.
*/
SET @Counter = 1
SET @DelaySeconds = 60
SET @TestTime = DATEADD(SS,@delayseconds,GETDATE())
WHILE @Counter <=2
BEGIN
INSERT INTO #FileStatsPerDb (DatabaseName,FileType,Physical_Name,DriveLetter,READS,BytesRead,Writes,BytesWritten,SIZE)
SELECT
DB_NAME(mf.database_id) AS DatabaseName
,Mf.Type_desc AS FileType
,Mf.Physical_name AS Physical_Name
,LEFT(Mf.Physical_name,1) AS Driveletter
,num_of_reads AS READS
,num_of_bytes_read AS BytesRead
,num_of_writes AS Writes
,num_of_bytes_written AS BytesWritten
,size_on_disk_bytes AS SIZE
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
JOIN sys.master_files AS mf ON mf.database_id = fs.database_id
AND mf.FILE_ID = fs.FILE_ID
IF @Counter = 1
BEGIN
WAITFOR TIME @TestTime
END
SET @Counter = @Counter + 1
END
;
WITH FileStatCTE (Databasename,Filetype,Driveletter,TotalReads,TotalWrites,TotalSize,TotalBytesRead,TotalBytesWritten)
AS
(SELECT BL.Databasename,BL.FileType,Bl.DriveLetter,
NULLIF(SUM(cp.Reads-bl.Reads),0) AS TotalReads,
NULLIF(SUM(cp.Writes-bl.Writes),0) AS TotalWrites,
NULLIF(((SUM(cp.Size-bl.Size))/1024),0) AS TotalSize,
NULLIF(((SUM(cp.BytesRead-bl.BytesRead))/1024),0) AS TotalKiloBytesRead,
NULLIF(((SUM(cp.BytesWritten-bl.BytesWritten))/1024),0) AS TotalKiloBytesWritten
FROM
( SELECT insertdate,Databasename,FileType,DriveLetter,READS,BytesRead,Writes,BytesWritten,SIZE
FROM #FileStatsPerDb
WHERE InsertDate IN (SELECT MIN(InsertDate) FROM #FileStatsPerDb) ) AS BL
JOIN
( SELECT insertdate,Databasename,FileType,DriveLetter,READS,BytesRead,Writes,BytesWritten,SIZE
FROM #FileStatsPerDb
WHERE InsertDate IN (SELECT MAX(InsertDate) FROM #FileStatsPerDb) ) AS CP
ON BL.Databasename = cp.Databasename
AND bl.filetype = cp.filetype
AND bl.DriveLetter = cp.DriveLetter
GROUP BY BL.databasename,BL.filetype,Bl.driveletter)
SELECT databasename,filetype,driveletter,
100. * TotalReads / SUM(TotalReads) OVER() AS ReadPercent,
100. * TotalWrites / SUM(TotalWrites) OVER() AS WritePercent
FROM FileStatCTE
ORDER BY ReadPercent DESC,WritePercent DESC
Query2 : Finding Table Reads/Writes
SELECT @@ServerName AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_NAME(ddius.object_id) AS TableName ,
SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
AS Reads ,
SUM(ddius.user_updates) AS Writes ,
SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups
+ ddius.user_updates) AS [Reads&Writes] ,
( SELECT DATEDIFF(s, create_date, GETDATE()) / 86400.0
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleDays ,
( SELECT DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleSeconds
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.object_id = i.object_id
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
and ddius.database_id = DB_ID()
--And OBJECT_NAME(ddius.object_id) not like '%sys%'
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY [Reads&Writes] DESC;
GO
May 7, 2015 at 4:43 pm
The discrepancy comes from where you're getting those numbers.
The big difference is that virtual file stats has information on physical reads and writes. The index usage stats DMV doesn't care if a seek is satisfied by a physical read from disk or a read from a page already in memory. It's just a seek.
What this really tells you is that you're doing a lot of reads from memory, which isn't so bad 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply