SQL Server 2005 database monitoring

18 Jun 2009 8:51 PM

jfalberg

We have the need to monitor and determine the amount of data being writing to
a SQL Server 2005 database on a daily basis. Maybe hourly â€¦

Can somebody help me determine the best method to track this?

19 Jun 2009 7:41 AM

Uri Dimant

Hi
In other word you would like to see how much data has been instered into a
table/s?
The below query gives a good indication does the db have highly writes or
reads

WITH DBIO AS

(

SELECT

DB_NAME(IVFS.database_id) AS db,

CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,

SUM(IVFS.num_of_bytes_read +IVFS.num_of_bytes_written) AS io,

SUM(IVFS.io_stall) AS io_stall

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS

JOIN sys.master_files AS MF

ON IVFS.database_id = MF.database_id

AND IVFS.file_id = MF.file_id

GROUP BY DB_NAME(IVFS.database_id), MF.type

)

SELECT db, file_type,

CAST(1. *io/ (1024 *1024) AS DECIMAL(12, 2))AS io_mb,

CAST(io_stall /1000. AS DECIMAL(12,2))AS io_stall_s,

CAST(100.*io_stall / SUM(io_stall)OVER()

AS DECIMAL(10,2))AS io_stall_pct,

ROW_NUMBER()OVER(ORDER BY io_stall DESC) AS rn

FROM DBIO

ORDER BY io_stall DESC;

Show quoteHide quote

"jfalberg" <jfalb***@discussions.microsoft.com> wrote in message
news:BCCD01AA-8013-4102-A90F-39AD0AFA46E1@microsoft.com...
> We have the need to monitor and determine the amount of data being writing
> to
> a SQL Server 2005 database on a daily basis. Maybe hourly …
>
> Can somebody help me determine the best method to track this?
>

19 Jun 2009 1:46 PM

jfalberg

Show quote Hide quote

"Uri Dimant" wrote:

> Hi
> In other word you would like to see how much data has been instered into a
> table/s?
> The below query gives a good indication does the db have highly writes or
> reads
>
> WITH DBIO AS
>
> (
>
> SELECT
>
> DB_NAME(IVFS.database_id) AS db,
>
> CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
>
> SUM(IVFS.num_of_bytes_read +IVFS.num_of_bytes_written) AS io,
>
> SUM(IVFS.io_stall) AS io_stall
>
> FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
>
> JOIN sys.master_files AS MF
>
> ON IVFS.database_id = MF.database_id
>
> AND IVFS.file_id = MF.file_id
>
> GROUP BY DB_NAME(IVFS.database_id), MF.type
>
> )
>
> SELECT db, file_type,
>
> CAST(1. *io/ (1024 *1024) AS DECIMAL(12, 2))AS io_mb,
>
> CAST(io_stall /1000. AS DECIMAL(12,2))AS io_stall_s,
>
> CAST(100.*io_stall / SUM(io_stall)OVER()
>
> AS DECIMAL(10,2))AS io_stall_pct,
>
> ROW_NUMBER()OVER(ORDER BY io_stall DESC) AS rn
>
> FROM DBIO
>
> ORDER BY io_stall DESC;
>
>
> "jfalberg" <jfalb***@discussions.microsoft.com> wrote in message
> news:BCCD01AA-8013-4102-A90F-39AD0AFA46E1@microsoft.com...
> > We have the need to monitor and determine the amount of data being writing
> > to
> > a SQL Server 2005 database on a daily basis. Maybe hourly â€¦
> >
> > Can somebody help me determine the best method to track this?
> >
>
>
>

Basically the activity going on.

I tried to copy and paste the script into Query analyzer but got the
following message:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'OVER'.

Basically I'm trying to understand what this query does.

19 Jun 2009 2:34 PM

Aaron Bertrand [SQL Server MVP]

Are you sure this is a SQL Server 2005 database? Between "Query Analyzer"
and the error messages you've received, I think it is SQL Server 2000. (I
tried Uri's query on a 2005 database in 80 compatibility mode, and it didn't
fail either.) What does the following yield:

SELECT @@VERSION;

Show quoteHide quote

> Basically the activity going on.
>
> I tried to copy and paste the script into Query analyzer but got the
> following message:
>
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'WITH'.
> Server: Msg 156, Level 15, State 1, Line 17
> Incorrect syntax near the keyword 'OVER'.
>
> Basically I'm trying to understand what this query does.

19 Jun 2009 1:27 PM

Linchi Shea

Or simply run sp_spaceused in the database at the beginning of the day and
then run it again at the end of the day, and get the difference.

Linchi

Show quoteHide quote

"jfalberg" wrote:

> We have the need to monitor and determine the amount of data being writing to
> a SQL Server 2005 database on a daily basis. Maybe hourly â€¦
>
> Can somebody help me determine the best method to track this?
>