Defrag questions

Question

Defrag questions

ShorePatrol

SSC-Addicted

Points: 458
More actions
August 26, 2014 at 7:37 am

#310484

I run a DBCC nightly on all my SQL DB's, but now I'm trying to ensure the fragmentation levels are ok. I don't know much about this process, but here's what I have so far...
I am testing with a test DB, which has a few tables that are "critical red" in Idera's SQL Fragmentation Analyzer.
I create a maintenance job to rebuild all indexes on that database, it completes successfully. The critical reds, and much lower (some from 80% to 40%) but they are still in the "red"...
What "should" I be doing? If this indexes have been rebuilt, is that good enough regardless what the analyzer says?
Any info appreciated!

Viewing 15 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Very small tables often don't show change on rebuild, and it's pointless trying to rebuild them. Often not worthwhile rebuilding indexes under a few hundred pages.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

What size are the tables that are still red? You don't really need to worry too much about fragmentation on small tables - let's say 1000 pages or less.

John

ShorePatrol SSC-Addicted Points: 458 More actions · Answer 3

I know you can check page size with TSQL - is the page information anywhere in Management Studio?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Usually, just counting the rows will give you a feel for whether the table is small. If you look at the table properties in SSMS and go to the Storage tab, there's some useful information in there.

John

ShorePatrol SSC-Addicted Points: 458 More actions · Answer 5

Yep - I can see where the rowcount and disk space is...just didn't know how to see page count...

Either way, yes, most of these are very small tables.

What would be the case if the table were large? (Large is a pretty relative term).

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Well, a page is 8KB, so it's easy to calculate.

For large tables, you want fragmentation to be as low as possible. If you rebuild all the indexes on such tables, if any of them are still red, you'll want to investigate.

John

ShorePatrol SSC-Addicted Points: 458 More actions · Answer 7

Gotcha...I'm ignoring tables less than 1meg.

For instance, here's one that is probably not a problem:

TABLE SIZE FRAG%

table1 1512K 61% (clustered)

Some that I'm not sure of:

TABLE SIZE FRAG%

table2 3312K 97% (heap)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ShorePatrol (8/26/2014)
Some that I'm not sure of:
TABLE SIZE FRAG%
table2 3312K 97% (heap)

It's a heap, so can't really be defragmented. Besides, fragmentation on a heap is different to fragmentation on an index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ShorePatrol SSC-Addicted Points: 458 More actions · Answer 9

I think it's time I go for my MCSA 😉

Recently got my MTA in SQL, but I'm sure I would learn a lot more of this in the MCSA courses (and MCSE after that)

Thanks for all the help - I appreciate it.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

3312K still isn't huge - only 414 pages. But best advice is to make sure every table has a clustered index, with exceptions to that rule clearly documented. Make sure you choose your clustering key carefully. There are whole blogs and articles on that subject, so I won't repeat it all here.

John

ShorePatrol SSC-Addicted Points: 458 More actions · Answer 11

John,

Here's a dumb question - some of the DB's of "vendor provided" - they just live on our server, and we do the backups, etc...

Is it part of a DBA's normal duties to add indexes and tune performance on vendor databases? I would think that's a little hairy...but I'm curious if this is "normal" for a real DBA....(which I am not - at least not yet ;-))

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Some vendors are happy for you to do this; some aren't. At the very least, you should monitor performance and, where necessary, tweak the indexing or suggest changes to the vendor.

John

ShorePatrol SSC-Addicted Points: 458 More actions · Answer 13

Gotcha - thanks...

For the SQL databases I "watch over" I do the backups (with Red-Gate, full & transaction logs), and monitor for performance issues (to the extent I know how to). I do DBCC checks nightly, along with index rebuilds nightly as well.

It's been super-stable for many, many years. So I guess at a minimum I'm doing ok.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 14

John Mitchell-245523 (8/27/2014)
Some vendors are happy for you to do this; some aren't. At the very least, you should monitor performance and, where necessary, tweak the indexing or suggest changes to the vendor.
John

We have quite of few vendor apps. Almost all of them specifically say do NOT change anything about the data structure or add additional indexes or you will no longer be supported.

Side note. If you have a vendors database and you do add indexes make sure you keep the scripts for these indexes. Reason I say that is when it comes time to do an application upgrade and that involves scripts they run against the db and do table alters those indexes may no longer exist afterwards.

Some small dbs we have had over the years have had very little indexes and some with no clustered indexes. Very poor designs. Some over index.... AND some are very good about indexing. It just depends.