May 23, 2012 at 5:25 am
I have found some code that is periodically performed on our databases that is described as 'reindex patch'. It creates a table of tables that meet certain requirements then runs dbcc reindex (tablename) with no_infomsgs on the list. I am interested in converting this to use 'alter index'.
I investigated the table list to see what was missed and the main difference is table type 'base table' is delt with but table type 'view' is not.
I am new to SQL and strugling to work out why it was set thus so I have this question:
Q] Is there any benefit to excluding views from a reindex (apart tasks to perform)?
I have been looking at the Brads Sure guide to Maintenance Plans and I can't seem to locate the answer. I am tempted to just replace this code with the maintenance task instead.
Thanks in advance
May 23, 2012 at 1:04 pm
Can you please post the query that "creates a table of tables that meet certain requirements" ?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2012 at 2:34 am
Here is the code that selects a list of tables to reindex then reindex. Hope I posted the code correctly.
declare @tabname varchar(255), @i int, @imax int
declare @tablst table (i int identity(1,1), tname varchar(255) collate database_default not null)
insert @tablst select table_schema+'.'+table_name
from information_schema.tables
where table_type = 'base table' and table_schema in ('x','xuser')
order by table_schema+'.'+table_name
set @imax = scope_identity()
set @i = 0
while @imax > @i
begin
set @i = @i+1
select @tabname = tabname from @tablst where i = @i
dbcc dbreindex (@tabname) with no_infomsgs
end
When I try different logic in the select to see what is not processed in this code, I find 'table_type' of views. There is one other table which the 'table_schema' is dbo.
As mentioned I am wondering why the views were left off? Is it just fewer things to do or do they require reindexing less often?
May 24, 2012 at 4:49 am
Drenlin (5/23/2012)
Q] Is there any benefit to excluding views from a reindex (apart tasks to perform)?
typically (and i'm not going into indexed views here) - views don't have indexes, tables do - views are just a "window" onto one or more tables
the syntax for rebuild is as follows
ALTER INDEX [PK_mytable] ON [dbo].[mytable] REBUILD
or to defrag
ALTER INDEX [PK_mytable] ON [dbo].[mytable] REORGANIZE WITH ( LOB_COMPACTION = ON )
by the way it's easy to script these - go to management studio and expand your table - go to the indexes folder and click "rebuild" - then click on the script button at the top
typically you would use this kind of "table of tables" if you were reading the data from sys.dm_db_index_physical_stats and making a decision to rebuild only those indexes that are most fragmented and have a certain number of pages.
a maintenance plan will pretty much re-index everything (which is not to say this is a bad thing)
how many tables and indexes do you have ? and is it crucial that your databases are up 100% of the time ? can you reindex at the weekend ????
MVDBA
May 24, 2012 at 5:10 am
Thanks Mike
I guess the quantity should have been mentioned before. There are about 1600 tables to go though using that select command with an additional 700 views being skipped. Database size ranges between 400Mb to 3Gb. All databases have the same structure (database per customer - don't ask, long answer but valid). There is minimal usage times (weekends) that this would be scheduled for (haven't decided on frequency yet but between weekly to monthly).
Thanks for the tip to see the code. I see it inserts the values for the defaults which helps me with understandings.
If you say views don't have indexes (agree - don't go into indexed views) then I guess it tried to streamline the process. I shall have to play with updated version and a version that does all (like the Maintenance Plan) on a test server.
I have enough understanding to play further.
Many thanks
May 24, 2012 at 10:04 am
Drenlin (5/24/2012)
I have enough understanding to play further.
By all means dig in and learn the internals but there is no sense in re-inventing the wheel. May I recommend a solution that inspects all indexes in your instance and reorgs them or rebuilds them based on the level of fragmentation: SQL Server Index and Statistics Maintenance by Ola Hallengren
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2012 at 10:53 am
michael vessey (5/24/2012)
the syntax for rebuild is as followsALTER INDEX [PK_mytable] ON [dbo].[mytable] REBUILD
or to defrag
ALTER INDEX [PK_mytable] ON [dbo].[mytable] REORGANIZE WITH ( LOB_COMPACTION = ON )
For info, you dont need to specify
WITH ( LOB_COMPACTION = ON )
The default is set to on and is ignored if no LOBs exist
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply