SQL Server Performance

Help with Tuning Wizard

Discussion in 'T-SQL Performance Tuning for Developers' started by TomT, Sep 13, 2003.

  1. TomT New Member

    Being new to SQL Server, I'm sure I'm missing something very obvious, but after quite a bit of time trying to figure this out, I realize I need some help.

    I ran a trace for 4-5 hours on a server, and ended up with around 160k rows, with plenty of activity. However, when I run this thru the tuning wizard, it tells me the workload does not contain any events or queries that can be tuned against the database.

    I am running it against the correct database, and there are loads of SQL:BatchCompleted and RPC:Completed events. Columns are eventclass, textdata, duration, binarydata, and spid.

    What am I missing here?

    Thanks very much for your assistance...
  2. Luis Martin Moderator

    Tom:

    Everthing looks write.

    1) Open the trace file.
    2) Find a Sql completed.
    3) Cut and Paste into SQL Analyzer.
    4) Execute the query.
    5) If the results is the same, look if SQL have a create table #xxxx, means temporary table witch is one of the cause because SQL Index Tuning don´t undertand.
    6) If SQL run OK. Try then with Index Tuning from Query Analyzer.

    BTW SQL version and SP?

    Luis Martin
  3. TomT New Member

    Luis,

    Thanks very much for your reply. Sorry, I forgot to mention the version, it's 2000 SP3.

    I did try your suggestion before posting, and the query ran fine in Analyzer, and also in the tuning wizard. I tried again just now, with the same results.

    Kind of a mystery, eh?

    Thanks again for your assistance.

    Tom Tucker

    TomT
  4. Luis Martin Moderator

    Tom:

    Is really a mystery, so we try to learn toghether.

    Try the following:

    Run a trace only with SQL. In the same trace you have, filter only SQL and not RPC.
    Save the file and create another file with RPC only.
    Run Index Tuning for each file.
    BTW if you are agree, send me via e-mail RAR or ZIP with trace and I´ll see what is going on.
    The idea is to aislate the problem using: what if?

    Luis Martin
  5. TomT New Member

    Luis,

    I tried your suggestion of separating the file into
    sql and rpc. Neither of the files made any
    difference.

    I'd be glad to email the two files if you like (or
    would you prefer the original?).

    Let me know, and also where to send them.

    Thanks very much for your kind assistance.

    Tom


    TomT
  6. Luis Martin Moderator

    Tom:

    I was looking you trace, and I made a table like one of the SQL trace:

    SELECT "TeamID" ,"TeamName" ,"Active" FROM "dbo"."Teams" ORDER BY "dbo"."Teams"."Active" DESC,"dbo"."Teams"."TeamName"

    I filter the trace file by SPID=78 and duration >= 15 to get a small trace (69 rows). I run Index tuning and there (obviusly) is nothing to do, but no error.
    Same with SQL Analizer.

    I first I suspect ", because in my traces I have no " at all, but run Ok in my test.

    After that I run all trace, and no errors at all

    So, I'll wait for anothers ideas from Forum.

    Luis Martin
  7. gaurav_bindlish New Member

    TomT, let's talk about basics first. What makes you think that Index tuning wizard should give you some recommendations? Do you face any performance issues?

    How many databases are involved in the trace? Tuning wizard has the limitation of being able to look at only one database.

    At this point I would like to mention that Tuning Wizard is evolving and is not a perfect tool to tune.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. TomT New Member

    Gaurav,

    I'm only working with one database. I was just interested in seeing what the tuning wizard suggested about my indices. I am not seeing performance problems, but wanted to see if, based on user activity throughout the day, the trace along with the tuner might suggest some improvements.

    I was very surprised it could not find anything to work with, since there are lots of sql and rpc events.

    thanks for your assistance,

    Tom

    TomT
  9. gaurav_bindlish New Member

    Probably you have the perfectly designed database....

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. TomT New Member

    Wouldn't that be nice. The problem however is that the tuning wizard claims there are no valid events to work with, which as far as I can see, is not true....

    TomT
  11. satya Moderator

    Can you make sure the following events are included in the profiler
    1)SQL:BatchCompleted 2) RPC:Completed.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  12. TomT New Member

    Satya,

    Thanks for your reply, yes, both of these events were included (they were the only events captured).

    Tom

    TomT
  13. satya Moderator

    Then try capturing other events also that should add-up the trace file to recommend better indexes.
    I have seen this issue earlier and simply arranged time to refresh the services, thought its not required but it rectified so.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  14. Luis Martin Moderator

    I think we are missing something here.

    Tom sed: "it tells me the workload does not contain any events or queries that can be tuned against the database"

    There is not a case when Index Tuning has nothing to do. This case mean and error, like profiler is working in other database, different were trace was captured.

    If there is no improvement the is no message at all.

    I'm rigth?

    Luis Martin

  15. satya Moderator


    Wouldn't that be nice. The problem however is that the tuning wizard claims there are no valid events to work with



    I think this problem is a chain event and realted to profiler/ITW and lets wait for Tom.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. TomT New Member

    Satya,

    I'm not sure what you mean by "chain event"...

    TomT
  17. satya Moderator

    PROFILER trace events ----> ITW.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page