Post reply

SHOWCONTIG Data

  • March 31, 2005 at 1:46 am

    #152373

    All,

    I've been looking at a particular table and reviewing the fragmentation.  I noticed the Scan Density at around 50%.  Other figures of note were Logical Scan Fragmentation at 11%, Extent Scan Fragmentation of 54%, Average Page Density of 93%.

    All of our databases are accross 2 files.  Some sites I saw were saying that if this is the case then the Scan Density should be ignored and I should ignored.

    Just want a bit of clarification on this.

  • March 31, 2005 at 2:15 am

    #549743

    If it's hard to say whether the table is split across both files or not then you should probably think what would you do if it was just the one file.

    If it was me then I would be looking at reindexing regardless of whether it spanned the files or not, the rest of the figures aren't that bad but the worst that will happen is that performance will stay the same, the best is that it will get better (we hope lol).

    Why not Reindex and see what showcontig reports afterwards, if it's still similar then you can be fairly confident that the table is spanning both files and that you don't need worry about it next time.

  • March 31, 2005 at 2:23 am

    #549745

    Reindexing is a big issue here.  I'm new to the company and the database is a vendor supplied system.  I'm working with them at the moment.

    The clustered index looks good in all area's.  I reindexed the table yesterday and within 5 hours, a couple of the non clustered indexes had a scan density of around 40%.  The clustered index stayed above 90%.

    As the databases are all hospital sites, the downtime scheduling is quite sensitive.

  • March 31, 2005 at 2:29 am

    #549746

    You could always try an online defrag, but we all know how transaction log hungry that is.

    Sounds like there's a whole lot of insert/update/delete activity going on so maybe it's down to a badly designed system (with it being a vendor supplied database I wouldn't be suprised, we've had some really terrible ones in a former company :hehe.

    Good Luck

  • March 31, 2005 at 3:06 am

    #549752

    See if this helps:

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 31, 2005 at 3:08 am

    #549755

    Thanks Frank,

    I'd forgotten to add the update stats bit after the online defrag, but thought that Clive was experienced enough to know that anyway

  • March 31, 2005 at 3:11 am

    #549759

    Yes, I'm sure Clive is

    Clive, good luck with that new job btw!

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 31, 2005 at 3:16 am

    #549762

    Yeah, I've been reading through stuff like that.  I have been running online defrags as well as updating statistics.

    It's a bit of a pain.  For some reason, Auto Update Statistics is turned off and the vendors do dbreindex, indexdefrag and update statistics in scheduled jobs throughout the week.  So for Monday, objects starting with A through E, Tuesday, F through J etc etc.

    Oh what fun

  • April 1, 2005 at 4:21 pm

    #550221

    The scheduled update statistics in chunks, (A..E, ect) will have to be checked to see if it is working OK.

    It could be that the general design is to spread the process over several days. This would spread out the additional transaction log usage; otherwise the transaction log could grow quite large on a single update-all table statistics process. How large is the database? What is the transaction log file size? How many files?

    He has that Auto Update Statistics turned off so he can have controll over creating the stats. BTW, if you have some lopsided table sizes, for example, 1000 tables of an average size and then one or more mother chickens, then you might consider filtering the process even further.

  • April 2, 2005 at 12:20 pm

    #550238

    The databases vary in size from maybe 2Gb to 7Gb.  I agree, the reindexing/updating statistics has been defined to load balance.  The problem I have though is that 1 week between reindexing/updating stats is obviously not working due the fragmentation I am seeing.

    There are 43 databases spread over 6 clustered servers.  3-6 instances of SQL Server exist on each box.

    The good thing is that my company are very happy for me to approach the vendors with recommended changes to their database design.

    It's quite a new thing for me to be working on database's that I have not designed myself.

    Thanks for the feedback so far and thanks Frank for the good luck wishes!!

Viewing 10 posts - 1 through 9 (of 9 total)

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