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




Show quoteHide quote

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!!

Show quoteHide quote

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

Show quoteHide quote

Hi David,

SQLBenchmarkPro is a great product that will enable you to easily and
quickly identify ineffecient stored procedures or SQL statements. The
"analyse" feature quickly pinpoints the worst performing queries in a
system. Go to www.gajsoftware.com and download an evaluation copy.

Best Regards,
Andrew
------------------------------------
www.gajsoftware.com
-----------------------------------
Use SQLBenchmarkPro to find and solve your performance problems.


Show quoteHide quote

Problem on scripting stored procedures
Telling if SQL2005 is running
SMSS - script to new window uses sp_executesql?!
error importing data from Access to SQL Server
Upgrade connection issues