SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

Q. What is Fragmentation? How to detect fragmentation and how to eliminate it?

A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types:

• Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance.
• External Fragmentation: When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another causes higher disk rotations, and this is called Extent Fragmentation.

Index pages also maintain a logical order of pages inside the extent. Every index page is linked with previous and next page in the logical order of column data. However, because of Page Split, the pages turn into out-of-order pages. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-pagepointer in the current leaf page. This is called Logical Fragmentation.

Ideal non-fragmented pages are given below:

Statistics for table scan are as follows:

• Page read requests: 2
• Extent switches: 0
• Disk space used by table: 16 KB
• avg_fragmentation_in_percent: 0
• avg_page_space_used_in_percent: 100

Following are fragmented pages:

In this case, the statistics for table scan are as follows:

• Page read requests: 6
• Extent switches: 5
• Disk space used by table: 48 KB
• avg_fragmentation_in_percent > 80
• avg_page_space_used_in_percent: 33

How to detect Fragmentation: We can get both types of fragmentation using the DMV: sys.dm_db_index_physical_stats. For the screenshot given below, the query is as follows:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESC

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.

Reducing fragmentation:

• Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
• Reducing Fragmentation in an Index: There are three choices for reducing fragmentation, and we can choose one according to the percentage of fragmentation:
  • If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEXREORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
  • If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.
  • (Update: Please note this option is strongly NOT recommended)Drop and re-create the clustered index: Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX.

SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REORGANIZE
GO
Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

CONTAINS is a predicate used in the WHERE clause of a Transact-SQL

CONTAINS (Transact-SQL)

Other Versions

Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.

CONTAINS can search for:

• A word or phrase.
• The prefix of a word or phrase.
• A word near another word.
• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

For information about the forms of full-text searches that are supported by SQL Server, see Query with Full-Text Search.

Applies to: SQL Server (SQL Server 2008 through current version).

 Transact-SQL Syntax Conventions

Syntax

CONTAINS ( 
     { 
        column_name | ( column_list ) 
      | * 
      | PROPERTY ( { column_name }, 'property_name' )  
     } 
     , '<contains_search_condition>'
     [ , LANGUAGE language_term ]
   ) 

<contains_search_condition> ::= 
  { 
      <simple_term> 
    | <prefix_term> 
    | <generation_term> 
    | <generic_proximity_term> 
    | <custom_proximity_term> 
    | <weighted_term> 
    } 
  | 
    { ( <contains_search_condition> ) 
        [ { <AND> | <AND NOT> | <OR> } ] 
        <contains_search_condition> [ ...n ] 
  } 
<simple_term> ::= 
     { word | "phrase" }

<prefix term> ::= 
  { "word*" | "phrase*" }

<generation_term> ::= 
  FORMSOF ( { INFLECTIONAL | THESAURUS } , <simple_term> [ ,...n ] ) 

<generic_proximity_term> ::= 
  { <simple_term> | <prefix_term> } { { { NEAR | ~ } 
     { <simple_term> | <prefix_term> } } [ ...n ] }

<custom_proximity_term> ::= 
  NEAR ( 
     {
        { <simple_term> | <prefix_term> } [ ,…n ]
     |
        ( { <simple_term> | <prefix_term> } [ ,…n ] ) 
      [, <maximum_distance> [, <match_order> ] ]
     }
       ) 

      <maximum_distance> ::= { integer | MAX }
      <match_order> ::= { TRUE | FALSE } 

<weighted_term> ::= 
  ISABOUT 
   ( { 
        { 
          <simple_term> 
        | <prefix_term> 
        | <generation_term> 
        | <proximity_term> 
        } 
      [ WEIGHT ( weight_value ) ] 
      } [ ,...n ] 
   ) 

<AND> ::= 
  { AND | & }

<AND NOT> ::= 
  { AND NOT | &! }

<OR> ::= 
  { OR | | }

Examples






A. Using CONTAINS with <simple_term>


The following example finds all products with a price of $80.99 that contain the word "Mountain".










USE AdventureWorks2012;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
   AND CONTAINS(Name, 'Mountain');
GO







B. Using CONTAINS and phrase with <simple_term>


The following example returns all products that contain either the phrase "Mountain" or "Road".










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' Mountain OR Road ')
GO







C. Using CONTAINS with <prefix_term>


The following example returns all product names with at least one word starting with the prefix chain in the Name column.










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO







D. Using CONTAINS and OR with <prefix_term>


The following example returns all category descriptions containing strings with prefixes of either "chain" or "full".










USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO







E. Using CONTAINS with <proximity_term>






Applies to: SQL Server 2012 through SQL Server 2014.




The following example searches the Production.ProductReview table for all comments that contain the word "bike" within 10 terms of the word "control" and in the specified order (that is, where "bike" precedes "control").










USE AdventureWorks2012;
GO
SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments , 'NEAR((bike,control), 10, TRUE)');
GO







F. Using CONTAINS with <generation_term>


The following example searches for all products with words of the form ride: "riding," "ridden," and so on.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');
GO







G. Using CONTAINS with <weighted_term>


The following example searches for all product names containing the words performance, comfortable, or smooth, and different weights are given to each word.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8), 
comfortable weight (.4), smooth weight (.2) )' );
GO







H. Using CONTAINS with variables


The following example uses a variable instead of a specific search term.










USE AdventureWorks2012;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);
GO







I. Using CONTAINS with a logical operator (AND)


The following example uses the ProductDescription table of the AdventureWorks2012 database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word Aluminum and the word spindle. The search condition uses the AND Boolean operator.










USE AdventureWorks2012;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, 'Aluminum AND spindle');
GO







J. Using CONTAINS to verify a row insertion


The following example uses CONTAINS within a SELECT subquery. Using the AdventureWorks2012 database, the query obtains the comment value of all the comments in the ProductReview table for a particular cycle. The search condition uses the AND Boolean operator.










USE AdventureWorks2012;
GO
INSERT INTO Production.ProductReview 
(ProductID, ReviewerName, EmailAddress, Rating, Comments) 
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5, 
'The Mountain-200 Silver from AdventureWorks2008 Cycles meets and exceeds expectations. I enjoyed the smooth ride down the roads of Redmond');
 
-- Given the full-text catalog for these tables is Adv_ft_ctlg, 
-- with change_tracking on so that the full-text indexes are updated automatically.
WAITFOR DELAY '00:00:30';   
-- Wait 30 seconds to make sure that the full-text index gets updated.
 
SELECT r.Comments, p.Name
FROM Production.ProductReview r
JOIN Production.Product p 
ON
 r.ProductID = p.ProductID
 
AND r.ProductID = (SELECT ProductID
                  FROM Production.ProductReview
                  WHERE CONTAINS (Comments, 
                                 ' AdventureWorks2008 AND 
                                   Redmond AND 
                                   "Mountain-200 Silver" '));

GO







K. Querying on a document property






Applies to: SQL Server 2012 through SQL Server 2014.




The following query searches on an indexed property, Title, in the Document column of the Production.Document table. The query returns only documents whose Title property contains the string Maintenance or Repair.


 Note

For a property-search to return rows, the filter or filters that parse the column during indexing must extract the specified property. Also, the full-text index of the specified table must have been configured to include the property. For more information, see Search Document Properties with Search Property Lists.













Use AdventureWorks2012;
GO
SELECT Document FROM Production.Document
  WHERE CONTAINS(PROPERTY(Document,'Title'), 'Maintenance OR Repair');
GO

SQL SERVER – Working with SPARSE Columns in SQL Server

CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),FirstCol INT,SecondCol VARCHAR(100),ThirdCol SmallDateTime)GOCREATE TABLE Sparsed(ID INT IDENTITY(1,1),FirstCol INT SPARSE,SecondCol VARCHAR(100) SPARSE,ThirdCol SmallDateTime SPARSE)GODECLARE @idx INT = 0WHILE @idx < 50000BEGIN
INSERT INTO UnSparsed VALUES (NULL,NULL, NULL)INSERT INTO Sparsed VALUES (NULL, NULL, NULL)SET @idx+=1ENDGOsp_spaceused 'UnSparsed'GOsp_spaceused 'Sparsed'GODROP TABLE UnSparsed
GODROP TABLE Sparsed
GO

Previously I wrote about SQL SERVER – 2008 – Introduction to SPARSE Columns. Let us understand the concept of SPARSE column in more detail. I suggest you read the first part before continuing reading this article.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

• INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
• SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
• SPARSE column can not have default value or rule or computed column.
• Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
• Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
• A table operation which involves SPARSE column takes performance hit over regular column.

Let me know your thoughts about SPARSE column feature of SQL Server 2008, I am very eager to see your point of view on this new feature.

I make a visit to the local user group whenever I get and keeping the community juices up and running for me. I get an opportunity to share some of the learnings and meet tons of new people. In our recent meetup at the SQLBangalore UG, I met a lot of young talent joining the IT field. Many take time to walk up to me and share a moment now and then. It is in this continued journey that inspired me to write this blog.

When I was at the UG meet, I said I write on a lot of topics and showed up this blog. It is rarely that someone fails to stumble onto this space. But one kid walked up to me and asked, what is SPARSE columns? After I explained what it was, he immediately asked me – is there any catch to it? Are there restrictions and limitations? Though there were few that I mentioned, I thought I will write few as part of this blog.

NULL Effect

Sparse columns MUST be nullable. Trying to create a sparse column as not NULL will fail with an error.

CREATE TABLE noNullSparse (Name VARCHAR(20) SPARSE NOT NULL)

Msg 1731, Level 16, State 1, Line 1

Cannot create the sparse column ‘Name’ in the table ‘noNullSparse’ because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.

Though the error is not explicit, you can see that the columns marked as SPARSE cannot be NOT NULL fields.

Datatypes to Watch

The above error gives away a lot of information on what are the datatypes that will cause you possible errors if marked as SPARSE.

• geography
• geometry
• image
• ntext
• text
• timestamp
• user-defineddatatypes(UDT)

The other attributes like IDENTITY, FILESTREAMS and ROWGUIDs are also not allowed.

SPARSE with Computed Column

Lesser known is that SPARSE column’s cannot be used with Computed Columns. But, we can use a SPARSE a column inside a Computed Column. Below is a classic example:

-- Trying to mark a computed column as SPARSE will fail with incorrect syntax errorCREATE TABLE Sales1 (MRP INT, Loss TINYINT, Profit AS (MRP - Loss)SPARSE)-- However, including an existing SPARSE column in a computed column is allowedCREATE TABLE Sales2 (MRP INT, Loss TINYINT SPARSE, Profit AS (MRP -Loss))-- CleanupDROP TABLE Sales2

Final Note

The other times we are likely to get an error is to mark a SPARSE column with default values, Primary key, clustered Index, partition key, user defined table type etc. These are some of the restrictions when working with SPARSE columns and will raise an error if used.

Would be great to know if anyone uses SPARSE columns in your designs anywhere? What are the scenario’s you found it useful? Let me know via your comments as it would be a great learning for all.