Post reply

Defragmenting system table indexes

  • November 19, 2008 at 11:29 pm

    #201640

    I am seeing slow performance on sp_help_fulltext_columns. Using profiler, it appears the performance inhibitor is some complex joins between system tables sysdepends and syscolumns.

    Looking at the state of the indexes they appear quite fragmented but I haven't found a way to rebuild them as the various DBCC commands I have tried tell me they "cannot be used on system table indexes".

    Can anyone advise what I can do to get around this limitation?

    Thanks,

    Scott Pettman

  • November 20, 2008 at 11:31 am

    #901215

    Hi Scott,

    I'm afraid you can't; INDEXDEFRAG sorts out everything with Id's < 100.

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • November 20, 2008 at 1:30 pm

    #901268

    Thanks Elizabeth.

    Dang!

    In that case, is there another way I can remedy the performance problems of this stored procedure? We have a 3rd party web application that is timing out after 30 seconds on startup as it attempts to run sp_help_fulltext_columns (which is routinely taking 37 to 45 seconds for the particular database).

    The obvious answer is to alter the timeout parameter for that application but it appears to be hardcoded and we don;t have the source code.

    Any thoughts?

    Regards,

    Scott Pettman

  • November 23, 2008 at 5:07 pm

    #902230

    On our DEV server this procedure takes 1-2 seconds to execute, 5 sec at most.

    But we don't have more than 10 columns with full-text index per database.

    You probably need to rethink your system architecture and start using a little bit of relational database functionality.

    _____________
    Code for TallyGenerator

  • November 23, 2008 at 6:03 pm

    #902239

    Thanks for your feedback Sergiy.

    Yes the dev application only takes a few seconds for us also, but in Production with a reasonable amount of data in the system tables the performance degrades down to around 40 seconds.

    This is 3rd party application so there is little scope for modifying the code (we would like to just increase the timeout parameter but it appears to be hardcoded). Similarly, we can't really restructure the database though obviously I could add indexes, rebuild the database etc, if that would help.

    As the performance issue relates to querying of the syscolumns and sysdepends tables by an inbuilt stored procedure, and I am actively prevented by the instance from making any changes to those tables, including index rebuilds, it isn't really clear to me just which piece of relational database functionality would assist.

    Our current plan is to write a custom version of sp_help_fulltext columns that performs and store that in the application database so that it will be used rather than the version in the master database. We just have to hope that the app doesn't use a fully qualified name when calling the sp.

    Regards,

    Scott Pettman

  • November 23, 2008 at 10:42 pm

    #902273

    If you open the code of procedure sp_help_fulltext you'll realize that it's written in the way which causes table scans by definition.

    No indexes may change this behavior, they are gonna be just ignored.

    Full text indexing was never meant to be heavily used in RDMS.

    It just a side application helping to sort small number of exclusions.

    It's not suppose to effective or be a main part on SQL Server based application.

    Whoever decided to buy that application did huge mistake.

    And save you time on rewriting that procedure. Not gonna work.

    There is no way to call a "sp_" procedure in user database if a procedure with the same name exists in master database.

    Create simple one, like "SELECT 1 as One" and try to invoke it.

    _____________
    Code for TallyGenerator

  • November 24, 2008 at 3:35 pm

    #902790

    Sergiy,

    I tried that test to call an alternate procedure and can confirm we are screwed. Thanks for the heads up.

    I've passed the info back to the application support team. As far as I can see our only real options now are to go back to the vendor or to recommend to the app users that that they start looking for a replacement product.

    Thanks again for your feedback.

    Regards,

    Scott Pettman

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply