?
Solved

SQL Index Fragmentation?

Posted on 2014-10-27
4
Medium Priority
?
246 Views
Last Modified: 2014-10-27
Hi,

I have some fragmented indexes in SQL 2008 R2.

I have been reindexing them with:

DBCC DBREINDEX

or command like:

alter index ... on dbo... REBUILD

anyway not all indexes seem to reduce in fragmentation to 0.

So I am curious have I got an unreasonable expectation here?

what should I expect the outcome to be? And why might some
not reduce to 0?

btw here is the SQL I am running to work out fragmentation:

declare @current_time as nvarchar(20)

set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)

SELECT @current_time as Timestamp,
DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY OBJECT_NAME(ps.OBJECT_ID), avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

Thanks,

Ward.
0
Comment
Question by:whorsfall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40406129
Your script it's only taking in consideration the page count (1500+) but Microsoft's recommendation is to check for fragmentation level. Here's the MSDN article about it where you'll see that a reindex should be performed when fragmentation level between 5% and 30% and a rebuild should be performed when fragmentation's higher than 30%.
0
 

Author Comment

by:whorsfall
ID: 40406204
Hi,

Thanks for that so I can assume after a rebuild then the fragmentation should be 0?

Plus assuming I get all the tables by changing my query.

And what about the command dbcc dbreindex?

Thanks,

Ward
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40406218
You can't assume a fragmentation level of 0%. That depends if there's no records changes (INSERT, UPDATE and DELETE) during or immediately after the reindex. But it's almost sure that you can assume a fragmentation lower than 5%.

Don't use DBCC REINDEX because will be deprecated in the next SQL Server version. Use ALTER INDEX instead, with REORGANIZE option for avg_fragmentation_in_percent between 5% and 30% or with REBUILD option for avg_fragmentation_in_percent > 30%.
0
 

Author Comment

by:whorsfall
ID: 40406224
Hi,

Thanks for the clarification that was great :)

Ward
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 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