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.

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 SQL Defrag Studio on a regular basis removes fragmentation and makes your databases run faster.

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 SQL Defrag Studio 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.

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. SQL Defrag Studio 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.

SQL Defrag Studio allows you 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.

And you won't have to write a single line of T-SQL either!

Slow databases mean your customers have to wait. Frustrated customers mean lost business. And every employee's time is money, so if they're waiting for data from your database, your business is burning money. Therefore defragmenting your database on a regular basis saves you money.

Running SQL Defrag Studio regularly can save your company money by making your databases run faster. In these days of cost cutting and budget watching, SQL Defrag Studio can speed up your databases at a fraction of the cost of an expensive server upgrade. And you can schedule SQL Defrag Studio to run defragmentation jobs on your databases at a time when it will have zero impact on your users.

Using SQL Defrag Studio means you can select only those tables you want to defrag and it will carry out the defragmentation in one go. This means you do not have to manually write the code to analyze every table, or write a cursor to defragment them. SQL Defrag Studio automates the whole process, quickly and simply.

SQL Defrag Studio also analyzes the information for each table to prioritize those with heavy fragmentation. The color coded graphical display will show you at a single glance both problem tables and those needing less attention to help you with your defragmentation plan, which is especially important for larger databases.

Please be aware that you should always check with the data owner before defragmenting any database, as the process can be very disk intensive and will almost always affect performance to a noticeable degree on even lightly used production servers containing any significant amount of data.

Norb Technologies therefore recommends that you schedule or manually run defragmentation jobs outside of normal business hours, or at some other time when you will not impact upon users' ability to work.

Whilst your data should always be safe when using SQL Defrag Studio, as with any structural change to a database, Norb Technologies always recommends that you perform a backup before running any defrag job with SQL Defrag Studio. We also recommend you defrag your hard disk before running SQL Defrag Studio defragmentation jobs.

SQL Defrag Studio does not defragment any of the system tables, either in the master database or the system tables in user databases.

Furthermore we suggest that you allow plenty of time for defragmentation so that SQL Defrag Studio can perform the operation thoroughly and completely. If you select the option to run a Full re-index then users will be unable to access data in tables which have clustered indexes as SQL Defrag Studio will create an exclusive lock on the tables.

You can quickly and easily set up defragmentation jobs to run overnight with SQL Defrag Studio's built-in scheduler.

If you decide to run the Full Re-index, which takes longer to do but is more thorough, then SQL Server will take an exclusive lock on tables with clustered indexes, but only a shared lock on non-clustered indexes. With an exclusive lock, your users have no access to the data, but with a shared lock users will be able to see the data but not make modifications.

If you have a lot of modifications, such as record deletes or updates, then fragmentation is likely to be higher and may take longer on these tables.

After using SQL Defrag Studio on a regular basis you will soon learn how long it needs to perform defragmentation so that time slots can be allocated as part of your regular performance tuning plan.

Once you have built the indexes there is still work to be done. As your data sets grow over time, SQL Server will continue to rebuild indexes and move data around as efficiently as possible. This happens in a number of ways, but the result is that you will need to perform maintenance on your indexes over time despite all of the automatic functionality built into SQL Server.

So, how often you use SQL Defrag Studio depends on how often your databases become slow due to fragmentation. This is something you will need to work out for your individual server set-up, but we recommend you make the most of SQL Defrag Studio's ability to analyze all the tables in a chosen database and highlight those with high fragmentation.

This will probably guide you towards tables with heavy activity (inserts, updates and deletes) that are more often likely to suffer the slowness caused by fragmentation. This means you can use SQL Defrag Studio's scheduler to perform defragmentation as often as you need on selected tables in your database.

This should be run before you use SQL Defrag Studio to defragment your database tables. Defragmenting your hard disk drive is always a good idea, but especially when the drive stores database files. From our experience it is easy to neglect this area when addressing database performance problems, as it is often assumed that databases are sized correctly when they are first created, or never grow. In reality, many databases are created using SQL Server's default settings which means that the .mdf file(s) in which the database is stored is continually being extended.

A hard disk defragmenter will re-arrange the file(s) which contain your database, so that they can be read with the minimum number of I/O operations, which in turn returns the maximum benefit from SQL Defrag Studio.

Here's an important tip to remember: to gain the best performance from SQL Defrag Studio, it is important that you always run disk defragmentation operations first.

The reason behind this is simple. If you defragment your database first and then defragment your hard disk, the order of the clustered index pages on disk will be changed as a natural side effect of the disk defragmentation process. This effectively undoes the index defragmentation performed by SQL Defrag Studio, meaning that you will have to run SQL Defrag Studio again.

Whilst none of this will harm your data, it will certainly require more time and effort, particularly on large databases, so is worth remembering before you start.