Index Fragmentation

Fragmentation:- Fragmentation can be defined as condition where data is stored in a non continuous manner. In can be defined into two types

1. Internal Fragmentation
2. External Fragmentation

Internal Fragmentation:- In this fragmentation, there exists a space between the different records within a page. This is caused due to the Insert, delete or Update process and due to this Index takes more space than it needs to and it result in more read operation during scanning. It can also be caused due to the low value of fill factor of the page which determine how much % of the page should be used for storing the records. 


External Fragmentation:- In this fragmentation, the extents of the table is not physically stored continuously on the disk which causes the jump from one extent to another extent which takes longer time. 

Both the fragmentation can be resolved by Rebuilding or Reorganization of the indexes of the tables. 

Rebuild and  Reorganization of Indexes:- SQL Server has the ability of maintaining the indexes whenever we makes changes (update, Insert, Delete) in the tables. Over a period of time, the may causes thefragmentation on the table in which  the logical ordering based on the key value pairs does not match with the physical ordering inside the data files. This causes the degradation of the performance of the SQL Query.To solve this problem of fragmentation, we use rebuilding or reorganization of the indexes.

In case of Rebuilding, it drop the particular index and again recreate it.It removes fragmentation, reclaims the disk space by compacting the pages based on the specified or existing fill factor setting, and again reorders the index rows in those contiguous pages. We can rebuild all the indexes of the table within a single transaction by specifying the ALL with it.

ALTER INDEX [Indexname] ON [tablename] REBUILD

ALTER INDEX ALL ON [tablename] REBUILD

In case of Reorganization, it defragments the leaf level nodes of indexes by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes.  It uses minimal system resources and also compact the index pages

ALTER INDEX [Indexname] ON [tablename] REORGANIZE

ALTER INDEX ALL ON [tablename] REORGANIZE

Rebuild should be used when the fragmentation index is greater than 30% and reorganization option should be used when the fragmentation index is between 5% and 30%. Rebuilding of an Index can be done online or offline. But to achieve the availability of the index, rebuilding should be done online. Reorganization can be done online.

Find fragmentation of the database for all the indexes

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO




Index Page level Locking is Disable ERROR

Introduction

When we are working with production database, sometime we find a specific message in maintenance plan log.

The index [IndexName] on table [TableName]

cannot be reorganized because page level locking is disabled. 

This will cause our Index Reorganize steps fails. In this article we are trying to explain it and solve it.

Why this Errors Occurs

By default the page level locks should be enabled for Index. The main causes of this error are the Index Reorganize step has hits an Index that have page lock disables. This somehow your Index Page Lock is disabled.

How to Solve it

Finding the Table Name and Index Name where Page Lock is Disable

SELECT T.Name AS [Table Name], I.Name As [Index Name]

FROM   sys.indexes I

       LEFT OUTER JOIN sys.tables T

       ON I.object_id = t.object_id

WHERE  I.allow_page_locks = 0

       AND  T.Name IS NOT NULL;

Now Use Alter Statement to Allow Page Lock On

The syntax is mentioned bellow

ALTER INDEX <Index_Name> ON <Table_Name>

SET (ALLOW_PAGE_LOCKS = ON);

To generate the Alter Script Automatically

SELECT 'ALTER INDEX ' + I.Name + ' ON ' +  T.Name +

       ' SET (ALLOW_PAGE_LOCKS = ON)' As Command

FROM   sys.indexes I

       LEFT OUTER JOIN sys.tables T

       ON I.object_id = t.object_id

WHERE  I.allow_page_locks = 0

       AND T.Name IS NOT NULL;

Hope you like it.

INDEX not implemented when a Materialized view is created from another

Introduction

There is a limitation of view that we must understand is if a materialized view is created by another materialized view, in this case we cannot configured index on Second Materialized view

Please try to understand the bellow pictorial diagram.

Materialized View 1  à

                              Used to Create  à  

                                                Materialized View 2  ß Cannot Configure Index

This article is related to it.

Example to understand

