Solved

SQL Activity Monitor - Raw query

Posted on 2014-03-03
11
547 Views
Last Modified: 2014-03-07
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
0
Comment
Question by:dthansen
  • 6
  • 4
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39902280
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
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39902324
Take a look at this:  http://www.sqlfingers.com/

It's basically the Activity Monitor without the GUI.
0
 

Author Comment

by:dthansen
ID: 39913332
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:dbaSQL
ID: 39913372
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.
0
 

Author Comment

by:dthansen
ID: 39913769
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.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39913816
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;
0
 

Author Comment

by:dthansen
ID: 39913853
dbaSQL, is that query real-time stats or cumulative since service start?

Thanks,
Dean
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 39913932
I believe it is cumulative.

I could be wrong, but I believe the system statistical functions may be real-time.  See the note about nondeterministic:
http://technet.microsoft.com/en-us/library/ms177520.aspx

Take a look at this one for the raw I/O stats:

SELECT
     @@TOTAL_READ [Total Reads],
     @@TOTAL_WRITE as [Total Writes],
     CAST(@@IO_BUSY as FLOAT) * @@TIMETICKS / 1000000.0 as [IO Sec]
GO


Here's a couple other ones that I have, but I believe they are cumulative:

SELECT *
FROM fn_virtualfilestats(DB_ID(N'AdventureWorks2012'), 2);
GO


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 [SizeMB]
FROM
      ::fn_virtualfilestats(NULL,NULL) vfs INNER JOIN sys.master_files mf
        ON vfs.DbId = mf.database_id
        AND vfs.FileId = mf.FILE_ID
0
 

Author Comment

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

Expert Comment

by:dbaSQL
ID: 39914017
Very good.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39914044
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question