Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Figured out an annoying DTA issue (bug?)

    Problem: I have a client that loves to take advantage of the default schema, all queries are dynamically generated, no stored procs.

    Trying to tune for indexes has been a chore since when you try to analyze a workload in DTA you need to be a sysadmin. Problem with being a sysadmin is you cannot also have a default schema different from dbo.

    Solution: Start DTA as sysadmin. Go back to SSMS, create a connection with the default schema you need. Then click on Tools - Database Engine Tuning Advisor and analyze your workload in the 2nd session of DTA. The only caveat is that the login needs to have the db_owner role in the specific database the query is running in.

    Previously I was manually adding the schema to all tables referenced in the workload file which was time consuming.

    If you try to just launch DTA with the login w/ the default schema it runs for a while and then halfway through it errors out with some very non-specific error like "unexpected error."

    Actually it still happens intermittently, seems DTA is pretty buggy...
    Last edited by Gagnon; 05-17-11 at 01:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •