Post reply

3rd Party database with weird indexes?

  • March 14, 2006 at 8:32 am

    #111446

    Okay…I used to think I understood this stuff, but this case has me befuddled, can someone help?

    We have a 3rd party application that uses a SQL Server 2000 database.  Nothing abnormal there, mildly annoying because we don’t have any significant information over the objects or their indexes other than what we can see in management tools.  Performance on this application is, to say the least, abysmal.  A report being run in the application takes 30-45 minutes to run.

    I looked at the 2 primary tables that the report is using, and something just isn’t clicking…

    These tables have 14,000,000 and 25,000,000 rows respectively…large, but not huge.  Both have a clustered index and 7-8 secondary indexes.

    The clustered indexes are approximately 2.8G and 5.5G…the other indexes….16K each.

    That’s not a typo…16K.

    That just seems…impossible.  I executed a rebuild on one of the indexes that I would expect the report’s query to use (a date field index) and it swelled to 784M in size.  That seems more reasonable, albeit larger than I am comfy with…since 16 of those indexes would be a total of 12 Gig just in indexes on two tables. 

    Looking at the statistics on these 16K indexes, I see that they have 0 free bytes per page, 0.00% free density, 0% Extent fragmentation, and 3471% logical fragmentation.

    These numbers just don’t make any sense…Books online and Web Searches aren’t offering any direction.  Talking with tech support from the vendor makes me think they don’t even have a DBA on their staff. 

    Does anyone have any advice?  Should I just bite the bullet and rebuild all these indexes?  Is this some sort of weird virtual index I’ve never heard of?  Has something gone Horribly Wrong?

  • March 14, 2006 at 12:11 pm

    #626619

    Very weird indeed.

    How do you check the size of non-clustered indexes?

  • March 14, 2006 at 12:15 pm

    #626620

    You can see them in taskpad inside Enterprise Manager for the database.

    As it turns out, though, that number is deceptive, as it was based off some old statistics.  Apparently 2005 fixes this, but 2000 does not.

    Read up on dbcc updateusage for more information on this.

  • March 15, 2006 at 7:57 am

    #626768

    Hi Alan,

    are you running sp_updatestats and sp_createstats frequently?

    According to MS even if auto_update_statistices and auto_create_statistics is ON there may be statistics not created or not updated...

    regards karl

    Best regards
    karl

  • March 15, 2006 at 8:48 am

    #626799

    We hadn't been, as this third party application included a "reindexing utility" that (allegedly) handled index maintenance after every load.

    Problem...it appears that it basically just does the very surface index tasks (basically, dropping and recreating them, no index tuning at all).

    So we're working on fixing that.  we've had some success now that we've got good numbers to understand what's going on.

    Thanks for the help!

  • March 16, 2006 at 3:05 pm

    #627171

    I would run my own reindex script on the tables and see if space changes.  Sp_spaceused will give you the size of the object and the index size.  Also, I would capture the sql statement and verify what execution plan it is using.  Often times by default it will use the clustered index especially if a lot of the columns are being returned because of the expense of a bookmark lookup.  Hence the need for covering indexes.

    Let us know.

    Tom

     

  • March 20, 2006 at 2:56 pm

    #627732

    Check to make sure which file group the index(es) were created.  That may be reason you don't see them in EM. (they are in a different file group).

    Good Luck !!

  • March 22, 2006 at 5:31 am

    #628055

    Having encountered some pretty poor third party apps on sql server ( and some even poorer concepts of indexing )

    I'd suggest you run sp_updatestats and dbcc updateuage(0) against your database. I'm not a great lover of taskpad for viewing table/index info - I'd always look in sysindexes.

    Things you can check, are the columns actually populated that the indexes are defined upon ( we are talking third party apps after all )

    try sp_autostats tablename  to see what stats/indexes and when they were updated 

    sp_helpindex indexname - that'll tell you about the index.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

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

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