[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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?
0
marrowyung
Asked:
marrowyung
  • 16
  • 11
  • 8
  • +2
4 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only rebuild can defragment a database table.
0
 
Megan BrooksSQL Server ConsultantCommented:
You may or may not need to do anything. For a tongue-in-cheek flowchart approach to the "index fragmentation problem" from Brent Ozar see Should I Worry About Index Fragmentation?

The article Stop Worrying About SQL Server Fragmentation, also from Brent Ozar, is a more serious take on the subject. Depending on what is really going on, and on your storage equipment, fragmentation may not matter much.

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, for every insert -- perpetual fragmentation. This in turn stemmed in part from having tables (huge ones) that were not normalized, as well as from not staging the data long enough to collect all the information before appending to the main tables. 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.

So before you fix fragmentation by rebuilding indexes, have a look at what is causing it and whether it even matters. Having it reported by a monitoring program does not always mean that you need to do something.
0
 
marrowyungAuthor Commented:
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,
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not only shrinking fragments a table. Many insert, update and delete actions will do that as well and that's why a regular reindex job is needed.

so still about the index but not data inside that table.
No. Can be data as well if the fragmented index is a clustered index.
0
 
Megan BrooksSQL Server ConsultantCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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 !!
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Could you please provide update on this?
0
 
marrowyungAuthor Commented:
Pawan Kumar Khowal,

your script still on index fragementation but not data in the table, right? I am worrying about data fragmentation,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
marrowyungAuthor Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Index contains data only. So once you Rebuild or reorganize index , you will data quickly as it will remove fragmentation.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
marrowyungAuthor Commented:
ok, so can't duel with data fragmentation anyway.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If there's no clustered index in the table, then you can't.
0
 
marrowyungAuthor Commented:
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?
0
 
Pawan KumarDatabase ExpertCommented:
Yes for the first one.

Yes you are correct in the second case also. At leaf level of the clustered Index we will have entire data.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
1
 
marrowyungAuthor Commented:
so in this case just rebuild index anyway? again , the same and see the only one method.
0
 
Pawan KumarDatabase ExpertCommented:
Yes in this case we just rebuild or reorganize index. Yes this is the only method.
0
 
Eugene ZCommented:
<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?>
1st - do not panic when you see such

just insure you have regularly run DB mainst jobs (Reindex, if need Index defrag, update stats, etc.)
--------------------------------------------------------------------------------
The deep dive in the fragmentation of some specific table
requires some knowledge of you DB architecture -processes;:
for example

 table design,
Size,
processes using this tables
index Design (again design)
..
if your table is large - clustered index will help
also you may like to check
"Index fill factor"
https://msdn.microsoft.com/en-us/library/ms177459.aspx

----
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..

there are several "smart reindex" scripts online  that may help you (
see more
Reorganize and Rebuild Indexes
https://msdn.microsoft.com/en-us/library/ms189858.aspx
--

Sometimes Reindex does not help if table is not designed well (e.g. no index.. etc)

more:
How to identify and solve the fragmentation in SQL Server?
https://blogs.msdn.microsoft.com/batuhanyildiz/2013/04/18/how-to-identify-and-solve-the-fragmentation-in-sql-server/
0
 
marrowyungAuthor Commented:
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.
0
 
Eugene ZCommented:
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)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
marrowyungAuthor Commented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
marrowyungAuthor Commented:
Are you running Ola's reindex job daily?

yes.

on TEST DB we run it weekly.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
marrowyungAuthor Commented:
"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 !
0
 
marrowyungAuthor Commented:
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#a41834930
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
marrowyungAuthor Commented:
"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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
marrowyungAuthor Commented:
ok you mean the rare case is,  it still high fragmentation if the fragmentation  is created in between the indexoptimize one day ago?
0
 
Eugene ZCommented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
marrowyungAuthor Commented:
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 ?
0
 
Pawan KumarDatabase ExpertCommented:
Sorry for the delay in answer.

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

Regards,
Pawan
0
 
marrowyungAuthor Commented:
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..
0
 
marrowyungAuthor Commented:
tks all, it is clear that I need a clustered index.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 16
  • 11
  • 8
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now