SQL SERVER ADMINISTRATION: REORGANIZE OR REBUILD CLUSTERED AND NON-CLUSTERED INDEXES

In this blog entry, I would like to describe how to reorganize or rebuild indexes.

First of all, SQL server stores row or index data on 8KB sized memory Pages. For each table (no matter how big), a own page is created. If the table exceeds a certain size due to manipulation of data (e.g. new row inserts), the page will be extended („Page Split“) to a second page etc.

 

The new page will also cause the creation of a new page in the upper level with the index tree, which may also lead to a page split.

Page Spilts are time consuming, cause fragmentation and index pages aren’t phyically ordered as supposed. All this results to a decrease of performance.

Let's have a brief look on the architecture of indexes in SQL Server.

In a table without a clustered index, the data will be stored within the page unsorted („Heap“).

Indexes have a root page, which is the starting point. The tree is split between non-leaf (root and intermediate leafs) and leaf-levels.

In a clustered index, the leaf level is the actual data page. This means the data is stored here in ordered form.

The most significant difference in the index architecture between clustered and non-clustered, is that the leaf level in non-clustered indexes contain key values and not the actual data. 

In case of a heap, there is a row locator in the leaf node pointing to the correct data page.

A non-clustered index in a table that has a clustered index, will refer to the clustered index for pointing the row location.

Disadvantage here is that non-clustered indexes will have to be rebuild if the clustered index is rebuild or dropped. An advantage here is that a Reorganize of a clustered index, will mean that the non-clustered index does not have to be reorganized.

To solve the above listed issues concerning performance, indexes need to be REORGANIZEd or REBUILD.

The level of fragmentation can be analyzed with the help of system function sys.dm_db_index_physical_stats

Here is a more precise query:
SELECT sch.name  + ' ' + tab.name             AS 'Table',
idx.name                                                       AS 'Index',
stat.avg_fragmentation_in_percent,
stat.page_count,
idx.type_desc                                                AS IndexType
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS stat
INNER JOIN sys.tables  AS tab                   ON tab.object_id = stat.object_id
INNER JOIN sys.schemas AS sch               ON tab.schema_id = sch.schema_id
INNER JOIN sys.indexes AS idx                 ON idx.object_id = stat.object_id
AND stat.index_id = idx.index_id
ORDER BY stat.avg_fragmentation_in_percent DESC
If the average fragmention is between 5 and 30 %, a REORGANIZE is fine, if it's above 30% REBUILD is advised.

Following T-SQL syntax is required for REORGANIZE:

ALTER INDEX indexname

ON tablename REORGANIZE

In case of a rebuild, there is an option for a fill factor. This means the page will be filled with the percentage value defined. The rest is left open to avoid a fast fragmentation.

ALTER INDEX indexname

REBUILD WITH (FILLFACTOR = 80)

The fillfactor by default will only relate to the leaf level of the tree. If you want to also cover the other pages PAD_INDEX = ON will do the job.

ALTER INDEX indexname 
REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = ON)

T-SQL: Create Dynamic SQL Statement using XMLPATH() and sp_executesql

In this post I would like to demonstrate a way how to exlude columns from a Select * query and executing the new query dynamically with the built-in procedure sp_executesql.
At work I had a request, where I had to exclude a certain column, which is usually no problem. The issue was, that I had a table that was being altered constantly and new columns where added to it from time to time. So simply writing the desired columns would have led to the result that new columns wouldn't be selected at runtime.
With the help of XMLPATH() and sp_executesql I was able to solve this issue. Let's look at the following table:

The table is called rec.DimCountries. Let's assume we will be adding a new column to it next week and another one a week later and so on. The end user would like to see every column except for CountryID.

rec.DimCountries
CountryID
Country
Name

With the help of sys.columns I am able to get all columns listed, but first we need to find out the object_id of the table in sys.objects

SELECT object_id
FROM sys.objects
WHERE name = 'DimCountries'

 object_id
-----------
517576882

Now we can lookup the column names from sys.columns with that object_id

select name
from sys.columns
where object_id = 517576882

 name
-----------
CountryID
Country
Name

So this gives us the chance to exclude 'CountryID' and lookup the rest dynamically.
I will add a comma for listing purposes here:

SELECT ', ' + Name
FROM sys.columns
where object_id = (
SELECT object_id
FROM sys.objects
WHERE name = 'DimCountries')
AND name <> 'CountryID'

-----------
, Country
, Name

I need to get this result above in one line and remove the first comma. For the removal I will use STUFF and for the one line listing I will use XMLPATH().
Here is the complete script. I will break down the main elements below:


DECLARE @cols nvarchar(200),
@sqlStatement nvarchar(300)

SELECT @cols = STUFF((
            SELECT ', ' + Name
            FROM sys.columns
            where object_id = (
                SELECT object_id
                FROM sys.objects
                WHERE name = 'DimCountries')
                AND name <> 'CountryID'
                FOR XML PATH('')
                ), 1, 1, '' )

SET @sqlStatement = N'SELECT ' + @cols + N' FROM rec.DimCountries'
EXEC sp_executesql @sqlStatement


FOR XML PATH('') will concat the results of the column name into 1 line

STUFF(', Country, Name', 1, 1, '') will remove the first comma. The first part has the value that needs to be changed, second part determines where to start, the third part is the length and fourth part is what to value to use for replacement:

STUFF ( character_expression , start , length , replaceWith_expression )


The output of @cols = Country, Name
So I will create a string for the SELECT part and assign it to variable @sqlStatement

Now we can hand over the variable @sqlStatement to sp_executesql
EXEC sp_executesql @sqlStatement 

Result delivers dynamically every column except CountryID
Country  Name
---------------------------
JPN      Japan
EU       European Union
A         Austria
ITA      Italy