Friday, May 24, 2013

How to retrieve IO statistics of SQL databases on file level?


 
Performance of a SQL database depends on different factors. One of these factors is disk activity, also known as Disk IO. With Windows Performance monitor (Perfmon) you can measure the performance of your disk. However if you have 4 database files on 1 drive, you do not know which of your databases is causing the most Disk IO. Within SQL Server you can use a dynamic view which will give you information on database file level.  Execute next statement on the SQL Server:

SELECT d.name  ,s.filename, NumberReads,  NumberWrites,  BytesRead,BytesWritten,
 IoStallReadMS, IoStallWriteMS, IoStallMS,BytesOnDisk
FROM Fn_Virtualfilestats(NULL,NULL) f
INNER JOIN sys.sysaltfiles s ON f.dbid = s.dbid and f.FileId = s.fileid
INNER JOIN sys.databases d ON f.DbId = d.database_id
ORDER BY IoStallReadMS DESC

This query will show next columns:

Name: Database name
Filename: Filename of the database file. Look to the extension to see if it is the MDF or LDF file
Timestamp: Database timestamp at which time the data was taken
Number of reads: Number of reads issued on the file
BytesRead: Number of bytes read issued on the file
IoStallReadMS: Total amount of time, in milliseconds, that users waited for the read IOs to complete the file
Number of writes: Number of writes issued on the file
BytesWritten: Number of bytes written issued on the file
IoStallWriteMS: Total amount of time, in milliseconds, that users waited for the read IOs to complete the file
BytesOnDisk: Physical file size(count of bytes) on disk.


With this query, you can look which databases are generating the most IO and time database files are waiting on the disk to get the required data. This can help you to decide to move some database files to seperate disks.

6 comments:

Anonymous said...

1xbet korean betting - Legalbet
1xbet korean betting - Legalbet.co.kr offers more 1xbet free download than 1500+ sports and casino games to players in South Korea,

jessciacarvin said...

SQL database and SQL query to retrieve detailed information about database file-level Disk IO statistics. It highlights the key metrics. English Essay Help - Essaywritingservices.ca helps out complete all assignments throughout the Hard disk.

Vidhipal said...

Experience a surge of energy and focus with Gat nitraflex pre workout, designed to optimize your workouts. With scientifically-backed ingredients, it's the perfect companion to fuel your fitness journey and help you reach your goals.

james said...

SQL databases can prove to be somewhat difficult to fully comprehend, yet the knowledge is crucial for working with data. If you are interested in documenting the process of acquiring such technical skills or other profession, there are professional biography writers who create engaging life stories. They make sure that what you know and how you can positively impact their operations comes out in the writing clearly.

Zayn VC said...

Impressive SQL query for retrieving IO statistics! Analyzing database performance is as critical as assessing potential investments for top venture capital firms. 🚀📈

Faisal Aftab said...

Wow, this SQL query is a game-changer for monitoring disk IO! 🚀📊 It’s like having a detailed analysis from faisal aftab precision and clarity at its best! 🔍💻