Solved

SQL Activity Monitor - Raw query

Posted on 2014-03-03
11
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

632 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