Home
  Home
Home
Search
Articles
Page Tag-Cloud
  Software
Software Tag-Cloud
Submit Patch
Building from Source
Open Source Definition
  Popular Tags
C Plus Plus
Source Code
Legacy
Class
Console
  Members
Login
Web-Email
Notable Members
  Official
Our Company
Copyright Information
Software EULA
GPL EULA
LGPL Eula
Pre-Release EULA
Privacy Policy
  Support
Make Contact
 
 
SQL Server Virtual File Stats

As anyone knows who's worked with SQL Server for more than about 10 minutes - she's I/O intensive - especially when under high memory pressure. So... exactly which one of your databases is reading and writing more then the others? Does your database read more than it writes? If so, by how much? How many IOPS has it encountered and how large were those logical IOPS?

Well, let me tell you - the answer is a lot easier then you might think. This simple script will spit out all of that crucial information about each database file (both data and log). Check it out for yourself!

SELECT
	DB_NAME(vfs.DbId) DatabaseName,
	mf.name,
	mf.physical_name,
	vfs.BytesRead,
	vfs.BytesWritten,
	vfs.IoStallMS,
	vfs.IoStallReadMS,
	vfs.IoStallWriteMS,
	vfs.NumberReads,
	vfs.NumberWrites,
	(size*8)/1024 Size_MB
FROM
	fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf
	ON mf.database_id = vfs.DbId
	AND mf.file_id = vfs.FileId

Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.

 


Tags:
 Filesystem    IO    Performance    SQL Server  

Created by Josh Patterson on 8/17/2013, last modified by Josh Patterson on 8/17/2013

Josh Patterson @ 8/17/2013
  I swear, that's my favorite snippet ever...  
First Previous 1 Next Last 

Login or signup to leave a comment.
 
Copyright © 2017 NetworkDLS.
All rights reserved.
 
Privacy Policy | Our Company | Contact