September 14, 2015 at 10:08 am
Hi,
I am running my weekly maintenance plans that use Ola's script for DBCC CHECKDB.
I thought this was working fine to defragment my indexes, but when I ran a fragmentation check I got over 100 tables at over 70 percent fragmented!
What can I do? I expected some fragmentation, but not this much.
September 14, 2015 at 10:12 am
Auto shrink on?
Manual shrink operation after the index rebuild?
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
September 14, 2015 at 10:20 am
auto shrink if OFF
I do manually shrink the t-logs once a week due to space issues.
September 14, 2015 at 10:22 am
How big are the tables with 70% fragmentation? What is their page size?
September 14, 2015 at 10:31 am
krypto69 (9/14/2015)
I am running my weekly maintenance plans that use Ola's script for DBCC CHECKDB.
Naive question but does that include Index Rebuild, as well as CHECKDB?
krypto69 (9/14/2015)
I do manually shrink the t-logs once a week due to space issues.
Repeated shrinking will fragment the files.
Can you increase the Log Backup frequency to reduce the max size of the TLog file?
Apart from a little overhead for each file the total disk space used in a day will be similar for lots-of-log-backups as few-log-backups.
We increase our TLog backup frequency to every minute or two during Index Rebuilds, as that puts the most strain on our log file.
September 14, 2015 at 10:43 am
I just noticed that allot of the fragmentation seems to be on HEAP tables.
So if I exclude the HEAP tables I still have 100 tables that have fragmentation over 20 percent!
I just ran Ola's maintenance script yesterday.
September 14, 2015 at 11:16 am
krypto69 (9/14/2015)
I just noticed that allot of the fragmentation seems to be on HEAP tables.So if I exclude the HEAP tables I still have 100 tables that have fragmentation over 20 percent!
I just ran Ola's maintenance script yesterday.
Table sizes and page sizes of the tables in question, please.
September 14, 2015 at 11:21 am
Kristen-173977 (9/14/2015)
krypto69 (9/14/2015)
I am running my weekly maintenance plans that use Ola's script for DBCC CHECKDB.Naive question but does that include Index Rebuild, as well as CHECKDB?
krypto69 (9/14/2015)
I do manually shrink the t-logs once a week due to space issues.Repeated shrinking will fragment the files.
Can you increase the Log Backup frequency to reduce the max size of the TLog file?
Apart from a little overhead for each file the total disk space used in a day will be similar for lots-of-log-backups as few-log-backups.
We increase our TLog backup frequency to every minute or two during Index Rebuilds, as that puts the most strain on our log file.
Well, he did say that the shrinking is of the log files, so that isn't contributing to index fragmentation, methinks 🙂
Assuming reindexing is being run, and not just CHECKDB, I'm with Lynn on this. It seems likely the tables involved are small.
Cheers!
September 14, 2015 at 1:05 pm
Jacob Wilkins (9/14/2015)
Well, he did say that the shrinking is of the log files, so that isn't contributing to index fragmentation, methinks 🙂
I agree. I'd diverted to start worrying about the repeated shrinking of the log files and the likely side effects of that ... my view, on that tangential point, is that the logs need backing up often enough that they don't need shrinking. If they need shrinking every week then they are running at the size (and increasing their size back up to that size) that they need to be for the workload they have to do, and the current backup frequency. But that will have no impact on the index fragmentation issue
September 14, 2015 at 1:11 pm
Kristen-173977 (9/14/2015)
Jacob Wilkins (9/14/2015)
Well, he did say that the shrinking is of the log files, so that isn't contributing to index fragmentation, methinks 🙂I agree. I'd diverted to start worrying about the repeated shrinking of the log files and the likely side effects of that ... my view, on that tangential point, is that the logs need backing up often enough that they don't need shrinking. If they need shrinking every week then they are running at the size (and increasing their size back up to that size) that they need to be for the workload they have to do, and the current backup frequency. But that will have no impact on the index fragmentation issue
Certainly, and those points were spot-on. I just wanted to clarify that the OP needn't worry about index fragmentation from shrinking the log. 🙂
Cheers!
September 16, 2015 at 1:10 am
It would be good to get an answer to Lynn's question about the number of pages... you might not need to worry for some of them ( depending on your answer to the above).
September 16, 2015 at 7:59 am
krypto69 (9/14/2015)
Hi,I am running my weekly maintenance plans that use Ola's script for DBCC CHECKDB.
I thought this was working fine to defragment my indexes, but when I ran a fragmentation check I got over 100 tables at over 70 percent fragmented!
What can I do? I expected some fragmentation, but not this much.
Don't current version of those scripts use DBCC for Integrity Checking and Alter Index for Defrag and Rebuild?
They also do Integrity checks and Index Maintenance separately.
Maybe check her website, or post very good detail on exactly what you are executing.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply