asked on

SQL Activity Monitor - Raw query

SQL has an Activity Monitor that displays Database I/O MB per second. I'm trying to build on internal alerting system and want to capture this data periodically.

Is there a query/system view I can use to see the MB per second being used by the SQL system, in much the same way Activity Monitor must be periodically polling for it?

Thanks

Anthony Perkins

If you are asking about a specific T-SQL system call, I would refer you to the DMVs and specifically the function sys.dm_io_virtual_file_stats

dbaSQL

Take a look at this: http://www.sqlfingers.com/

It's basically the Activity Monitor without the GUI.

dthansen

ASKER

Yes, I am looking for a T-SQL query I ran run to find how many MB/Sec is being transferred. Much the same information the graph at the top of the SLQ Activity Monitor shows.

Thanks,
Dean

dbaSQL

You should take a look at sys.dm_io_virtual_file_stats. It returns I/O stats for data and log files. See here:
http://msdn.microsoft.com/en-us/library/ms190326.aspx

Run it like this to get stats on your database:
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'YourDBName'), 2);

You could potentially mingle in with the query I already referenced at sqlfingers.

dthansen

ASKER

sys.dm_io_virtual_file_stats looks like cummulative stats since the SQL service was started. I'm looking for "how heavy is the load right now".

I think I found some stuff in sysperfinfo table but still trying to sort out what counters are related to disk load and how often sysperfinfo is refreshed.

Thanks.

dbaSQL

SELECT
      db_name(io.database_id) [Database],
      mf.physical_name [FileName],
      io.*
FROM
      sys.dm_io_virtual_file_stats(NULL, NULL) io INNER JOIN sys.master_files mf
       ON io.database_id = mf.database_id
       AND io.file_id = mf.file_id
ORDER BY
      (io.num_of_bytes_read + io.num_of_bytes_written) DESC;

dthansen

ASKER

dbaSQL, is that query real-time stats or cumulative since service start?

Thanks,
Dean

ASKER CERTIFIED SOLUTION

dbaSQL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dthansen

ASKER

dbaSQL, it looks like sp_monitor from your post (http://technet.microsoft.com/en-us/library/ms177520.aspx) is just what I needed!

dbaSQL

Very good.

dbaSQL

Just a point of follow-up. sp_monitor uses the statistical functions to gather its data.

see here: http://www.novicksoftware.com/Articles/sql-server-io-statistics-page-2.htm

About half way down, at this line:
sp_monitor uses the system statistical functions discussed in the previous section to get its information