Step - 1 [ Create the Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

       (

         EMPID    INT         NOT NULL,

         EMPNAME  VARCHAR(50) NOT NULL,

         EMPGRADE CHAR(1)

       );

GO

Step – 2 [ Insert Some Records ]

INSERT INTO  [dbo].[tbl_EMPLOYEE]

      (EMPID, EMPNAME, EMPGRADE)

VALUES(1, 'Joydeep Das', 'A'),

      (2, 'Sukamal Jana', 'A'),

      (3, 'Sangram jit', 'B'),

      (4, 'Souman Bhowmik', 'C');

GO

Step – 3 [ Create First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_1', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_1];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_1]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[tbl_EMPLOYEE];

GO 

Step – 4 [ Create Second VIEW by Using First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_2', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_2];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_2]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[view_EMPLOYEE_1];

GO

Step – 5 [ Creating the UNIQUE CLUSTERED Index on Second VIEW and Error occurs]

CREATE UNIQUE CLUSTERED INDEX IX_view_EMPLOYEE_2

ON [dbo].[view_EMPLOYEE_2](EMPID);

Msg 1937, Level 16, State 1, Line 2

Cannot create index on view 'MATRIXSYSDB.dbo.view_EMPLOYEE_2'

because it references another view 'dbo.view_EMPLOYEE_1'.

Consider expanding referenced view's

definition by hand in indexed view definition.

Reason for That

The reason for this is that another view over a view is difficult to maintain

What to do to solve it

Use the SELECT statement of first view within the second view.

Why Fragmentation Exists after INDEX REORGANIZE or REBUILD

Introduction

When we are working with Index fragmentation, a lot of query comes in mind. One of the questions that one of my friends asks me I would like to explain it and try to answer it. I think that lot of professional have the same type of query in mind.

Questions

As we all know that to see the Index defrag percent (from MS SQL 2005) we use

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL,NULL) 

But after defragment the index by using REORGANIZE or REBUILD we find some of the index is till fragmented > 66%. It is especially for Master tables. Where there are low numbers of records exists.

Why it occurs

In SQL Server, the page size is 8 KB. Extents are the basic unit. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

Extents are two types

·         Uniform extents are owned by a single object; all eight pages in the extent can only be             used by the owning object.

·         Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can       be owned by a different object.

References: http://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx

When a new table is created MS SQL server does not know about how many rows will be entered in the table. When we add data MS SQL Server allocate a sing page of 8KB from mixed extents. Once this table has at-least 3 extents then the MS SQL Server will start issuing space from uniform extents and 1 extent at a time instead of a single page.

This dangerous that for a small number of records lots of space will be wasted from uniform extents.

When we calculate the fragmentation, these single page allocations drive the fragmentation level up.

What to do

That is the reason we shouldn't look at the fragmentation of the small tables.

MSFT documentation says:

Fragmentation will have an impact on the performance only if the number of pages in the table/index is around 1000 pages.

Fixing the Fragmentation of HEAPS

Introduction

A table objects that not containing any Clustered Index is called Heap. It does not mean that this table has no indexes. The table may contain several non clustered indexes on it.

I personally not recommended any kind of Heap table in database. But sometime we find it as for functional support. I do not understand why we create the Heap table? If we do not have any option to primary key we can use the surrogate key.

The problem lies when a Heap table is highly fragmented.

This article is trying to identify the Heap table fragmentation issue and try to defragment it.

How to Identify Heap table Fragmentation Percent

To identify whether your heap table is fragmented, we need to either run DBCC SHOWCONTIG (2000 or 2005) or use the DMV sys.dm_db_index_physical_stats (2005 and later)

DECLARE @db_id     SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2008R2');

SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.tbl_EMPLOYEE');

IF @object_id IS NULL

  BEGIN

     PRINT N'Invalid object';

  END

ELSE

  BEGIN

      SELECT *

      FROM   sys.dm_db_index_physical_stats(@db_id,

                                            @object_id,

                                            0,

                                            NULL ,

                                            'DETAILED');

  END

GO

The third parameter in sys.dm_db_index_physical_stats is for index_id, and we should use zero (0) when the table is a heap.  If you use zero and the table is not a heap, you will receive an error.

How to Defrag Heap

1.    Using the REBUILD option of ALTER TABLE (Only MS SQL 2008 and higher)

ALTER TABLE dbo.tbl_EMPLOYEE REBUILD;

GO

2.    We will need to select a column to create the clustered index on; keeping in mind this will reorder the entire table by that key.  Once the clustered index has been created, immediately drop it.

CREATE CLUSTERED INDEX cluIdx1 ON dbo. tbl_EMPLOYEE (col1);

GO

DROP INDEX cluIdx1 ON dbo. tbl_EMPLOYEE;

      GO

3.    By manually moving all data to a new temporary table. 

CREATE TABLE dbo.tbl_EMPLOYEE_Temp(col1 INT,col2 INT);

GO

INSERT dbo.tbl_EMPLOYEE_Temp

SELECT * FROM dbo.tbl_EMPLOYEE;

GO

Next, drop the old table, rename the temporary table to the original name, and then create the original non-clustered indexes.

DROP TABLE dbo.tbl_EMPLOYEE;

GO

EXEC sp_rename 'tbl_EMPLOYEE_Temp','tbl_EMPLOYEE';

GO

CREATE NONCLUSTERED INDEX idx1 ON dbo.tbl_EMPLOYEE(col1);

GO

CREATE NONCLUSTERED INDEX idx2 ON dbo.tbl_EMPLOYEE(col2);

GO

Hope you like it.

Index Fragmentation

This article is related to Index Fragmentation.

Basic of Index Fragmentation

Index fragmentation is a phenomenon where index contents are no longer stored continuously in the storage. When index contents become scattered in the storage, fragmented, performance on index will degrade.

There are 2 types of Index fragmentation

1.    Extent or External fragmentation.

2.    Page or Internal fragmentation.

The Index fragmentation can occurs in both Clustered and Non-Clustered Index. To understand the External and Page fragmentation I am taking an example.

Un-Fragmented Index

In the above figure all the pages are full and the physical order of the pages is sequential.

As a result of data modifications, in the cases of extent fragmentation the pages get out of the physical order.  This type of fragmentation produces random IO, which does not perform as well as sequential IO.

The bellow figure demonstrates the extent fragmentation.

Extent Fragmentation

In the internal fragmentation there are gaps in the data pages, which reduce the amount of data that can be stored on each page. So it increases the overall amount of space needed to store the data. When the pages are not full and additional pages are required to store the data, as more IO will be required to retrieve those additional pages. The bellow figure demonstrates the process.

Page Fragmentation

During a page split, a new page is allocated, and half the records are moved from the old page to the newly-allocated page. Each page has a pointer to the previous and next page in the index, so those pages will also need to be updated. The bellow figure demonstrates the process.

Page splitting due to fragmentation

Analyzing Fragmentation

To analyse SQL Server indexes, you use the system function sys.dm_db_index_physical_statsto determine which indexes are fragmented and the extent of that fragmentation. We used this function to analysing all the index of the database or all the index of the table or a specified index.

The function sys.dm_db_index_physical_statsto takes the following parameters described below.

Database ID

A smallint value that represents the ID number of a database. If null is specified, the function retrieves index-related data from all databases on a SQL Server instance.

Object ID

An int value that represents the ID number of a table or view. If null is specified, the function retrieves index-related data for all tables and views in a specific database or SQL Server instance. If you specify null, you must also specify null for the index ID and partition number.

Index ID

An int value that represents the ID number of an index. If null is specified, the function retrieves index-related data for all indexes defined on the specified table or view. If you specify null, you must also specify null for the partition number. Also, if the object ID refers to a heap, use 0 as the index ID.

Partition number 

An int value that represents the partition number of an index or heap. If null is specified, the function retrieves index-related information for all partitions related to a specific object.

Mode 

The scan level used to obtain index-related information. Valid inputs include NULL, DEFAULT, or one of the following three modes:

LIMITED

Scans the smallest number of pages, which means this is the fastest mode. The LIMITED mode is equivalent to NULL and DEFAULT.

SAMPLED

Scans 1% of all pages. If an index contains fewer than 10,000 pages, then DETAILED mode is used.

DETAILED

Scans all index pages, which means this is the slowest mode, but most accurate.

The sys.dm_db_index_physical_stats function returns a number of values that provide details about the indexes we specify. SQL Server Books Online provides details about each of these values.

Along with other information, there are two important columns that for detecting fragmentation, which are as follows:

avg_fragmentation_in_percent

This is a percentage value that represents external fragmentation. For a clustered table and leaf level of index pages, this is Logical fragmentation, while for heap, this is Extent fragmentation. The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.

avg_page_space_used_in_percent

This is an average percentage use of pages that represents to internal fragmentation. Higher the value, the better it is. If this value is lower than 75%, some corrective action should be taken.

Here is the quick script to find fragmentation of the database for all the indexes.

SELECT   ps.database_id,

         ps.OBJECT_ID,

         ps.index_id,

         b.name,

         ps.avg_fragmentation_in_percent

FROM     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL,NULL) ASps

         INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

                                        AND ps.index_id = b.index_id

WHERE ps.database_id = DB_ID()

ORDER BY ps.OBJECT_ID

Hope you like it.

Index Defragmentation

In flashback

In my previous article, I am trying to discuss related to Index architecture, how clustered index works and the fragmentation of the index.

If you needed any references, you can find it from

Related to Clustered Index

http://sqlknowledgebank.blogspot.in/2012/07/related-to-clustered-index.html

Index fragmentation

http://sqlknowledgebank.blogspot.in/2012/07/index-fragmentation.html

Introductions

In this article I am trying to discuss related to the Index defragmentation. I have learned several article related to it and collects and gather the facts related to it.

Fragmentation of the Index can be de-fragmented by two ways; it depends on size of the table and the level of the defragmentation.

1. Reorganize Index
2. Rebuild Index

Reorganize Index

It does not take much more system resources. That means it can be done when the user access the table objects where the index exists. This process reorganizes the leaf nodes of the index physically to match it with logical order. If the physical order matches the logical order of the index it increases the performance.  

To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause. This statement replaces the DBCC INDEXDEFRAG statement of the previous version of SQL Server. 

To reorganize a single partition of a partitioned index, use the PARTITION clause of ALTER INDEX.

Reorganizing an index defragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance.

The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.

Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.

The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates.

Reorganize an index when the index is not heavily fragmented. See the previous table for fragmentation guidelines. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index.

Besides reorganizing one or more indexes, large object data types (LOBs) that are contained in the clustered index or underlying table are compacted by default when an index is reorganized. 

The data types image, text, ntext,  varchar(max),  nvarchar(max),  varbinary(max), and xml are large object data types.  Compacting this data can cause better disk space use.

Reorganizing a specified clustered index will compact all LOB columns that are contained in the leaf level (data rows) of the clustered index.

Reorganizing a non-clustered index will compact all LOB columns that are non-key (included) columns in the index.

When ALL is specified, all indexes associated with the specified table or views are reorganized and all LOB columns associated with the clustered index, underlying table, or non-clustered index with included columns are compacted.

The LOB_COMPACTION clause is ignored if LOB columns are not present.

Rebuild Index

Rebuild index means the dropping an existing index of the table and create a new index. The older index is gone with the drop and in the new index the logical order matches the physical order of the index and improves the performance.

In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages and allocating new pages as needed. This can improve disk performance by reducing the number of page reads required to obtain the requested data.

To rebuilds clustered and non-clustered indexes ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement of previous version of the SQL Server and the CREATE INDEX with the DROP_EXISTING clause.

If we rebuilding is a cluster index, then our tables will be unavailable to the user during the rebuild process and if it is a non-clustered index only a shared lock will be placed on the table so users will be able to access the table, but would not be allowed to do modifications on it.

SQL Server 2005 the online option for index rebuilds is only available in Enterprise edition while in SQL Server 2008 it is available in Enterprise, Developer, and Evaluation editions.

When we are creating an index online, it does not drop the existing index rather it keeps the original index to be used by the users and creates an index parallel to it. During this process only the source index will be available for use by the users. Once the index rebuild operation is complete, the original index will be dropped and the new one will be updated and available for read and write operations.

Reorganize Index Example

-- Base Table Defination

CREATE TABLE emp_record

       (empID      INT          IDENTITY(1,1) NOT NULL,

        empNAME    VARCHAR(50)  NOT NULL,

        empDEMP    CHAR(1),

        CONSTRAINT PK_emp_record PRIMARY KEY CLUSTERED(empID)

       )

-- Reorganize Index

ALTER INDEX PK_emp_record ON emp_record REORGANIZE

-- Reorganize All Index

ALTER INDEX ALL ON emp_record REORGANIZE

Rebuild Index Example

-- Rebuild Index

ALTER INDEX PK_emp_record ON emp_record REBUILD

-- Rebuild Index with option specified

ALTER INDEX ALL ON emp_record

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

              STATISTICS_NORECOMPUTE = ON)

