SQL Server Performance

Query became slow, cannot figure out why...

Discussion in 'T-SQL Performance Tuning for Developers' started by SledKnight, Jun 17, 2009.

  1. SledKnight New Member

    Okay, so I am having a query issue that is causing us to have program exceptions from running too long, over a minute is some cases.
    The table structure has not changed, but there are now more records. I have tweaked and tweaked to no success, so here I offer a comparable table layout and run statistics.
    The table structure is similar to the folowing (there are other columns and indexes, but none that I feel should be relevant to the solution):

    [ItemTable] ~ 600,000 records
    ItemID BIGINT
    ItemType VARCHAR(100)
    ItemName VARCHAR(100)
    --- Indexes
    (PK_Item000, Clustered, [ItemID])
    (IX_Item001, Non-Clustered, [ItemID, ItemName])
    (IX_Item002, Non-Clustered, [ItemType, ItemName])

    [ItemTaskTable] ~ 14,000,000 records
    ItemID BIGINT
    TaskID BIGINT
    TaskStatus INT
    --- Indexes
    (PK_ItemTask000, Clustered, [ItemID, TaskID])
    (IX_ItemTask001, Non-Clustered, [TaskStatus, TaskID, ItemID])

    [TaskTable] ~ 150 records
    TaskID BIGINT
    ItemType VARCHAR(100)
    TaskName VARCHAR(100)
    --- Indexes
    (PK_Task000, Clustered, [TaskID])
    (IX_Task001, Non-Clustered, [ItemType, TaskName])

    [OtherInfoTable] ~ 13,000,000 records
    ItemID BIGINT
    InfoTag VARCHAR(100)
    InfoValue VARCHAR(100)
    --- Indexes
    (PK_OtherInfo000, Clustered, [ItemID, InfoTag])
    (IX_OtherInfo001, Non-Clustered, [InfoValue])
    The original query looks like this:
    SELECT I.ItemName, U.InfoValue
    FROM [ItemTable] I (NOLOCK)
    INNER JOIN [ItemTaskTable] M (NOLOCK) ON (I.ItemID = M.ItemID AND M.TaskStatus = 0)
    INNER JOIN [TaskTable] T (NOLOCK) ON (M.TaskID = T.TaskID AND T.TaskName = 'SomeTask')
    INNER JOIN [OtherInfoTable] U (NOLOCK) ON (I.ItemID = U.ItemID AND U.InfoTag = 'SomeTaskGroup')

    Everything points to the [OtherInfoTable] table as taking 85% of the processing time. When checking the execution plans, I found the indexes being used as these:
    (IX_Item002, Non-Clustered, [ItemType, ItemName])
    (IX_ItemTask001, Non-Clustered, [TaskStatus, TaskID, ItemID])
    (IX_Task001, Non-Clustered, [ItemType, TaskName])
    (IX_OtherInfo001, Non-Clustered, [InfoValue])
    With all but IX_ItemTask001 being Index Scans instead of Seeks, and returning IO output of:
    Table 'OtherInfoTable'. Scan count 1, logical reads 92105, physical reads 0, read-ahead reads 92229.
    Table 'ItemTable'. Scan count 1, logical reads 6861, physical reads 1, read-ahead reads 3808.
    Table 'ItemTaskTable'. Scan count 1, logical reads 137, physical reads 0, read-ahead reads 0.
    Table 'TaskTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

    Now I restructured the Query to provide Index Seeks by doing this (NOTE: Without "AND U.ItemID >= 0" it continues to Index Scan, not Seek):

    SELECT I.ItemName, U.InfoValue
    FROM [ItemTable] I (NOLOCK)
    INNER JOIN [ItemTaskTable] M (NOLOCK) ON (I.ItemID = M.ItemID AND M.TaskStatus = 0)
    INNER JOIN [TaskTable] T (NOLOCK) ON (M.TaskID = T.TaskID AND T.ItemType = 'SomeType' AND T.TaskName = 'SomeTask')
    INNER JOIN [OtherInfoTable] U (NOLOCK) ON (I.ItemID = U.ItemID AND U.InfoTag = 'SomeTaskGroup')
    WHERE I.ItemType = 'SomeType' AND U.ItemID >= 0
    Which began using the Index:
    (PK_OtherInfo000, Clustered, [ItemID, InfoTag])
    With IO output of:
    Table 'TaskTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
    Table 'ItemTaskTable'. Scan count 3, logical reads 137, physical reads 0, read-ahead reads 116.
    Table 'ItemTable'. Scan count 3, logical reads 1346, physical reads 79, read-ahead reads 1251.
    Table 'OtherInfoTable'. Scan count 72, logical reads 97271, physical reads 27, read-ahead reads 96939.
    I have tried Index defragging, Rebuilding Indexes, Update Statistics, OPTION (MAXDOP 1), and resturcturing the query into every concievable permutation included the use of Table Varaibles, derived tables, etc. The slowdown always comes from accessing the [OtherInfoTable], and the only thing I can see is that the number of Logical Reads and Read-Ahead Reads are pretty high.
    Any input would be great... I cannot figure out what is causing the access to that table to be slow.
    Thanks!
  2. Sandy New Member

    Hi SledKnight,
    As you tried below options so I can give you one more option which is not present in below option is Creating a Covering Index on your query
    [quote user="SledKnight"]I have tried Index defragging, Rebuilding Indexes, Update Statistics, OPTION (MAXDOP 1), and resturcturing the query into every concievable permutation included the use of Table Varaibles, derived tables, etc.[/quote]
    SledKnight, for your ref. and as per my understanding and the link http://blogs.developpeur.org/raptorxp/pages/sql-server-scans-and-seeks.aspx
    The Scan and Seek is:
    Please make sure you have used all correct index columns on your query and create a covering index too and try for the same.
    Thank you,
    Sandy.
  3. AjayWadehra New Member

    Try creating an index on the OtherInfoTable on the ItemID, InfoValue fields.
    Hope that helps.
  4. SledKnight New Member

    Well, I applied a covering index on OtherInfoTable to be: ItemID, InfoTag, InfoValue, and it reduced logical reads down to around 75000, but it is still slower than I think it should be (it is still 85% of my execution time). I'm not sure what else to try...
  5. AjayWadehra New Member

    in the query plan is it doing a seek or a scan on that index ?
  6. SledKnight New Member

    [quote user="AjayWadehra"]
    in the query plan is it doing a seek or a scan on that index ?
    [/quote]
    Did you not read my big post? :) It was a scan, I got it to do a seek (which it is doing now, even on the new covering index), but I'm still not seeing the speeds I would expect. 13 million rows is not really that large... and the ItemTasks table has 14 million, and it seeks in under 2 seconds.
  7. AjayWadehra New Member

    I did see the original post, I was just trying to determine if the new index did/did not change the plan.
    Also, try going back to the original query - see what that plan looks like.
    Couple more questions: how many records being returned by this query?, can you put a screen capture of the query plan ?
  8. SledKnight New Member

    [:p] Yeah, I was just messin' with ya.
    The only change to the plan was the index name. All the other stats are almost identical. The execution plan for that OtherInfoTable portion estimates the row count at around 120,000, but the actual rows return is <500.
    If I go back to the original query, the plan goes back to doing index scans.
    I have to easy way of posting a snapshot today... I can try Monday. Any particular portion that would help you diagnose?
    Thanks!

Share This Page