Indexes fragmenting overnight

Question

Indexes fragmenting overnight

grapefruitmoon

SSCrazy

Points: 2226
More actions
June 9, 2006 at 3:11 am

#67448

Over the past few days we noticed severe performance issues on some of our more complicated queries. I ran a DBCC ShowContig on the problematic tables, and noted that the Logical Scan Fragmentation was very high, like over 90%. I ran a DBCC DBREINDEX on the tables, the Logical Scan Fragmentation reduced down to between 0% and 10%, and the queries ran instantly.
However...the next day, the queries were causing problems again. Running ShowContig showed the fragmentation was up to over 90% again. Now, these are very static tables I'm dealing with...absolutely no UPDATE, INSERT or DELETE commands have been run against them (we import the data once a month). I set up a job to monitor the state of the index fragmentation overnight. All is well until 0100, when the LSF hits 90% again. I can't figure out what could be causing this, we have no jobs that run on, or affect, this database overnight, except the backup, which runs at 2100. Has anyone experienced anything like this before, or does SQL Server do something on the fly that could cause it to happen?
TIA!
-----------------------------------------------
SQL Server posts at my blog, Grapefruitmoon.Net[/url]

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

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

Mark Yelton SSCommitted Points: 1570 More actions · Answer 1

Gremlins.........

Seriously though, data modification is the only way the indexes can get out of whack like that. Just for SAGs, if the fill factor is at 100% change it to 70 and see what happens.

Mark

grapefruitmoon SSCrazy Points: 2226 More actions · Answer 2

Turns out that it was a DBCC SHRINKDATABASE that was the problem. This was being run every night, and was causing the indexes to fragment.

-----------------------------------------------
SQL Server posts at my blog, Grapefruitmoon.Net[/url]

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 3

very interesting. how about running DBCC SHRINKDATABASE before rebuilding indexes every night?

grapefruitmoon SSCrazy Points: 2226 More actions · Answer 4

Since the data only gets loaded once a month, we've switched off the nightly shrink, and we'll run it after the data comes in, followed by the re-index. I think the re-index will use up some more space though, but I'm hoping it's not too much.

-----------------------------------------------
SQL Server posts at my blog, Grapefruitmoon.Net[/url]

SQLBill SSC Guru Points: 51440 More actions · Answer 5

I would reindex then shrink. Reindexing causes the log file to grow 1.5 to 2 times the size of the database being reindexed.

-SQLBIll

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 6

dont you think it would cause defrag if we shrink post index rebuild? are you only suggesting shrinking log file?

SQLBill SSC Guru Points: 51440 More actions · Answer 7

When you rebuild the indexes, that will leave more empty space in the database (.mdf) and the log file (.ldf) will be huge. You could just leave the empty space in the .mdf and only shrink the log file, post reindexing. That will leave empty space in the data file, which can be a good thing...it gives room for more data without having to autogrow.

I say....shrink the database if they need it, reindex, backup the database, shrink the log file.

-SQLBill

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 8

sqlstar2011

SSCertifiable

Points: 6000

June 12, 2006 at 12:55 pm

#643212

Thanks for clarifying. This makes sense.