-- Rebuild Index online

ALTER INDEX PK_emp_record ON emp_record REBUILD WITH (ONLINE = ON)

ALTER INDEX ALL ON emp_record REBUILD WITH (ONLINE = ON)

Summarize the Differences

1. Index Rebuild drops the existing Index and Recreates the index from scratch.
2. Index Reorganize physically reorganizes the leaf nodes of the index.
3. Rebuild the Index when an index is over 30% fragmented.
4. Reorganize the Index when an index is between 10% and 30% fragmented.
5. If fragmentation is below 10%, no action required.
   Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions.

Defrag Index Stored Procedure

This stored procedure defrags the index accordingly. This is prototype only. You can modify it …

IF OBJECT_ID('proc_DEFRAG') IS NOT NULL

   BEGIN

       DROP PROCEDURE proc_DEFRAG

   END

GO

CREATE PROCEDURE proc_DEFRAG

       (

         @p_DBName   VARCHAR(50) = NULL,

         @p_SchemaNm VARCHAR(50) = NULL,

         @p_TBLName  VARCHAR(50) = NULL,

         @p_IndxName VARCHAR(50) = NULL

       )

AS

  DECLARE @v_FrgReOrgLmt   DECIMAL(10,2),

          @v_FrgReBuildLmt DECIMAL(10,2),

          @v_CurrentFreg   DECIMAL(10,2),

          @v_Flag          DECIMAL(1),

          @v_fillfactor    INT,

          @v_Str           VARCHAR(MAX),

          @v_Message       VARCHAR(MAX)

BEGIN

  SET @v_FrgReOrgLmt   = 10.00

  SET @v_FrgReBuildLmt = 30.00

  SET @v_CurrentFreg   = 0.00

  SET @v_Flag = 0

  SET @v_fillfactor = 80

  IF ISNULL(@p_DBName, '')<>''

         AND ISNULL(@p_TBLName, '')<>''

               AND ISNULL(@p_IndxName, '')<>''

                     AND ISNULL(@p_SchemaNm, '')<>''

     BEGIN

         SELECT @v_CurrentFreg = avg_fragmentation_in_percent

         FROM   sys.dm_db_index_physical_stats(DB_ID(@p_DBName), NULL, NULL, NULL, NULL)a

                INNER JOIN sys.tables b WITH (nolock) ON a.OBJECT_ID =b.OBJECT_ID

                INNER JOIN sys.indexes c WITH (nolock) ON a.OBJECT_ID =c.OBJECT_ID

                                                          AND a.index_id =c.index_id

                WHERE  b.name = @p_TBLName

                       AND c.name = @p_IndxName

          --Reorganizing the index

          IF ((@v_CurrentFreg >= @v_FrgReOrgLmt) 

                   AND (@v_CurrentFreg <= @v_FrgReBuildLmt))

             BEGIN

                SET @v_Str = 'ALTER INDEX ' + @p_IndxName +

                           ' ON [' + RTRIM(LTRIM(@p_SchemaNm)) +

                           '].[' + RTRIM(LTRIM(@p_TBLName)) +

                           '] REORGANIZE' 

                SET @v_Flag = 1

                SET @v_Message = 'Rebuild Index with REORGANIZE'

                GOTO WAYOUT

             END

          --Rebuilding the index

          IF (@v_CurrentFreg>@v_FrgReBuildLmt)

             BEGIN

               SET @v_Str = 'ALTER INDEX ' + @p_IndxName +

                   ' ON [' + RTRIM(LTRIM(@p_SchemaNm)) +

                   '].[' + RTRIM(LTRIM(@p_TBLName)) +

                   '] REBUILD WITH (FILLFACTOR = ' +

                   CONVERT(VARCHAR(3),@v_fillfactor) +

                   ', STATISTICS_NORECOMPUTE = OFF)'

               SET @v_Flag = 1

               SET @v_Message = 'Rebuild Index with REBGUILD'  

             END

