Solved

table fragmentation

Posted on 2016-09-29
40
75 Views
Last Modified: 2016-11-06
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
Comment
Question by:marrowyung
  • 16
  • 11
  • 8
  • +2
40 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41821295
Only rebuild can defragment a database table.
0
 
LVL 13

Assisted Solution

by:Megan Brooks
Megan Brooks earned 83 total points
ID: 41821363
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41821372
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 251 total points
ID: 41821377
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
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41821410
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41821434
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41822956
@Author - Could you please provide update on this?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41826073
Pawan Kumar Khowal,

your script still on index fragementation but not data in the table, right? I am worrying about data fragmentation,
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41826117
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41829634
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41829636
@Author - Index contains data only. So once you Rebuild or reorganize index , you will data quickly as it will remove fragmentation.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41829676
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41831288
ok, so can't duel with data fragmentation anyway.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831292
If there's no clustered index in the table, then you can't.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831458
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
 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 83 total points
ID: 41831463
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831506
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41832906
so in this case just rebuild index anyway? again , the same and see the only one method.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41832907
Yes in this case we just rebuild or reorganize index. Yes this is the only method.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 83 total points
ID: 41835505
<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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:marrowyung
ID: 41838135
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 41838173
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41838180
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41839463
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839581
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41839588
Are you running Ola's reindex job daily?

yes.

on TEST DB we run it weekly.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839598
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41839605
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 41839607
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839614
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41839791
"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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839800
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41839828
ok you mean the rare case is,  it still high fragmentation if the fragmentation  is created in between the indexoptimize one day ago?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 41840116
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41840130
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41851421
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41871533
Sorry for the delay in answer.

Yes if you do not have a clustered index then please create clustered index.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41871748
Hi Marrowyung,
Any feedback on this for us?

Regards,
Pawan
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41876542
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
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41876545
tks all, it is clear that I need a clustered index.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now