Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    22

    Unanswered: The Forgotten Index

    Ok, here is my situation:

    I have a table-valued function that, when moved from SQL2000 to SQL2005, is having some performance issues. I have found an appropriate index that fixes the performance issues on a table queried within the function.

    For the first day, everything was running wonderfully. Then, I get in the next morning and have a message from a developer saying that performance is bad again. I double check and sure enough, the function is timing out. As a quick test, I dropped and recreated the index and performance was back to normal (~1 sec). The following day, the same thing happened; the execution plan was completely skipping the index I had created. So, I checked any column statistics that may be getting updated that would cause issues and there were none that had been updated for about a week. I tried issuing the DBCC dropcleanbuffers and DBCC freeproccache commands and still, the engine would not recognize the index as the most efficient plan. Also, I have recreated this problem in a test database, so there is no nightly processing (or anyone at all for that matter) hitting the database.

    The only thing, to date, that I have found that works is dropping and recreating the index. This is obviously not an acceptable solution. I was curious if anyone here had ever come across this before and if so, what did you do to fix the issue?

    Thank you very much in advance!
    www.socialec.com - Social Engineering/NLP

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sounds like fragmentation to me possibly. did you run DBCC SHOWCONTIG when it sucks?

    I rebuild some indexes nightly and some weekly.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2007
    Posts
    22
    DBCC SHOWCONTIG:

    (My table)
    TABLE level scan performed.
    - Pages Scanned................................: 4564
    - Extents Scanned..............................: 571
    - Extent Switches..............................: 570
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [571:571]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 14.36%
    - Avg. Bytes Free per Page.....................: 194.2
    - Avg. Page Density (full).....................: 97.60%

    According to this, it appears that my pages are fairly full and not much fragmentation actually exists. Please correct me if you interpret this any differently.

    The strange thing about this is, as I mentioned, this is a test database specifically created to test this index and it is completely static. The initial build of the index is going to be as fragmented as it will ever be. However, every night, the plan goes down the tubes and misses the index.

    Thanks again for any insight!
    www.socialec.com - Social Engineering/NLP

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Have you tried an optimizer hint (command) to force it to use this index?

    Have you looked at the execution plan differences to see why the optimizer is choosing a different method of data retrieval?

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would try the optimizer hint and maybe WITH RECOMPILE. What happens when performance sucks and you recompile the stored procedure?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    A query hint will probably do the trick. If you are using variables in your function, you can use the OPTIMIZE FOR hint to give SQL Server enough information about your variables to generate a useful plan.

    Can you post the source code for your table valued function?

  7. #7
    Join Date
    Oct 2007
    Posts
    22
    tomh53:
    I have quickly tried to force the index use through hints. Initial attempts have proved unsuccessful, but I believe I need to just dig deeper.

    Thrasymachus:
    It's actually a table-valued function, not a stored procedure.

    cascred:
    I would prefer not to post the code for a few reasons. One is that the function is roughly 2000 lines long. Another is that it contains semi-sensitive information that I would rather not show. Basically the jist is this:

    CREATE FUNCTION dbo.func (@var1 VARCHAR(10), var2 CHAR(3))
    RETURNS @tbl Table
    (col1 ..... coln)

    IF @var1 = 'blah' AND @var2 = 'blahblah'
    INSERT INTO @tbl
    SELECT ........
    FROM ......
    INNER JOIN
    INNER JOIN
    INNER JOIN
    etc.

    UNION ALL

    SELECT ........
    FROM ......
    INNER JOIN
    INNER JOIN
    INNER JOIN
    etc.

    UNION ALL

    SELECT ........
    FROM ......
    INNER JOIN
    INNER JOIN
    INNER JOIN
    etc.

    ELSE

    (Repeat)
    (Does some other stuff)
    RETURN

    Everywhere the table pops up in as a joining table, I have attempted to force the use of my new index. This unfortunately does not currently work. However, this will just require some additional work on my part to figure out exactly where this is necessary.

    I guess I would prefer not to force hints if at all possible. Obviously, (for whatever reason) the hints are not necessary because when the index is dropped and recreated, everything works fine without the hints. My goal is to determine why the engine is hitting the index for a period of time, then later, even though no data has changed, completely disregard it.

    Thanks to you all for your input!
    www.socialec.com - Social Engineering/NLP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Table functions can yield poor performance under certain circumstances. Consider switching to a Common Table Expression if possible, or store the results of your table function in a temp table prior to joining with your main query.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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