mercredi 24 décembre 2014

Rebuilding indexes on a list of tables


I have a large number of tables that may change, may have infrequent changes in index, etc. I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the system to a crawl. So, I began hunting and found this:


http://ift.tt/1x4Sky2


With some changes to the "WHERE" criteria I was able to isolate my table of tables/indexes



SELECT USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) OwnerName,
OBJECT_NAME( i.id ) [TableName],
i.name AS [IndexName],
CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
WHEN 1 THEN 'YES' ELSE 'NO' END [IsClustered],
CASE INDEXPROPERTY(i.id, i.name, 'IsUnique')
WHEN 1 THEN 'YES' ELSE 'NO' END [IsUnique],
STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
FROM sysindexes i
WHERE USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) = 'dbo'
and INDEXPROPERTY( i.id , i.name , 'IsClustered') = 1
ORDER BY OwnerName, TableName, IndexName


I know that I can loop through this result set using dynamic SQL and apply this:



ALTER INDEX Index_Name ON Table_Name REBUILD PARTITION = ALL WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )


Where "Index_Name" and "Table_Name" are substituted for each time the line is run, and while this seems fine and good I can't help but wonder if there is a better solution. Can I avoid cursors and dynamic SQL in this case?





Aucun commentaire:

Enregistrer un commentaire