April 18, 2007 at 11:56 am
I've been trying to use the Rebuild Index function in SSMS for a few days now and it appears to work sometimes but most often not. It runs and says it completed successfully but with I check the index again it still recommends a rebuild. Sometimes the recommendation changes from rebuild to reorganize and when I do a reorganization it changes back to recommending a rebuild. I'm runny the Standard edition of SQL. I tried DBCC DBREINDEX and that too doesn't seem to work. Am I doing this wrong?
April 18, 2007 at 12:30 pm
we had a similar issue. i think it helps if you run maintenance on a regular basis and after a few days the indexes get to a good state
April 18, 2007 at 2:16 pm
Why not use t-sql to do it?
ALTER INDEX some_index ON some_table REBUILD
April 18, 2007 at 2:26 pm
Yeah, I've tried t-sql too but no difference. I did try saying ..REBUILD WITH (ONLINE = ON), but it came back saying I need to be running the enterprise edition. We are using standard. So I'm wondering if the edition we are using makes a difference.
April 18, 2007 at 2:40 pm
In that case you're using a feature which is only available in the enterprise edition of the software. There shouldn't be any difference in the rest of the features.
Try posting the results to this query to give us some more information about that particular index (replace table_name and index_name accordingly):
select x.*
from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('table_name'), NULL, NULL, NULL) x
joinsys.indexes i
onx.object_id = i.object_id
andx.index_id = i.index_id
where i.name = 'index_name'
April 18, 2007 at 2:50 pm
Here you go Aaron,
23 2009058193 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 83.3333333333333 6 1 6 NULL NULL NULL NULL NULL NULL NULL NULL
April 18, 2007 at 2:57 pm
If I'm reading that right there's only 6 data pages in the entire index. What's your rowcount on that table?
I've noticed that the index recommendations kind of fall apart on very small tables. Someone with more knowledge than I about the sql server internals could probably give you a reason why, however I wouldn't worry about it if your table is very small.
April 18, 2007 at 3:06 pm
Yeah, this one isn't very big I have bigger ones but for the most part they aren't very big that is why I haven't been too concerned but I do have a db that is approaching 50 gb (an growing) in devl. that will one day go live so I wanted to figured this out now before it becomes pain in the @#$.
If with standard edition I cannot rebuild online then what do I do?
April 18, 2007 at 3:24 pm
I'd only be concerned if you see this same behavior on larger tables/indexes.
As for not being able to rebuild online you need to really understand what's going on. If you can't rebuild online, you can rebuild but the index is offline. That doesn't mean the table data is unavailable. If you have low levels of fragmentation (less than 25-35% IMHO), I'd say go with using REORGANIZE instead of REBUILD which is a less intensive operation. Then schedule rebuilds for after-hours or during maintenance windows when system usage will be especially low.
April 18, 2007 at 3:39 pm
Oh, so basically what offline here means is no one will be able to use the index while this operation is on going. Ahh. Well I've been testing on a test db that only I have access to and it doesn't seem to be doing anything, but like you said it may be because it's so small to begin with. So I won't worry about it too much and move on to other things.
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply