SQL Server Performance

Index Tuning Wizard and First Statement in a Batch Limitation

Discussion in 'Performance Tuning for DBAs' started by ZLA, Jun 19, 2008.

  1. ZLA New Member

    According to SQL Server Query Performance Tuning Distilled by Sajal Dam, the Index Tuning Wizard only looks at the first SQL statement in a batch statement from a trace file. My web site generally groups multiple statements together to reduce the cost of connections. Often the first several statements are simple ones with the more resource intensive ones coming later. I don't use the GO keyword to separate my statements.
    Is there any way to get the ITW to look at all my statements? Do I need to edit the source text data manually and add GOs to all my batches?
    Thanks in advance.
  2. Luis Martin Moderator

    Could you post a little part of you batch?
  3. ZLA New Member

    Here are three examples. I've changed the object names in some cases for confidentiality reasons. Any syntax errors are from the editing and not in the actual source. If the book's comment is correct, then the last two batches will ignore all the important content after the two DECLARE statements. The first batch will probably tune the components of vReceiveList but ignore any tuning for MMTable with respect to it's subquery.
    I haven't actually tested the book's assertion and was just trying to see if anyone knew about the limitation or if I need to add GO statements after the semicolons in order to get all the statements considered by the Wizard.
    SELECT * FROM vReceiveList WHERE 1=1;
    SELECT * FROM vReceiveDetailList WHERE 1=1;
    SELECT ClientID, MaterialID FROM MMTable WHERE ListInd = 1 AND ClientID IN (SELECT DISTINCT ClientID FROM vReceiveList WHERE AssignInd = 1) ORDER BY ClientID, MaterialID
    DECLARE @ID int;
    SELECT @ID = ClientID FROM vEditData WHERE BatchID = 1760;
    EXEC spGetMaterials @ClientID, 1, 1;
    SELECT * FROM vEditData WHERE ItemID = 1730;
    SELECT * FROM vDetailEditData WHERE ItemID = 1730;
    EXEC spGetTM;
    EXEC spGetMaterials @SiteID, 1;
    EXEC spGetRType;
    SELECT MeasureTypeID, MeasureType FROM MTRTable ORDER BY MeasureTypeDescription;
    DECLARE @IDArray varchar(7000);
    EXEC spAddData 1387, '5.4', 0, 133, @IDArray OUTPUT;
    EXEC spScheduleData @IDArray, '', '1/18/2007', NULL, 133;
    EXEC spAddData 1387, '5.4', 0, 133, @IDArray OUTPUT;
    EXEC spScheduleData @IDArray, '', '2/17/2007', NULL, 133;
  4. Adriaan New Member

    Why don't you create this as a sproc, and run the ITW against that?
  5. Luis Martin Moderator

    I really don't now if you need a GO statement to go on with ITW in a trace.
    I don't work (in performance issue) in that way.
    If I were you, this is what a try:
    Open Query Analyzer and test, check execution plan, run ITW with:
    SELECT * FROM vReceiveList WHERE 1=1;
    SELECT * FROM vReceiveDetailList WHERE 1=1;
    SELECTClientID, MaterialID FROM MMTable WHERE ListInd = 1 AND ClientID IN(SELECT DISTINCT ClientID FROM vReceiveList WHERE AssignInd = 1) ORDERBY ClientID, MaterialID
    And repeat this method with:
    DECLARE @ID int;
    SELECT @ID = ClientID FROM vEditData WHERE BatchID = 1760;
    EXEC spGetMaterials @ClientID, 1, 1;
    SELECT * FROM vEditData WHERE ItemID = 1730;
    SELECT * FROM vDetailEditData WHERE ItemID = 1730;
    EXEC spGetTM;
    EXEC spGetMaterials @SiteID, 1;
    EXEC spGetRType;
    SELECT MeasureTypeID, MeasureType FROM MTRTable ORDER BY MeasureTypeDescription;
    And so on.
  6. ZLA New Member

    I guess I've been thinking I had to anaIyze an entire batch of statements in order for ITW to consider indexes that balance all of the query requirements, not just a particular query. After all, what works best for one query might not be best for another.
    But from your response and other reading I've done, I get the impression that many people use ITW to suggest indexes for individual queries and then pick the "best" recommendations so as not to implement too many indexes.
    Thank you for the feedback. I'll try what you suggest.

Share This Page