.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Index fragmentation script runs but yet fragmentation remains high

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

I have an index maintenance job that runs frequently, what I find is that despite the fact that the script runs, fragmentation is still quite high on the tables. I am using the standard approach of having  a rebuild if fragmentation is over 20 and if its between 5 and 20 then it reorganises the page.

Is this usual for such a behavior ?

View Complete Post

More Related Resource Links

Check Index Fragmentation query

Hi, I have ran the following query to view the fragmentation on my large table with 1000+ pages. Now the results I got are moslty at index level 0 and 1 and most objects are fragmented 90+%. My question to the experts is that the query is displaying fragmentation mostly at Index level 0 and 1. Do I need to look or just Level = 0 is fine? The query is SELECT DB_NAME(SDDIPS.[database_id]) AS [database_name], OBJECT_NAME(SDDIPS.[object_id], DB_ID()) AS [object_name], sddips.[object_id] as [Object_ID] ,SSI.[name] AS [index_name], SDDIPS.partition_number, SDDIPS.index_level ,SDDIPS.index_type_desc, SDDIPS.alloc_unit_type_desc, SDDIPS.avg_fragmentation_in_percent], SDDIPS.[page_count] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'detailed') SDDIPS INNER JOIN sys.sysindexes SSI ON SDDIPS.OBJECT_ID = SSI.id AND SDDIPS.index_id = SSI.indid WHERE SDDIPS.page_count > 1000 AND avg_fragmentation_in_percent > 15 AND index_type_desc <> 'HEAP' ORDER BY OBJECT_NAME(SDDIPS.[object_id], DB_ID()), index_id

script to check fragmentation


Dear all,

Can anyone provide me the complete script to check the average fragmentation for each table (with table name) in a DB?

I have tried this but seem only retrive data for one of the table. I would like to show ALL tables.

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'xxxx.master'),
  JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;








Primary, Clustered, IDENTITY key index fragmentation


We have a table with an IDENTITY primary key and a clustered index on this key.  The SQL to create the table is shown below.  After inserting 100 records or less (records aren't deleted or updated, typically), the index is highly fragmented.  This eventually becomes a crucial bottleneck because the table is joined to another table with millions of records to produce a view.

It's easy enough to rebuild the index, but it would be optimal to prevent the fragmentation in the first place.


1) What causes the fragmentation? Is it the fact that the table contains a nvarchar(max) field?  Is it the clustered index?

2) Are there ways to prevent fragmentation in the first place, and if so, how?  I have tried setting PAD_INDEX to ON, and using FILLFACTORS of 30, 50, 80, 90, and 100, all to no avail.  I have also tried converting the nvarchar(max) to a nchar(400).

Thank you.

SQL Server Version: 2008

SQL to create the table, index:

CREATE TABLE [dbo].[MyStrings](
    [StringId] [int] IDENTITY(-2147483647,1) NOT NULL,
    [SecondaryKey] [binary](20) NOT NULL,
    [Text] [nvarchar](max) NOT NULL,

merge replication system tables fragmentation issue

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

fragmentation in merge replication

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

Fragmentation and performance

From what I read on the web fragmentation can have a great impact on performance. So I've done a test... "luckily" some of my tables where highly fragmented. So I made my test with a table that was 98% fragmented and rebuilt its clustered index with the alter index command. fragmentation dropped to 0%. BUT, my test results did not show any improvement in performance (I calculated the elapsed time that took for a simple query on that table with a where clause on the first column of the clustered index. I used "dbcc dropcleanbuffers" before each run). "Problem" is that it took the same time before defragmentation (through rebuild) as after defragmentation. Is my test correct? If not, how do I check the performance fragmenation correctly? thanks, Dror

Difference between truncate and drop table on database fragmentation


I have a group of developers that are creating stored procedures, in them they are dropping a table and re-creating the table and then continuing with the processing.  My question is if there is any difference between the effect of dropping a table and truncating the table on database fragmentatiom.




Rebuild indexes based on fragmentation.


Does anyone have the script to rebuild/Reorg the indexes based on the fragmentation for a database in sql server 2005




SSMS Table designer does not script index filters



In SQL Server Mgt Studio (SSMS 10.50.1600.1, SQL 10.50.1600), under the table designer, when a table has a filtered index on it, if the modification does need to rebuild the table, the filter is missing on the index.

To reproduce the bug:

-          Create a table

Was forced to shrink a log file. How do I prevent future fragmentation

I read that one of the side effects of truncating and shrinking a large log file is that it will incurr fragmentation as it grows.  What steps can I take to prevent this?  How do I monitor the log files internal fragmentation as it grows?

ASP.NET Best Practices for High Performance Applications

This article lists the techniques that you can use to maximize the performance of your ASP.NET applications. It provides common issues, design guidelines, and coding tips to build optimal and robust solutions.

High-Performance .NET Application Development & Architecture

It has always been a goal of project architects to plan an effective strategy from the ground up in regards to an new application. All relevant factors are taken into consideration with respect to the application, from its design and layout to a functional website infrastructure. Pre-.NET strategies and design guidelines still effective now were developed with Microsoft's DNA (Distributed interNet Application) platform. This model successfully served the purpose of architecting N(any number of)-Tier (levels) applications. In its basic sense, as in most robust, distributed applications, you'll architect 3 main layers or Tiers: presentation, business rules and data access.

High Performance ASP.NET - Speeding Up ASP.NET Pages

have been writing a series of blog posts, which I have named High Performance ASP.NET Websites Made Easy! There is no rhyme or reason to the order of these posts and certainly can be read in any order:

Developing High Performance and Scalable ASP.NET Websites
Avoid Chatty Interfaces Between the Tiers in Your ASP.NET Web Application
ASP.NET Page Profiling - Page Tracing - High Performance and Scalable ASP.NET Websites Made Easy
Web Applications: N-Tier vs. N-Layer - Benefits and Trade-Offs
Query Analyzer - Sql Server Database Indexes and Execution Plans

10 Tips for Writing High-Performance Web Applications

Writing a Web application with ASP.NET is unbelievably easy. So easy, many developers don't take the time to structure their applications for great performance. In this article, I'm going to present 10 tips for writing high-performance Web apps. I'm not limiting my comments to ASP.NET applications because they are just one subset of Web applications. This article won't be the definitive guide for performance-tuning Web applications-an entire book could easily be devoted to that. Instead, think of this as a good place to start.

Rendering ASP.NET Script References into the Html Header

One thing that I've come to appreciate in control development in ASP.NET that use JavaScript is the ability to have more control over script and script include placement than ASP.NET provides natively. Specifically in ASP.NET you can use either the ClientScriptManager or ScriptManager to embed scripts and script references into pages via code.

*.axd script references not being rendered to browser


I'm running VS2010 with .net 4.0, and am running into issues with the .net ajax script tags mysteriously not being rendered to the browser.

So far I've reduced it to a scenario where I set the DataSourceID property of a databound control.  More specifically, I've only tested it on a Gridview and a DropDownList.

This can be reproduced fairly easily on my machine by following these steps:

1. Create a new empty Web Application Project

2. Create a new Web Form

3. Add a ScriptManager inside the form

4. Add a DropDownList and a DataSource (I used an ObjectDataSource)

5. Set the DataSourceID property of the DropDownList to the ID of the DataSource.  This can be done either declaratively on the .aspx page, or in the code behind in the aspx.cs page, and both produce the same results.

6. Open the page in a browser and watch as you get a javascript error stating "Sys is not defined" and notice that the page source contains no script tags referencing *.axd scripts.

Here's my source code showing my set up:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ScriptResourceFAIL.Default" %>


Client script not running


Dear all,

in my MVC 2 application, and in the shared Master Page I wrote this little script for menu handling :

<script src="../../Scripts/jquery-1.4.1.js" type="text/javascript"></script>
<script type="text/javascript"> $(document).ready(function () {   
     $('#nav li').hover(  
         function () {  
             //show its submenu  
             $('ul', this).slideDown(100);  
        function () {  
           //hide its submenu  
            $('ul', this).slideUp(100);           

The script, that shows menu items when the

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend