|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Alter Index On Database Rebuild (SQL Server 2005)
I have a SQL Server 2005 database that I am trying to rebuild the indexes.
I used the ALTER INDEX ON TABLE_NAME REBUILD on each table. After completing this task I ran the DBCC SHOWCONTIG on each table. I noticed the logical scan fragmentation is set between 25 - 80. I thought ALTER INDEX ON TABLE_NAME REBUILD would change the logical scan fragmentation from DBCC SHOWCONTIG. Please help me with this issue. Thanks, Two questions, what version of SQL Server, and what filegroup is your
table built on - and what files is the filegroup built on? OK, that's three questions. Thanks. Josh On Sun, 30 Nov 2008 10:17:00 -0800, Joe K. <Joe K*@discussions.microsoft.com> wrote: Show quoteHide quote > >I have a SQL Server 2005 database that I am trying to rebuild the indexes. >I used the ALTER INDEX ON TABLE_NAME REBUILD on each table. > >After completing this task I ran the DBCC SHOWCONTIG on each table. > >I noticed the logical scan fragmentation is set between 25 - 80. > >I thought ALTER INDEX ON TABLE_NAME REBUILD would change the logical scan >fragmentation from DBCC SHOWCONTIG. > >Please help me with this issue. > >Thanks, JXStern wrote:
Show quoteHide quote > Two questions, what version of SQL Server, and what filegroup is your One more question :)> table built on - and what files is the filegroup built on? OK, that's > three questions. > > Thanks. > > Josh > > > On Sun, 30 Nov 2008 10:17:00 -0800, Joe K. <Joe > K*@discussions.microsoft.com> wrote: > >> I have a SQL Server 2005 database that I am trying to rebuild the indexes. >> I used the ALTER INDEX ON TABLE_NAME REBUILD on each table. >> >> After completing this task I ran the DBCC SHOWCONTIG on each table. >> >> I noticed the logical scan fragmentation is set between 25 - 80. >> >> I thought ALTER INDEX ON TABLE_NAME REBUILD would change the logical scan >> fragmentation from DBCC SHOWCONTIG. >> >> Please help me with this issue. >> >> Thanks, > How large are the tables (number of rows/number of pages). If the tables have less than 100 pages - you probably will see a lot of those tables still fragmented. Oops - one additional question: How much free space do you have in the data file? Jeff > How large are the tables (number of rows/number of pages). I agree, This is perhaps the most common reason ion these group where a rebuild doesn't change things (much) - too few pages to be relevant. The other would be that the index isn't really an index but a heap. Posting the actual result will clarify these things. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jeffrey Williams" <jeff.williams3***@verizon.ent> wrote in message news:Os0BWByUJHA.5856@TK2MSFTNGP03.phx.gbl... > JXStern wrote: >> Two questions, what version of SQL Server, and what filegroup is >> your >> table built on - and what files is the filegroup built on? OK, >> that's >> three questions. >> >> Thanks. >> >> Josh >> >> >> On Sun, 30 Nov 2008 10:17:00 -0800, Joe K. <Joe >> K*@discussions.microsoft.com> wrote: >> >>> I have a SQL Server 2005 database that I am trying to rebuild the >>> indexes. >>> I used the ALTER INDEX ON TABLE_NAME REBUILD on each table. >>> >>> After completing this task I ran the DBCC SHOWCONTIG on each >>> table. >>> >>> I noticed the logical scan fragmentation is set between 25 - 80. >>> >>> I thought ALTER INDEX ON TABLE_NAME REBUILD would change the >>> logical scan fragmentation from DBCC SHOWCONTIG. >>> >>> Please help me with this issue. >>> >>> Thanks, >> > > One more question :) > > How large are the tables (number of rows/number of pages). If the > tables have less than 100 pages - you probably will see a lot of > those tables still fragmented. > > Oops - one additional question: > > How much free space do you have in the data file? > > Jeff The version is version 9 (3042 - SQL Server 2005), with Primary filegroup
with a single data file. Thanks, Show quoteHide quote "JXStern" wrote: > Two questions, what version of SQL Server, and what filegroup is your > table built on - and what files is the filegroup built on? OK, that's > three questions. > > Thanks. > > Josh > > > On Sun, 30 Nov 2008 10:17:00 -0800, Joe K. <Joe > K*@discussions.microsoft.com> wrote: > > > > >I have a SQL Server 2005 database that I am trying to rebuild the indexes. > >I used the ALTER INDEX ON TABLE_NAME REBUILD on each table. > > > >After completing this task I ran the DBCC SHOWCONTIG on each table. > > > >I noticed the logical scan fragmentation is set between 25 - 80. > > > >I thought ALTER INDEX ON TABLE_NAME REBUILD would change the logical scan > >fragmentation from DBCC SHOWCONTIG. > > > >Please help me with this issue. > > > >Thanks, > > OK, then my latest "ghost" bugs are probably (!?) not involved.
For an easier way to look at fragmentation, try http://msdn.microsoft.com/en-us/library/ms188917.aspx sys.dm_db_index_physical_stats I have noticed that rebuild sometimes does not do as good a job as reorganize - pluls reorganize chases "ghosts" and rebuild does not. But neither gets you to zero fragmentation. I guess I don't really understand the process or limits, but have been doing some research recently! I presume you do have a clustered index on most/all of these tables, and that that is one (only?) index that you rebuild/reorganize on. Josh Show quoteHide quote "Joe K." <Joe K*@discussions.microsoft.com> wrote in message news:93D50834-B2AC-4E0B-A717-28FF3A69842D@microsoft.com... > > > The version is version 9 (3042 - SQL Server 2005), with Primary filegroup > with a single data file. > > Thanks, > > "JXStern" wrote: > >> Two questions, what version of SQL Server, and what filegroup is your >> table built on - and what files is the filegroup built on? OK, that's >> three questions. >> >> Thanks. >> >> Josh >> >> >> On Sun, 30 Nov 2008 10:17:00 -0800, Joe K. <Joe >> K*@discussions.microsoft.com> wrote: >> >> > >> >I have a SQL Server 2005 database that I am trying to rebuild the >> >indexes. >> >I used the ALTER INDEX ON TABLE_NAME REBUILD on each table. >> > >> >After completing this task I ran the DBCC SHOWCONTIG on each table. >> > >> >I noticed the logical scan fragmentation is set between 25 - 80. >> > >> >I thought ALTER INDEX ON TABLE_NAME REBUILD would change the logical >> >scan >> >fragmentation from DBCC SHOWCONTIG. >> > >> >Please help me with this issue. >> > >> >Thanks, >> >>  
Other interesting topics
Migrating SQL 2000 to SQL 2005, any risk I change db collation too?
Trigger and Misc. HTTP T-SQL queries (with XML support) don't work on sql server 200 Adding memory has degraded performance SQL Server 2005 Cannot refer column Schema Across Database SQL Server 2005 SSMS List Role Permission free text search in large starschemes Bitwise in SQL Server [SAN] More spindles via multiple RAID/LUNs, or fewer & more focused LUNs? Update? |
|||||||||||||||||||||||
