Monitor i/o, cpu etc. without traces

Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.

I know about @.@.cpu, @.@.io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.

Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(

I realize that in general this is a ridiculous request, but I thought I would ask anyway.

Hello, Rottengeek.

Are you using SQL Server 2005? If so, you might want to use some of the new DMVs.

sys.dm_exec_query_stats gives you I/O and execution statistics for each plan. You can use the columns in this view to relate the plan handle and statement handle back to the actual SQL text. You'll find that the DMV has lots of neat data; each of the statistics carries a total, min and max value for the plan since it was last compiled.

If you checkout the dm_exec_sql_text() function in books online, it shows how to get the statement text given a sql_handle (one of the columns in dm_exec_query_stats) and has a couple of neat sample queries.

I hope that's enough to get you going in the right direction. If you have follow-up questions, please let me know.

.B ekiM

|||

Below are some of the system functions/virtual tables that you can use in SQL Server 2000:

fn_virtualfilestats - To get I/O counters per database/file

sysprocesses

sysperfinfo - Perfmon counters

But whether you can access these or not depends on your permission levels. You can also take a look at the links below for other pointers:

http://support.microsoft.com/kb/298475/

http://support.microsoft.com/kb/243588/

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

|||

I'm on SQL Server 2000.

This was VERY helpful.

Unfortunately, I can't query sysperfinfo, however, I may be able to get a workaround by submitting a script to the DBA...who knows. At any rate, as a consultant (who is normally a DBA) these will come in VERY HANDY. One of the links instructs you how to create sp_blocker_pss80, which I will modify but will be much better than running a trace.

I'm typically pretty familiar with the system tables, but sysperfinfo I did not know about.

Thanks! If anyone else has some more ideas, I'd love to hear them...