3rd Party Performance Tool/Software

Is there any software that can analyze stored procedure performance and
recommend solutions?
Thanks,
Dave
How about the database engine tuning advisor, which ships with the product?
You can send it a workload consisting of a single stored procedure call, but
for a better analysis and set of recommended enhancements, you're better off
providing a workload of a typical business day.
Note that this tool will recommend structural changes like adding/removing
indexes, partitioning, etc. It will not look at your SELECT query with 18
unions and suggest a single FROM with a CASE expression. :-)
A
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave
|||As far as I am aware, there is no tool that would take a stored procedure,
optimize and refactor it, and recommend alternative solutions.
The only 'tool' that can do that is a skilled DBA!!
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave
|||InDepth for SQL Server has such a feature which it implements by levaraging
either ITW or DTA depending on SQL version. It uses performance data stored
in its Performance Warehouse to feed the recommendations. It also clearly
identifies the statements and batches that are consuming most resource to
help guide your tuning efforts. To be honest we mostly use it for the latter
as it's extremely light weight and much less resource intensive than a trace
plus we don't want ITW/DTA run against a production database. However by
identifying the main resource consumers these can be tuned and measured in
the QA envionment to produce recommendations for production.
http://www.symantec.com/enterprise/products/overview.jsp?pcid=1021&pvid=317_1
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave