I have just inherited a MSSQL database "Datamart" for support. (I am not
the DBA) This system is a single instance of MSSQL ( one sqlserver.exe )
running on a 4 way server connected to a SAN / IBM shark disk environment.
The instance is supporting 40 separate databases.  The Databases are
accessed externally by approx 25 various applications - both batch and
online humans.

The server is running between 50% and 100% utilized during business hours.
The data resides on the Logical "G:" drive which is SAN connected Logical
drives on the Shark. This drive is constantly busy  - pegging to 100% for
minutes at a time.

My Question:
How can I succinctly and accurately determine which Databases are driving
what percentage of the CPU and I/O of the database server. Including usage
of background databases such as tempdb or master. ( i.e. if tempdb is the
CPU user - what is driving the usage of tempdb )

I have access to perfmon and the DBA.

My attempts of correlating Transaction/sec and Active Transactions/Sec per
Database to sqlserver.exe processor utilization yielded no results.

---

Request for Question Clarification by answerguru-ga on 04 Sep 2004 17:52 PDT

Hello there,

There is a tool included with SQL Server called "SQL Profiler" which
you can use to measure all sorts of performance metrics of any subset
of your databases. I have found a great article which describes the
process for creating a trace, which monitors activity that you've
predefined:

http://www.databasejournal.com/features/mssql/article.php/2239461

Let me know if this is the type of information you are interested in,
and I can post it as an official answer for you.

Cheers,
answerguru-ga

---

Clarification of Question by sfwhite-ga on 06 Sep 2004 07:04 PDT

This seems to head in the right direction.  But - This seems to leans
towards ? and correct me if I?m wrong ? ?These are the top x things
happening in a database.?  If it is just the ?top things? ? It might
not give me the info on all databases. If database X is 90%, I still
want to know that database Y is 8% and database Z is 2%.

And - and forgive my ignorance ? I can?t seem to discern if I would
have all data from all databases, or I need to run a file for each
database and correlate?

Also:
Is the trace data CPU for each statement or database?
Is the CPU the percentage of the server or of the sqlserver.exe process.
What data is in the Writes/Reads? Counts or Bytes or?

Have you had a chance to execute this trace on a multi-database sql
instance? If so, could you post or froward some sample output?

---

Request for Question Clarification by answerguru-ga on 06 Sep 2004 09:37 PDT

The SQL Profiler is a very advanced tool in that it can be configured
to monitor just about anything imaginable with SQL Server. I didn't
mention that the type of monitoring being done by the example in that
article was just one sample of what can be achieved.

You don't need to run files to use profiler - it acts more as an agent
that watches what is happening in your database(s) over a period of
time. It then uses this data to provide some type of performance
summary. The specifics are determined in the way that you configure
SQL Profiler.

I cannot provide sample output since this is not a cut-and-paste job,
however, a little time spent working in SQL Profiler will allow you to
obtain appropriate results. The type of data that is returned is
dependant on how you set up the trace and can be any of the types that
you mentioned.

Hopefully, now that you know some of the high-level workings of SQL
Profiler, some self-directed learning will allow you to achieve what
you are trying to do.

If there aren't any additional generic questions about profiler that
you need answered, can I go ahead and post this as an official answer?

Thanks,
answerguru-ga

---

Clarification of Question by sfwhite-ga on 08 Sep 2004 09:25 PDT

Understanding high-level workings of SQL Profiler and some
self-directed learning doesn?t get me much closer to the ?How? as I
was before I asked the question.

?The type of data that is returned is dependant on how you set up the
trace and can be any of the types that you mentioned.? Great. Please
help me save time by defining a trace that succinctly and accurately
determines which Databases are driving what percentage of the CPU and
I/O of the database server.

---

Request for Question Clarification by answerguru-ga on 09 Sep 2004 00:07 PDT

Unfortunately that is far beyond the scope of what can be offered
through this service. The amount of time and effort involved in doing
something like this exceeds the value of your question many times
over.

In addition, you would need to disclose more specific data regarding
your database. Since this is a public forum, that may not be the best
thing to do.

I hope you can understand my position.

answerguru-ga

---

Clarification of Question by sfwhite-ga on 09 Sep 2004 08:08 PDT

I completely understand.

Hi there,

As I seem to have answered your question to the extent that it can be
answered in this forum, I have included below a condensed summary of
the information provided during our discussion.

There is a tool included with SQL Server called "SQL Profiler" which
you can use to measure all sorts of performance metrics of any subset
of your databases. I have found a great article which describes the
process for creating a trace, which monitors activity that you've
predefined:

http://www.databasejournal.com/features/mssql/article.php/2239461

The SQL Profiler is a very advanced tool in that it can be configured
to monitor just about anything imaginable with SQL Server. I didn't
mention that the type of monitoring being done by the example in that
article was just one sample of what can be achieved.

You don't need to run files to use profiler - it acts more as an agent
that watches what is happening in your database(s) over a period of
time. It then uses this data to provide some type of performance
summary. The specifics are determined in the way that you configure
SQL Profiler.

I cannot provide sample output since this is not a cut-and-paste job,
however, a little time spent working in SQL Profiler will allow you to
obtain appropriate results. The type of data that is returned is
dependant on how you set up the trace and can be any of the types that
you mentioned.

Hopefully, now that you know some of the high-level workings of SQL
Profiler, some self-directed learning will allow you to achieve what
you are trying to do.

Best of luck in tailoring this powerful tool to meet your specific needs.

Cheers,

answerguru-ga

answerguru Clearly states she cannot answer my question for the price
I am offering:

"Unfortunately that is far beyond the scope of what can be offered
through this service. The amount of time and effort involved in doing
something like this exceeds the value of your question many times
over."

And I am cool with that - BUT 
She closes the question with an Answer summerized with the following doublespeak:

"As I seem to have answered your question to the extent that it can be
answered in this forum . . ."

My first exchange on this board has proven to be a very poor one.

Refund will be requested.
Me – “How do I change out a carburetor?”
You – “Snap-on makes a great set of wrenches!”
Me – “ Sounds Good – How do I use these wrenches to change out a carburetor?”
You – “You aren’t offering me enough money to explain *How* “
Me – “ Ok”
You – “ Can I have the money anyway?”
Me – “No”