Tuning Advisor Questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 25 Mar 2008 06:50 GMT

I have saved a trace file from SQL Profiler using the tuning template. I have
3 questions related to running this against the tuning advisor:

1) When I try to run the trace in the tuning advisor, I get "98% of consumed
workload has syntax errors". What does this mean?
2) I also get lots of errors stating that "SHOWPLAN permission denied in
database ...", but I am running as SA so I don't understand how that could be
denied.
3) When you run tuning advisor with a trace file, is it actually running the
transactions again, or is it just looking at execution plans? Is it safe to
run on a LIVE production database, or do you have to do it on a copy of the
live db?

Any help is appreciated.

Reply to this Message

Uri Dimant - 25 Mar 2008 09:54 GMT

Dan
Is it SS2005?

>I have saved a trace file from SQL Profiler using the tuning template. I
>have
[quoted text clipped - 16 lines]
>
> Any help is appreciated.

Reply to this Message

Peter Yang[MSFT] - 25 Mar 2008 11:06 GMT

Hello,

Thank you for your post!

Regarding your quesiton, tuning log could provide you more detailed
informaiton. The syntax error message is informational - suppose your
original workload had a table T. “select * from T” would be valid. Now if
you dropped it and ran DTA with the original workload (with the “select *
from T” statement), DTA will not be able to tune that statement (because
the object T doesn’t exist in the database) and will report this as a
“syntax error” in the tuning log. It will also emit an appropriate error
message on the Details pane/Message column in the Progress page indicating
that x% percent of the workload has syntax errors.

As for error "SHOWPLAN permission denied in database ..." , it usually
caused by that user who created the Workload trace file did not have the
SHOWPLAN permission. You may want to grant the user permission or log on as
SA to test

Syntax for Granting, Denying, and Revoking the SHOWPLAN Permission
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/07306c9a-4b44-4f83-8923-a9cb
581047a2.htm

As I know, the workload is not actually run on the server. Please see the
following information from Books Online for more details:

Reducing the Production Server Tuning Load
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bb95ecaf-444a-4771-a625-e0a9
1c8f0709.htm

Considerations for Using Database Engine Tuning Advisor
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6ae38fe0-8b73-47a7-82df-4b72
66a103cf.htm

If anything is unclear or you have further questions on the issue, please
feel free to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

=====================================================
Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
<http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone>.
Feedback on your service and satisfaction can be posted to:
from the web interface: Partner Feedback
from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message