SHOWCONTIG Data

Question

SHOWCONTIG Data

Clive Strong

SSChampion

Points: 11222
More actions
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.

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

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

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.

Clive Strong SSChampion Points: 11222 More actions · Answer 2

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.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 3

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 5

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

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]

Clive Strong SSChampion Points: 11222 More actions · Answer 7

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

Clark Cruz-131251 SSC Journeyman Points: 82 More actions · Answer 8

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.

Clive Strong SSChampion Points: 11222 More actions · Answer 9

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!!