Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
swordfish-grapefruitmoon
Starting Member
3 Posts |
 Posted - 2006-06-09 : 09:10:17
|
| 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! |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-06-09 : 12:42:44
|
| You're running a database shrink job around 1am? That's my guess as shrink moves data backwards from the end of the database files to the start, paying no attention to LSF, and so merrily reversing the physical order of indexes.Am I right?Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
swordfish-grapefruitmoon
Starting Member
3 Posts |
Posted - 2006-06-12 : 05:31:47
|
| Now that is a good question! I'm not entirely sure if a shrink job is being run overnight...I'm not responsible for the maintenance tasks on the database. I'm awaiting a reply from the man in the know though! I notice from the SQL Server log that the transaction log is backed up at midnight. The problem we have then occurs about 10/15 minutes later (I ran the monitor job at a finer level over the weekend). Is it possible that the backup could have a similar effect as the shrink? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-06-12 : 06:35:12
|
| Not possible - backup is purely a read operation.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
swordfish-grapefruitmoon
Starting Member
3 Posts |
Posted - 2006-06-12 : 08:41:01
|
| I've just spoken to the person in charge of the maintenance jobs, and you were 100% correct Paul, there was a DBCC SHRINKDATABASE being run just after the transaction log backup. Many thanks for helping me find that, much appreciated! |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-06-12 : 23:31:18
|
| Cool - my pleasure.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
|
|
|
|
Indexes fragmenting overnight