Counting number of IO Accesses per DB

Hello,
I am trying to write a VB.NET application to do this:
1. Count the number of Read/Write accesses to a DB on the server and,
2. Get the last access time of the DB since its creation.
Tried looking it over at MSDN...tried googling...but have failed. Any
inputs would be more than valuable.
Thanks.
_SourabhThe basic building block you are looking for is the T-SQL function
fn_virtualfilestats. You can use write a query, call it from ADO.Net, and
do something nifty to display the results using VB.Net.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sourabh" <moharss@.auburn.edu> wrote in message
news:cff778d4.0404131052.6cda5743@.posting.google.com...
> Hello,
> I am trying to write a VB.NET application to do this:
> 1. Count the number of Read/Write accesses to a DB on the server and,
> 2. Get the last access time of the DB since its creation.
> Tried looking it over at MSDN...tried googling...but have failed. Any
> inputs would be more than valuable.
> Thanks.
> _Sourabh|||Thanks Geoff for your feedback...I used the T-SQL function to get what
I wanted. However, I have 2 more questions :
1. Will taking backups also affect the number of IO accesses to a DB ?
If this is true, the number of IO accesses would be proportional to the
size of the DB for reasons of virtual memory paging, maybe ?
2. The number of IO's - what time span does that cover? The life of the
database? The capacity of some log file?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sourabh Moharil" <moharss@.auburn.edu> wrote in message
news:uw$G9rlIEHA.3092@.TK2MSFTNGP10.phx.gbl...
> Thanks Geoff for your feedback...I used the T-SQL function to get what
> I wanted. However, I have 2 more questions :
> 1. Will taking backups also affect the number of IO accesses to a DB ?
> If this is true, the number of IO accesses would be proportional to the
> size of the DB for reasons of virtual memory paging, maybe ?
Yes, but a full backup will be proportional to the number of extents
allocated in a database plus the size of the transaction log segment that is
included in the backup. . The actual database size is irrelevant.
> 2. The number of IO's - what time span does that cover? The life of the
> database? The capacity of some log file?
Since that SQL instance started up.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks once again.
1. Is there some way to work around those "irrelevant" IO accesses
becuase of backups and get just the IO accesses from the users ?
2. I get funky timestamps out of the ::fn_virtualstats. Is there some
way to make those timestamps look legible ?
3. What I'm going for here is something that we can use to look at
individual databases and determine what percentage of the servers
resources (disk space, cpu utilization, IO accesses, anything other
metric we might think of later) that database consumes. This may later
be used as a basis for charging some categories of users for hosting
their databases. Are there some guidelines in place that could help me
determine what metrics I should use to evaluate cost of hosting a DB for
the enduser ?
Thanks once again.
I drank What ? : Socrates
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sourabh Moharil" <moharss@.auburn.edu> wrote in message
news:%23rMjwrxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks once again.
> 1. Is there some way to work around those "irrelevant" IO accesses
> becuase of backups and get just the IO accesses from the users ?
>
IO is IO is IO. All SQL knows is that something wanted to read/write to the
disk. Who, what, and why are beyond the scope of the counters.

> 2. I get funky timestamps out of the ::fn_virtualstats. Is there some
> way to make those timestamps look legible ?
>
Hmm. I don't see any docs on that. I will have to do some more digging and
get back to you.

> 3. What I'm going for here is something that we can use to look at
> individual databases and determine what percentage of the servers
> resources (disk space, cpu utilization, IO accesses, anything other
> metric we might think of later) that database consumes. This may later
> be used as a basis for charging some categories of users for hosting
> their databases. Are there some guidelines in place that could help me
> determine what metrics I should use to evaluate cost of hosting a DB for
> the enduser ?
>
Isn't backup part of the overall 'cost' of maintaining a database? Why
should it be separated out? Trying to create a 'funny money' system for
charging end users based on resource utilization may be more complex than
you like. You might write some broad limits for accounts and charge a flat
fee within those limits. The limits should be very general to keep one
account from abusing the system. IMHO, people won't sign up for complex
billing systems based on resource counters that they cannot control. I know
I wouldn't.

> Thanks once again.
> --
> I drank What ? : Socrates
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks Geoff...you feedback has been really valuable to us.
_Sourabh
--
I drank What ? : Socrates
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!