October 17, 2021 at 3:16 am
The picture is showing the results that I used sp_spaceused for OldTable and NewTable, I used "select into NewTable from OldTable", and created the same indexes on newtable with the OldTable, as per the data of the picture, the OldTable taking more space than the NewTable, so seems that the OldTable has many many fragmentation space, now How can I remove the fragmentation space? seems that the data has very big data fragmentation, how to remove it ? many thanks!
October 17, 2021 at 3:43 pm
Have you tried rebuilding the indexes on the old table - with the same fill factor as the new table? I would review each index and see what the fill factor has been set to on the old table.
The other concern is whether this table has a clustered index or not. If it doesn't have a clustered index and there are expansive updates and/or deletes from the table, the previous allocated will not be released. If the table does not have a clustered index and you cannot add one - try running an ALTER TABLE ... REBUILD.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 18, 2021 at 1:34 am
thank you Jeffrey Williams, the table has clustered index, but when I used "alter table oldtable rebuild" , the table released a lot space, many thanks!
when I did this operation, there is another question, when I ran "select * from sys.sysindexes" to check which tables have more rows,
it rans very very slow,but I ran it on another server, it runs very faster, how could I can fix the problem except for restarting SQL server service? thanks!
October 18, 2021 at 3:07 pm
thank you Jeffrey Williams, the table has clustered index, but when I used "alter table oldtable rebuild" , the table released a lot space, many thanks!
when I did this operation, there is another question, when I ran "select * from sys.sysindexes" to check which tables have more rows,
it rans very very slow,but I ran it on another server, it runs very faster, how could I can fix the problem except for restarting SQL server service? thanks!
There are a thousand reasons why this might be true. How many rows in each table? How does the hardware compare? What is the workload on the server? How many rows in the table? Etc, etc, etc.
This is a bit like asking why the Blue car gets better gas mileage than the Red car. Without knowing make, model, engine size, transmission and drive configuration, and driving habits, it's impossible to tell.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2021 at 3:29 pm
Dear Jeff Moden, thank you for you kind help! what you said is quite right.
if I ran it on the same server, sometimes it works fine, but sometimes not , because it is system's object. it is my confusion? thanks
October 18, 2021 at 4:32 pm
Dear Jeff Moden, thank you for you kind help! what you said is quite right.
if I ran it on the same server, sometimes it works fine, but sometimes not , because it is system's object. it is my confusion? thanks
It doesn't matter if it's a system object or not. It just proves that "It Depends" is frequently the correct answer and that "Always look eye" is the correct action to take.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2021 at 4:34 pm
Getting back to the original post... you do know what sys.dm_db_index_physical_stats() is for, correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2021 at 7:00 am
This was removed by the editor as SPAM
October 25, 2022 at 2:16 pm
Getting back to the original post... you do know what sys.dm_db_index_physical_stats() is for, correct?
Ok... After a year, I'll take that as a "No". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply