Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

table fragmentation

hi all,

a monitoring tool we use detected that some table has at least 90 percent fragmented, other than index rebuild, for table data, how can we solve it?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Only rebuild can defragment a database table.
SOLUTION
Avatar of Megan Brooks
Megan Brooks
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

Vitor Montalvão,

rebuild means rebuild index ,right? I also don't see this kind of thing before and usually we don't shrink a database as we all knew that it create high index fragementation.

Megan Brooks,

" "index fragmentation problem""

so still about the index but not data inside that table.


"The fragmentation I have encountered in large databases has to a considerable degree been the result of application design that inserted and then repeatedly updated the data for every record,

very good statement. tks.

but seems all company will  have this problem by this sense. again our monitor tools, idera DM detect that.

"as well as from not staging the data long enough to collect all the information before appending to the main tables. "

please clarify that bit further.

"Rebuilding the indexes would not resolve fragmentation in the active portions of the data that mattered the most at any given time, because every record was fragmented as it was created.
"

oh.....

"Having it reported by a monitoring program does not always mean that you need to do something.


yes.. I just worry about data inside for this case as I don't hear anything like this for SQL server except index rebuild,
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To clarify about staging, developers can learn to write queries without ever learning what goes on within the database as a result of those queries, particularly when data modification is involved. If there is no DBA or database developer to review their work, and if the database eventually grows to be extremely large (e.g. terabytes), it can be quite a mess to fix. The issues can include excessive I/O, excessive blocking, and wasted disk space.

If you have high-volume complex data coming in and being combined from several sources, the design may need to include staging tables that hold the it until it is all in, so that what is finally inserted to the main tables is complete, particularly if the size of each record changes while the data is being assembled and/or the data that changes is indexed.

If the data that is changing is denormalized, resulting in many rows having to be updated for a single value change, the problems are compounded. This can happen, for example, when the incoming data takes the form of large batches of rows for which some information is not known until all rows in a given batch have been received, and the batch-level information has not been normalized out into its own tables. Receiving the last row of a batch, then, may result in a cascade of updates to all the rows in the batch. If the batch is assembled in a staging table then it really doesn't matter since the space will be released when the data is copied to the final destination, but doing all the work in the final, target tables can result in perpetual fragmentation.
Query to check /detect fragmentation.

CREATE CLUSTERED INDEX Ix_ID ON TestClusteredKeyFragmentation (ID)
GO
 

INSERT INTO TestClusteredKeyFragmentation DEFAULT VALUES
GO 10000

 
--Execute the below query and check the details
SELECT
   OBJECT_NAME (ips.[object_id]) AS 'Object Name',
   si.name AS 'Index Name',
   ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',
   ips.page_count AS 'Pages',
   ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
FROM sys.dm_db_index_physical_stats (DB_ID('InMemory'), NULL, NULL, NULL, 'DETAILED') ips
CROSS APPLY sys.indexes si
WHERE
   si.object_id = ips.object_id
   AND si.index_id = ips.index_id
   AND ips.index_level = 0
   AND OBJECT_NAME (ips.[object_id]) = 'TestClusteredKeyFragmentation'
GO

Open in new window


Steps to over come the same..

Index Rebuild-It drops the existing Index and recreates the index


USE [DBName]
GO

ALTER INDEX ALL ON tableName REBUILD
GO

Open in new window


Index Reorganize-It physically reorganizes the leaf nodes of the index

USE [DBName]
GO

ALTER INDEX ALL ON tableName REORGANIZE
GO


--

Open in new window



Enjoy !!
@Author - Could you please provide update on this?
Pawan Kumar Khowal,

your script still on index fragementation but not data in the table, right? I am worrying about data fragmentation,
your script still on index fragementation but not data in the table, right?
If the table has a clustered index then it will defragment the data as well.
ok but what if it is non clustered index, then can only fix index fragmention..

Pawan Kumar Khowal,

your script only duel with index, I am focus on data.
@Author - Index contains data only. So once you Rebuild or reorganize index , you will data quickly as it will remove fragmentation.
but what if it is non clustered index, then can only fix index fragmention..
Sure. Indexes are objects separated from data. That's by design in any database system (not only SQL Server) so you can't expect miracles here.
ok, so can't duel with data fragmentation anyway.
If there's no clustered index in the table, then you can't.
ok, if as long as there are a clustered index, which usually created when we are defining PK, then we are fine by rebuilding the index?

as Clustered index are data page itself you mean that, right?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
as Clustered index are data page itself you mean that, right?
Right. That's why you can only have ONE clustered index by table so data will be sorted by the clustered index definition.
so in this case just rebuild index anyway? again , the same and see the only one method.
Yes in this case we just rebuild or reorganize index. Yes this is the only method.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
EugeneZ,

"just insure you have regularly run DB mainst jobs (Reindex, if need Index defrag, update stats, etc.)"

we have done that and that's why I am wondering why it still exists. we use the indexoptimize SP from ola solution.

fragemenation level is the standard one. 5 and 10

I think I should make it 1 and 4 to let it rebuild index for more table, agree ?


"if your table is large - clustered index will help "

why it is related? I guess no matter what size of table is , clustered index will help anyway ?

""Index fill factor"
https://msdn.microsoft.com/en-us/library/ms177459.aspx"

I think it only helps on page split, right? as I have other ticket on page split.

"you need to be careful with reindex of very large table (s) - specially with "off-line" option
it can negatively affect system if not planned and tested.."

yes but it is the fastest for the whole process, agree? online one can make the whole process several hours longer.
if your tables is fragmented after reindex
it may not have  clustered index...
====


fill factor - can in some cases address fragmentation ---less pages...
for example for Data warehouse 100% fill factor recommended to use
for another cases you may see difference - improved perform with 60 -90% ( 80% -90  most popular)

cluster index -- on a very small tables is optional

or  -- if index helps -. keep it
if it does not - drop it
--

-reindex: offline - > faster, needs extra space, if you run against large tables you need to make sure that it is done before business day.. You can set some another reindex  job just for a very large table(s)
I think I should make it 1 and 4 to let it rebuild index for more table, agree ?
That's only loosing time. What's the problem with a 1% fragmentation table?
Vitor,
I mean make it and see if this kind of message gone, but yes, no need to worry so much about that message.

I just wondering what makes that as I said , i use the ola solution every week, still see this kind of message, surprise, so rebuild more index then...

EugeneZ,,

"it may not have  clustered index..."

yeah, as other contributor here also said that. need that to rearrange the real physical data page, right?

"or example for Data warehouse 100% fill factor recommended to use"

why ? they don't need insert./update on the data warehouse table anymore as all should be in the transaction database tier instead of DW tier ?

"cluster index -- on a very small tables is optional"

because it is too small anyway and SQL server can read them all together without read more data page?

"if you run against large tables you need to make sure that it is done before business day.. You can set some another reindex  job just for a very large table(s)"

yes, with separate schedule you mean that? we here so sth like that but has been tested for a long time.

SQL server diff from one server to other server.
I just wondering what makes that as I said , i use the ola solution every week, still see this kind of message, surprise, so rebuild more index then...
Are you running Ola's reindex job daily? The purpose of his script is to run it daily and not weekly.
Are you running Ola's reindex job daily?

yes.

on TEST DB we run it weekly.
Mind that what's good in Ola's solution is that it will only reindex if the fragmentation level matches the parameter values (or the default values if you don't pass any parameter) and that's why it should be run daily to not let tables to be highly fragmented during long time.
"Mind that what's good in Ola's solution is that it will only reindex if the fragmentation level matches the parameter values (or the default values if you don't pass any parameter) "

yeah, we use 5 and 30 as 1 and 4 take a long time.

"and that's why it should be run daily to not let tables to be highly fragmented during long time."

we did it but DM still detect the table fragmentation, surprise !
hi,

if you have a friends run offce 365 home and outlook 2010 together, please help on that:
https://www.experts-exchange.com/questions/28974976/outlook-2010-and-office-365.html?anchor=a41834930&anchorAnswerId=41834930#a41834930
we did it but DM still detect the table fragmentation, surprise !
Not really a surprise. It depends on how much a table can be updated daily. I remember to work with a table that has 4 millions new records by hour and they were only kept 4h in the table before being deleted. With these kind of solutions a table can be highly fragmented in a single day.
"Not really a surprise. It depends on how much a table can be updated daily"

you mean the detected table is the one hasn't been ola indexoptimized and it just get high fragmentation BEFORE  ola can detect it and rebuild it ?

" they were only kept 4h in the table before being deleted. With these kind of solutions a table can be highly fragmented in a single day."

I don't understand this sorry.
you mean the detected table is the one hasn't been ola indexoptimized and it just get high fragmentation BEFORE  ola can detect it and rebuild it ?
I mean that in rare occasions you'll find that a table can be highly fragmented in just a day. It depends on the table purpose and usage.
ok you mean the rare case is,  it still high fragmentation if the fragmentation  is created in between the indexoptimize one day ago?
you can reindex - > and update -insert can fragment table.
it is related Table design --> proper clustered index, etc

in any case -- if you do not have performance problems -- it can be acceptable for a specific table -process
If your table is very large and you have heavy DML operation on that table on daily basis then only it will get fragmented daily. In that case You can rebuild / reorganize your indexes on that table only.

I am providing you the scripts again if you need.

Query to check /detect fragmentation.

CREATE CLUSTERED INDEX Ix_ID ON TestClusteredKeyFragmentation (ID)
GO
 

INSERT INTO TestClusteredKeyFragmentation DEFAULT VALUES
GO 10000

 
--Execute the below query and check the details
SELECT
   OBJECT_NAME (ips.[object_id]) AS 'Object Name',
   si.name AS 'Index Name',
   ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',
   ips.page_count AS 'Pages',
   ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
FROM sys.dm_db_index_physical_stats (DB_ID('InMemory'), NULL, NULL, NULL, 'DETAILED') ips
CROSS APPLY sys.indexes si
WHERE
   si.object_id = ips.object_id
   AND si.index_id = ips.index_id
   AND ips.index_level = 0
   AND OBJECT_NAME (ips.[object_id]) = 'TestClusteredKeyFragmentation'
GO

Open in new window


and fix is

Index Rebuild-It drops the existing Index and recreates the index


USE [DBName]
GO

ALTER INDEX ALL ON tableName REBUILD
GO

Open in new window

hi,

I am focusing on table defragmentation, so they are saying if clustered index is here it can be solve, or add a clustered index.

do you agree on that ?
Sorry for the delay in answer.

Yes if you do not have a clustered index then please create clustered index.
Hi Marrowyung,
Any feedback on this for us?

Regards,
Pawan
sorry in US for 12 days and can't sleep at all. I believe it should be the case as,  what else, I might come back  for that later if I find we have clustered index and we rebuild index everyday, but still has that alert.

you guys might need me to ignore about that man..
tks all, it is clear that I need a clustered index.