Hi,

The goal is to systematically analyse the performance of indexes,
stored procedures, UDFs, views, triggers etc. in the current database
(which is quite complex, for instance, it has 200+ user tables, 150+
procedures) to see what could be improved.

Here's my understanding. Profile is passive, that is, it monitors
database activities/events, yes, one can see what's going on. Then,
probably using Database Engine Turing Advisor to make improvements
etc....
Also, I should add, for someone for instance, a consultant to a new
environment, he/she would have very limited time to understand the
business driver etc. for core entities/tables etc. hence, picking some
heavy duty stored procedures to analyze won't seem to be an option...

I'm sure there's are third party tools out there that would analyse a
large relational database and offer areas of improvement...

So, what tool are you using to achieve the above goal?

Thanks.

A good product I have seen deployed is the Veritas/Symantec i3. It does
a lot more than performance analysis and monitoring of SQL Server (for
example, it can do the same for the .NET application layers accessing
SQL Server).

http://www.dmreview.com/issues/20050701/1031235-1.html
http://www.symantec.com/about/news/r...id=20070807_01

--
Plamen Ratchev
http://www.SQLStudio.com