Solved

SQL Activity Monitor - Raw query

Posted on 2014-03-03
11
541 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Improvement  ( Speed) 14 27
CPU high usage when update statistics 2 30
Sql server get data from a usp to use in a usp 5 16
replace \ by - in select 4 20
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

773 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