2. Collecting Data Files Info

The Second Step You need to handle when running analysis on a SQL Server. Is your Storage. 90% of your Problems are coming from your I/O WHY ? Storage is still the only mechanical part on your server. you can read pages 100 MB/s from the fastest HDD Available. you need to know the load that your Data files have to see how can you partition them on Different Storage Drives.


   1: Select DB_NAME(states.database_id) As DatabaseName,

   2: states.file_id,

   3: Files.physical_name,

   4: num_of_reads,

   5: num_of_bytes_read,

   6: (io_stall_read_ms/1000) As io_stall_read,

   7: num_of_writes,

   8: num_of_bytes_written,

   9: io_stall/1000 as io_stall,

  10: io_stall_write_ms/1000 as io_stall_write,

  11: (size_on_disk_bytes/1024)/1024 as SizeInMBs

  12:   From Sys.dm_io_virtual_file_stats(NULL,NULL) As states Inner Join sys.master_files As Files on (states.file_id = Files.file_id

  13:   And states.database_id = Files.database_id)

  14:   Order BY DatabaseName

The Above Query will allow you to get the Database files and the number of read and writes against the file and the amount of bytes read and wrote to this file


Some things you need to keep in mind

1. The Stall is the amount of time the file waiting in Queue to write our read from the data file. the Higher stall the more problems you have

2. The DMVs starts collecting the data from the moment you start the SQL Service and counters will reset once you restart


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s