Database Fragmentation with Visual Defrag

Visual Defrag is a defragmentation tool, designed specifically for Microsoft SQL Server database administrators and developers to defragment their indexes.
Visual Defrag allows DBAs to view all the indexes in a database, see how heavily fragmented each index is, and choose whether to defragment one index, a selection of indexes or all indexes.

What is Database Fragmentation?
From time to time, depending on how your data is used, you will need to reorganize the indexes on your tables. Indexes don’t maintain their fill factor and become fragmented and user queries will suffer the effects of reduced performance.
The tables in your database become fragmented when data modifications are made, such as inserts, updates and deletes. These changes to your tables are not usually made equally among the rows, so the page fullness will vary according to the characteristics of the data, creating hot spots within your indexes.

Fragmentation happens when the logical order of the pages does not match the physical order of the pages, or when pages that are logically sequential are not located on the same extent. This means your indexes become less efficient and queries will take longer to return results to your users.

How Does Fragmentation Affect My Databases?
When you run queries that scan part or all of a table, if you have fragmentation on that table, it causes additional page reads, slowing down parallel data scanning and reducing the performance and speed of your database.
For example, for SQL Server to retrieve all the pages of a fragmented table in their logical order, the disk drive’s heads have to jump around on the physical disk, rather than performing just contiguous read-only operations. This ‘latency’ causes decreased performance on your database and frustration and complaints from your users. Using Visual Defrag to regularly remove fragmentation will make your databases run faster.

Why is Fragmentation a Problem?
When fragmentation is present, and particularly if the fragmentation is heavy, perhaps on a table with data that is constantly being modified, users will find the performance of the table is lower than it should be and queries will be slower.
With internal fragmentation, if SQL Server needs to insert a new row on a full page, it will allocate a new page and split the full page so that half the rows are on one page and the other half on the other page.

Splitting can be an expensive I/O operation and leads to external defragmentation because more often than not, the new page will not be stored contiguous with the original page which has been split. The longer SQL Server takes in splitting pages, or jumping around the disk to return query results, the longer your users must wait.

This is how Visual Defrag can help. By using the built-in scheduler you can plan defragmentation jobs to take place on heavily used tables as often as you need, and at times when users will not be affected.

What is Defragmentation?
User modifications, such as inserts and deletes on the data in your tables, create pages splits. Performance will be lost as it takes SQL Server longer to return results of queries as these splits mean your indexes become less effective. Defragmentation is the process of putting your table indexes back in order and removing the page splits, so queries run at the speed you would expect and your users are not kept waiting.
Defragmentation should be a major part of your database maintenance plan to make sure frequently modified tables are regularly defragmented to keep them running at optimum speed. Visual Defrag enables you to regularly defragment your database tables at times when your users are not using the database by creating scheduled defrag jobs. This keeps your databases performing at their optimum speed and your users will benefit from faster data access.

Source: http://www.norbtechnologies.com/products/visualdefrag/faqs-defrag-sql-server.aspx

About index defrag: http://blogs.digineer.com/blogs/larar/archive/2006/08/16/smart-index-defrag-reindex-for-a-consolidated-sql-server-2005-environment.aspx

http://sqlfool.com/2009/03/automated-index-defrag-script/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s