WAYOUT:

          IF @v_Flag = 1   

             BEGIN

                   EXEC (@v_Str)

             END

         ELSE

            BEGIN

                   SET @v_Message = 'Parameter is NOT properly Supplied'  

            END

         RAISERROR(@v_Message, 0, 1) WITH NOWAIT

         RAISERROR('', 0, 1) WITH NOWAIT  

     END

END

GO

Hope you like it.

Physical defragmentation NOT Needed After SQL Server defragmentation

May of us doing physical defragmentation to get the performance of SQL server and think that physical defragmentation after SQL server defragmentation. This is a absolutely wrong concept and to illustrate my points I have collected some facts.

Physical disk fragmentation

Physical disk fragmentation is likely what comes to mind when fragmentation is first discussed. Physical fragmentation is a side effect of how hard drives and Windows work. It is common knowledge that regular disk defragmentation is required to achieve optimal performance from your PC. Windows even includes a basic defragmentation utility.

Physical fragmentation slows down your PC because reading data is interrupted by head seek delay. Windows fits files into free space, often breaking the file into segments stored apart from one another. A hard drive's head relocates to read each individual segment. As it moves to each segment the head 'seeks' - often at a cost of 3-4 times the time it takes to read the segment itself. Physical fragmentation primarily affects desktop or laptop PCs containing one hard drive. The single drive must sequentially gather data – so on a fragmented disk it seeks, reads, seeks, reads - these 4 operations are performed one after another. Defragmented, the operation ends up as seek, read, read. We reduce the total cost of 24ms to 15ms in our simple example.

