May 8, 2012 at 6:49 am
I wanted to rebuild indexes on a table.
When i queried on sys.partition i could find two rows for the table-- decided the table is partitioned
Details: SQL Server 2005
Indexes : two indexes on a table
Query on sys.partitions for table : 2 rows
So i rebuilt index
alter index <Indexname1> on <tablename> rebuild partition = 1
alter index <Indexname2> on <tablename> rebuild partition = 1
But recieve the error for both the queries "Cannot specify partition number in the alter index statement as the index is not partitioned."
So i had removed partition =1 from alter index and rebuild the index
alter index <Indexname1> on <tablename> rebuild
alter index <Indexname2> on <tablename> rebuild
Status : Successful
but when i check the fragmentation level on physical _io_stats, i could find the fragmentation level (>50) rows even after rebuild.
Need help to defragment the table.
May 8, 2012 at 6:54 am
There is one row per index, per table in sys.partitions if there's no partitioning in effect.
How large is the index? How many pages? I'm going to guess it's something insanely small like 2 or 4 pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2012 at 7:30 am
Hi Gail Shaw,
I didn't understand on identifying the partition of the table
If sys.partitions are having two rows one for each index mean....Table is not partioned. am i understood correct?
The index size is 4953032KB (got details from sp_spaceused query).
even after running "alter index <Indexname1> on <tablename> rebuild" on two indexes
i could find the fragmentation on sys.dm_db_physical_io_stats.
Help is much appreciated.
thank you
May 9, 2012 at 7:10 am
As i understood with partitioning...if there are any rows more than the no of indexes then there is partitioning on the table.
As my table have equal rows to no of indexes, so there is no table partition.
i had rebulid both the indexes on the but when i had verifed using physical_io_stats i could see fragmentation level still exists.
kindly suggest me.
Thank you.
May 9, 2012 at 7:15 am
If the table is partitioned, there will be multiple rows for that table in sys.partitions with different partition numbers. But unless you've explicitly partitioned the table, it won't be partitioned. It's not something SQL does behind the scenes.
How big is that specific index in pages? Query sys.dm_db_index_physical_stats
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2012 at 8:06 am
I had attached the attachment of
sys.partitions data of particular table
physical_io_stats of the table... image is representing the data after the rebuild of index on both indexes of table
May 10, 2012 at 10:19 am
There are 70 pages in that table. That's not going to defrag and it's not worth worrying about. The point that you should probably be worrying about fragmentation is in the vicinity of 1000 pages
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2012 at 1:14 am
Thanks Gail Shaw for the reply.
With respective the partititons i have provided the screen shot can you confirm.
May 11, 2012 at 5:22 am
Confirm what? The error told you clearly the table is not partitioned, you can also check that yourself by looking at whether there are any partition schemes and whether the indexes are stored on a filegroup or a partition scheme
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply