April 2, 2009 at 4:56 am
Hi all,
I'm currently trying to figure out the READ vs WRITE for my databases; and i've been looking at this using fn_virtualfilestats, and in the all but 1 instance this appears to be correct, the calculation I have used is:
NumberReads / (NumberReads+NumberWrites)
However for one of the db's it is showing roughly 80% of the work is reads, when I know this should not be the case as the db is write intensive, it should really be the other way round.
Does anyone know a better way to calculate READ vs WRITE for db's, also is it possible to reset the value returned in the function fn_virtualfilestats?
Any help would be appreciated.
Thanks
April 2, 2009 at 6:02 am
I've never tried achieving that kind of measure before. But, I will say that you shouldn't be surprised if there are lots of reads even on a system that you think should primarily be writes. Every time you insert you'll get constraint checks, unique, primary key, foreign key, that require a set of reads against the database. Same thing if you delete data or update data. So for a single row delete, you can get many times more reads than writes as each of the foreign keys for the table is checked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2009 at 6:20 am
Hi Grant,
Thanks for the reply, it's a good point you make about the constraints etc, do you know if it is possible to reset the values returned from the function, or are these done when the SQL service restarts (or never)?
April 2, 2009 at 6:25 am
I'm not sure, I haven't used this myself, and I don't see anything in the documentation. One point, Books Online suggests using the dynamic management function sys.dm_io_virtual_file_stats instead of fn_virtualfilestats. From what I can see in the documentation, it's based on the reads & writes since the last time the instance started. So, to reset you need to restart? Not helpful I'm sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2009 at 6:32 am
Once again Grant, many thanks for the additional info.
I'll be sure to try and find additional info regarding 'sys.dm_io_virtual_file_stats instead', however the interesting thing is that the values returned are different for each, whats even stranger is that you would expect the dm function to have lower values than the original function, however it's value are infact larger! Strange, maybe this means fn_virtualfilestats is recording from when the file was last grown or something?????
April 2, 2009 at 6:52 am
fn_virtualfilestats is cumulative since the last SQL restart
---------------------------------------------------------------------
April 2, 2009 at 6:58 am
Hi George,
thanks for letting me know that.
The figures from both now match up (my mistake) so thank you both for your help with this.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply