Get Read and Writes per a duration

Question

Get Read and Writes per a duration

aYasser97

SSC Enthusiast

Points: 181
More actions
October 26, 2022 at 4:01 pm

#4104858

Hello,
I want to be able to view the number of read and writes for a database or (tables in the database) in a day or week or month. Currently, I am using the following query:
SELECT name AS 'Database Name'
,SUM(num_of_reads) AS 'Number of Read'
,SUM(num_of_writes) AS 'Number of Writes'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
INNER JOIN sys.databases D
ON I.database_id = d.database_id
GROUP BY name ORDER BY 'Number of Read' DESC;
But this returns the total number of read and writes from the start.

Viewing 9 posts - 1 through 8 (of 8 total)

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

ratbak Ten Centuries Points: 1335 More actions · Answer 1

The windows of time you can get will be constrained by when the server last restarted.

You could use sample_ms from sys.dm_io_virtual_file_stats to determine that start time. You would need to calculate start datetime from miliiseconds -- it may overflow int and not work w/ DATEADD). e.g.,

DATEADD(millisecond, -sample_ms % 1000, DATEADD(second, -sample_ms / 1000, SYSDATETIME()))

sample_ms: Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function. The data type is int for SQL Server 2008 through SQL Server 2014 (12.x). In these versions, the value will reset to 0 after approximately 25 days of continuous database engine uptime.

Another option might be found in this article shows how to get OS start time using xp_cmdshell.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

aYasser97 wrote:

Hello, I want to be able to view the number of read and writes for a database or (tables in the database) in a day or week or month.

I have to ask... to what end? What do you intend to accomplish with or learn from such data?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135266 More actions · Answer 3

sys.dm_io_virtual_file_stats returns data since last startup of SQLServer.

To get what you aim for, you should persist its data yourself.

By itself that data means nothing at all.

It's only use is baseline related, so you can compare to x for enhancement or degradation, ...

ref: "Baselines for SQL Server and Azure SQL Database"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

Johan Bijnens wrote:

sys.dm_io_virtual_file_stats returns data since last startup of SQLServer.
To get what you aim for, you should persist its data yourself.
By itself that data means nothing at all.
It's only use is baseline related, so you can compare to x for enhancement or degradation, ...
ref: "Baselines for SQL Server and Azure SQL Database"

I agree that baselines can be a great thing... I just worry about things that 1) people probably won't actually use (which is why I asked what it was going to be used for) and 2) people responding to false alarms because a sudden jump in Reads can sometimes mean it's because someone actually improved some code performance.

For example, someone decides to use a Tally table (a real physical one) to achieve some extra performance. Of course, that is going to cause logical reads to go a bit nuts if it's used a lot. The same holds true with other "Helper Tables" like Calendar, Factorial, Fibonacci, Prime Number tables, etc. How would someone know that it was a performance improvement that caused Reads to increase?

I also have to ask the question I asked because of exactly what you said... "By itself that data means nothing at all." If you're not actually going to analyze it and compare it with other metrics, it's just burning extra CPU to collect it and extra disk space to keep it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135266 More actions · Answer 5

indeed, Jeff

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

aYasser97 wrote:

But this returns the total number of read and writes from the start.

So use LAG to find the difference between the current reading and the previous reading to find the period reading with the understanding that the previous reading on the first reading after a restart should be compared to zero.

I'd still like to understand how you're going to use such "baseline" data. It's a useless connection unless you actually compare it to other metrics, especially those concerning workload and code frequency.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

Johan Bijnens wrote:

indeed, Jeff

Sorry, Johan... I'm not sure how to take that. To be sure, it wasn't meant as an insult to anyone (especially you). You hit the nail on the head and I took the opportunity to expound a bit more, especially since the OP hasn't answered my question on the subject.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135266 More actions · Answer 8

Jeff Moden wrote:

Johan Bijnens wrote:
indeed, Jeff
Sorry, Johan... I'm not sure how to take that. To be sure, it wasn't meant as an insult to anyone (especially you). You hit the nail on the head and I took the opportunity to expound a bit more, especially since the OP hasn't answered my question on the subject.

We are totally on the same wave length. No problem at all.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me