Physical defragmentation products such as Windows defrag, Power Defrag™, Page Defrag™ (another Microsoft tool), or the granddaddy of them all, Diskeeper 2011™ work very well when repairing segmented files.

Diskeeper's technology is licensed to Microsoft as the defragmentation tool internal to Windows. In fact, Diskeeper's latest innovations bring physical defragmentation capabilities to a completely new level. All of these products reorder the data on your disk, consolidating files into fewer segments to minimize "head seeks" – providing faster boot times, quicker file reads, and a more responsive system overall.

SQL fragmentation and Physical fragmentation

However, physical disk fragmentation is not the same as SQL Server defragmentation! SQL Server is different. SQL Servers use advanced storage systems with multiple drives working in tandem, changing the way files are read. Physical fragmentation is something solved with hardware – not with defragmentation scripts or tools.

The fault-tolerance in database storage overcomes the vast majority of physical disk fragmentations' impact. Best practices universally prescribe multi-drive storage subsystems for production SQL Servers. Most SQL Servers use multi-drive storage such as RAID arrays, SANs, and NAS devices; there are always multiple drives acting in tandem. Hard disk controllers supporting drive arrays are aware of the alternate seek/read dynamic and tailor communications with the array for maximum I/O.

As a result, files are distributed across many drives inherently becoming segmented. Working in tandem, however, allows one drive to seek while the others read. With the common configuration of 5 drives, a seek delay of 9ms per drive allows 2 drives reading for 3ms with no seek delay impact at all. Data storage drives are generally much faster than workstation drives, so seek times of 4ms and read times of 1.5ms are not unusual.

There are many DBAs who run a traditional physical defragmentation program in tandem with their intelligent drive controller which results in limited improvement. Physically defragmenting a file in an array implicitly leaves the file segmented across the virtual unison of tandem drives. It's by design. The goal is to gain the most performance while incurring the least overhead – so don't run physical defrags if they slow the storage by 50% while running, and ultimately improve read speeds 1-2%.

The most important concept to understand is that the controller, physical defragmentation programs, and multi-drive arrays are unaware of what SQL Server is doing with the file data internally. By focusing on SQL Server's representation of data - how SQL Server has laid out the database itself, how full each page is, and how effectively we're utilizing available SQL Server resources, we can optimize to

the 'next level' of SQL Server performance, solidly trumping any benefit to physical defragmentation by orders of magnitude. In a nutshell, SQL Server's performance can be most improved by focusing on its internals. In fact, once you start focusing on defragmentation at the SQL Server level – whether with manual defragmentation or with the automated defragmentation provided with SQL defrag manager, you may decide that physical defragmentation is no longer needed!