June 24, 2013 at 5:42 am
A bit of a under the hood question as it where.
When using the maintenance task to rebuild indexes and specifying ALL does the process ignore indexes if the fragmentation is low, below 10% for example or if the pages are < 1000?
Many thanks
'Only he who wanders finds new paths'
June 24, 2013 at 7:41 am
Hi David,
Have you run your maintenance plan? If so is there any that haven't had stats collected on them since the last run time? If so then they haven't all been rebuilt:
select object_name(si.[object_id]) AS [TableName],
CASE
WHEN si.[index_id] = 1 then 'CL'
WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[index_id]), 3)
ELSE ''
END AS [IndexType],
STATS_DATE (si.[object_id], si.[index_id])
from sys.indexes si
WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1
and si.[index_id] != 0
Query from here
Edit: Something I'm not sure of is why some of them have a null stats column.
June 24, 2013 at 7:46 am
No I havent ran the plan yet, I normally do them via a script (or manually in some cases!) checking page_count and fragmentation from DMV's.
Just wanted to know if the maintenance plan actually takes into account these considerations, out of curiousity really?
'Only he who wanders finds new paths'
June 24, 2013 at 8:07 am
June 24, 2013 at 8:10 am
I think the last couple of paragraphs sum it up perfectly!
'Although this gives you a lot more flexibility in controlling your database maintenance it would have been nice if they included an option to use the new dynamic management views to view fragmentation and base the maintenance on these numbers. This new feature does give you the option down to the table level, but it would be nice if it also allowed you to select an individual index vs all indexes for the table.
Overall this is a nice improvement over SQL Server 2000 maintenance plans, but there are definitely some additional options that would have made it a lot easier to use and also a lot more useful in more environments.'
Cheers for the link, much appreciated. 😀
'Only he who wanders finds new paths'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply