• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

High Iindex Fragmentation on SQL Database

I have a cliet with a SQL Server 2003, they only use the server as a database server. We are trying to run a software that will backup the whole server and then upload it to the web. The software runs perfectly on their domain server but in the SQL server it creates huge incremental files due to the Maintenance Plan that is ran every night. The main problem here is that they have an index fragmentation of over 40% every day so they have to rebuild the indexes and rebuilding the indexes cause sectors on the system to change and therefore the software backs up the whole drive instead of just the files that have changed. My question is: Is it normal for a sql server to have a high fragmentation percentage every day? how can I decrease that? Is it necessary to rebuild the indexes every day?  How can i tune that up? The fragmentation is not high on all the tables and even if we run the script in the morning (before people work on them) to determine the fragmentation percentage, the fragmentation percentage is high.

Thank you in advance!
0
Nancy Villa
Asked:
Nancy Villa
  • 4
  • 3
  • 3
  • +1
5 Solutions
 
dqmqCommented:
High index fragmentation is not desireable. Iin the end, you may need to defrag daily to manage the fragmentation, but do exhaust other possibilities first.

Begin, by exploring the nature of the table activity to better understand the cause of the fragmentation: is it insert or update intensive?  Is the fragmentation spread evenly across the index or is it concetrated in certain locations? Is the fragmentation causing a performance problem (sometimes it's not an issue)?

Strategies to reduce fragmentation are:
Choose clustered indexes that are a better fit for the table activity
Adjust fill factor to minimize fragmentation


0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you have high index defragmentation, then you need to rebuild your indexes to get it fixed.
In the meanwhile, Kindly let us know the below things:

1. Do you have lot of data changes happening in your tables.
2. Do you have huge Data Deletes/Inserts happening in your tables.
3. Do you have Auto Shrink option in your database enabled which increase the Index Fragmentation. Turn it off

>> therefore the software backs up the whole drive instead of just the files that have changed.

For Backup of SQL Server related files (Data), you can do it at SQL Server level instead of third party server which can take either Incremental or Full backups faster compared to your third party software ( based upon what you have explained).
0
 
Anthony PerkinsCommented:
>>I have a cliet with a SQL Server 2003<<
Can you tell us what SQL Server version you are using.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Nancy VillaDirector of ITAuthor Commented:
the sql server version is 2005
0
 
Anthony PerkinsCommented:
You are not by any chance truncating the Transaction Log every night are you?
0
 
dqmqCommented:
ACPerkins>
I am unfamiliar with a relationship between the tran log and index fragmentation.  Please share what that might be. :>)
0
 
Nancy VillaDirector of ITAuthor Commented:
rrjegan17
1. Do you have lot of data changes happening in your tables.--- Yes
2. Do you have huge Data Deletes/Inserts happening in your tables.--Yes
3. Do you have Auto Shrink option in your database enabled which increase the Index Fragmentation. Turn it off--- It was enabled on one of the tables and the other ones had it disabled.

There are about 10k transactions a day , about 50 users continuously adding data, deleting data from 8am to 7pm.

0
 
Anthony PerkinsCommented:
dqmq,

You are right, I should have stated doing a BACKUP LOG WITH TRUNCATE_ONLY followed by a DBCC SHRINKFILE.
0
 
dqmqCommented:
>There are about 10k transactions a day , about 50 users continuously adding data, deleting data from 8am to 7pm.

With that kind of activity, some amount of fragmentation would be expected.  You can minimize fragmentation on a clustered index on a field that increases for each insert.  
0
 
Anthony PerkinsCommented:
>>Do you have Auto Shrink option in your database enabled which increase the Index Fragmentation.  Turn it off--- It was enabled on one of the tables and the other ones had it disabled.<<
Auto Shrink is a database property and not a table setting.

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
And do you have any Antivirus running in your SQL Server machine.
If so, then make sure you have excluded SQL Server files from being Scanned by Antivirus.

Also make sure that you don't have Windows defragmentation utilities configured to run on those SQL Server related drives.
0
 
Nancy VillaDirector of ITAuthor Commented:
We had to open a ticket with the software vendor because after all the troubleshooting we still haven't been able to figure out why the fragmentation is so high. Thank you all for your help on this.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now