Awarded as Microsoft Community Contributor 2011

I am really honored to receive this Award from Microsoft and would take this opportunity to thank my colleague Mr. Ankit Shah & my close friend Mr. Hemant Goswami who always kept motivating me for contributing to the Community. 

I am very excited on the receipt of this award and I promise to continue serving the community.

Rebuild all the Indexes of a SQL Database in one go

Introduction

In my last post, I had explained what could be the best value of Fill Factor for the indexes in SQL Server and had promised to show a handy way to ReBuild all the indexes including the ones created on the Indexed Views.

Implementation

USE DBName

GO

DECLARE @tsql NVARCHAR(MAX)  

DECLARE @fillfactor INT

SET @fillfactor = 70 

SELECT @tsql = 

  STUFF(( SELECT DISTINCT 

           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

          FROM 

           sysobjects o 

          INNER JOIN sysindexes i 

           ON o.id = i.id 

          WHERE 

           o.xtype IN ('U','V')

           AND i.name IS NOT NULL

          FOR XML PATH('')), 1,1,'')

--PRINT @tsql          

EXEC sp_executesql @tsql  

Conclusion

This way, we can easily rebuild all the existing indexes on the Tables as well as the Indexed Views of the selected Database with an option to set the fillfactor as well. Hope, this script will prove to be handy.

What is the best value for Fill Factor in SQL Server?

Introduction

When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0, which means that the leaf-level pages are filled to capacity. Fill-factor values 0 and 100 are the same in all respects. The fill-factor setting applies only when the index is created, or rebuilt.

How does it help?

The fill-factor option is provided for fine-tuning index data storage and performance. In an insert-intensive environment, the index pages will eventually split to accommodate additional entries. To avoid or reduce the frequency of page splits, the index should be rebuilt using an appropriate fill factor.

What is the ideal value?

It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, we might follow these guidelines:

•          Low Update Tables (100-1 read to write ratio): 100% fill factor
•         High Update Tables (where writes exceed reads): 50%-70% fill factor
•          Everything In-Between: 80%-90% fill factor.

Hope, this small article will help you in identifying the fill-factor for your application/system.

Next, I would come up with a handy script to update the fill-factor of all the existing indexes at one go.

Maximum recursion possible with CTE in SQL Server 2005/2008

In my last blog about Tally Tables, we talked about the use of recursive CTEs to generate Tally Tables. Following that, someone quickly asked me to generate a Tally Table for integers starting from 1 to 150 as when he tried the way explained in my last post, the following exception was generated –

Msg 530, Level 16, State 1, Line 3

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Now what to do?

Actually, to prevent infinite recursion, a default value for MAXRECURSION = 100 has been already set. Hence, any recursion will stop on reaching the threshold limit. If we want to loop/iterate more than the default value, we need to set the MAXRECURSION value as explained in my another post - Prevent recursive CTE from entering an infinite loop

So, the following statement will work to generate a Tally Table from 1 to 150–

DECLARE @Max AS INT = 150

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT * FROM CTE OPTION (MAXRECURSION 150);

After looking at this option, most of us will ask – what is the max value that could be used with the MAXRECURSION option? And the answer is – 32767. If we try to set a value greater than this,

SELECT * FROM CTE OPTION (MAXRECURSION 32768);

sql fires the following exception –

Msg 310, Level 15, State 1, Line 10

The value 32768 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

Hope, this post will make you understand few more facts related to the use of CTE with recursion.

Happy iterating…