The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Friday, April 19, 2024 Login
Public

Reindex "bubble" order certain tables first 3/20/2009 12:53:03 PM

Recently had a need to ensure that some tables were indexed in a particular order, mostly due to a very small maintenance window.  I did not want to create any supporting tables to do this, so we quickly modified the reindex script to include a priority and list the tables in the query.  If there were more than just a handful, our thought was to use the extended properties of each table to add a priority, and then order by that priority for the reindexing.

The quick way for a few tables was just changing the select statement to:

SELECT table_name, table_schema,
 Case when table_name in ('Client','conflict') Then 1
    When table_name in ('appointment') then 2
  Else 3 End As Priority
FROM information_schema.tables a
WHERE a.table_type = 'BASE TABLE'
 AND a.table_name <> 'dtproperties'
 AND NOT (a.table_name LIKE 'tmp%')
 ORDER BY Priority,table_name 

If you have many tables to do, and you need very fine grain control, than use the extended properties, add one to each table that you need to control.  It is not necessary to add it to every table unless you want to control the order of all.  Below is the script example to do this for two tables.


Create Table BobTest (bobTestId int identity(1,1) primary key,
    bob_description varchar(100))
Create Table z_ReindexFirst (testid int identity(1,1) primary key)

EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'1' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
     @level1name=N'BobTest'

EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'2' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
     @level1name=N'z_ReindexFirst'

SELECT CAST(e.name AS VARCHAR(1000)) As ext_prop_name, 
       Coalesce(CAST(e.value AS VARCHAR(1000)),0) As ext_prop_value,
        t.name
       FROM sys.tables AS t 
       LEFT OUTER JOIN sys.extended_properties AS e 
                        ON t.[object_id] = e.major_id 
                        AND e.minor_id = 0
       WHERE e.name is null or e.name='ReindexPriority'
ORDER BY e.value desc,t.name

drop table bobTest
drop table z_ReindexFirst

Blog Home