Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Any database design can include only the Normal forms

  • Any database design can include only the Normal forms. But while a DBA performs a product deployment it is critical to take care of certain factors for Index creation. On that lines, explain FILLFACTOR and PATFACTOR inside SQL Server? What are its uses and when would you use the same?

    Posted on 01-24-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

7  Answers  

Subscribe to Notifications
  • Score
    10

    Fill Factor

    The fill-factor option is provided for fine-tuning index data storage and performance.

    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.

    You can use the CREATE INDEX or ALTER INDEX statements to set the fill-factor value for individual indexes. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill-factor value of one or more indexes, use sys.indexes.

    The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.

    The following example shows how to use spconfigureto set the fill factor server configuration option to 100. Using spconfigure, you can change fill factor only when show advanced options is set to 1. The setting takes effect after restarting the server.

    Use AdventureWorks2008R2;
    GO
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'fill factor', 100;
    GO
    RECONFIGURE;
    GO
    


    CHOOSING FILLFACTORS FOR INDEXES

    By default, SQL Server creates indexes that are completely full at the leaf level and leaves room for two rows on the intermediate pages for growth. The fillfactor option for the create index command allows you to specify how full to create index pages and the data pages of clustered indexes.

    If you are creating indexes for tables that will grow in size, you can reduce the impact of page splitting on your tables and Indexes by using the fillfactor option for create index. Note that the fillfactor is used only when you create the index; it is not maintained over time. The purpose of fillfactor is to provide a performance boost for tables that will experience growth; maintaining that fillfactor by continuing to split partially full pages would defeat the purpose.

    When you use fillfactor, except for a fillfactor value of 100 percent, data and index rows are spread out across the disk space for the database farther than they are by default.


    DISADVANTAGES OF USING FILLFACTOR

    • If you use fillfactor, especially a very low fillfactor, you may notice these effects on queries and maintenance activities:

    • More pages must be read for each query that does a table scan or leaf-level scan on a nonclustered index. In some cases, it may also add a level to an index's B-tree structure, since there will be more pages at the data level and possibly more pages at each index level.

    • The number of pages that must be checked by your dbcc commands increases, so these commands will take more time.

    • The number of pages dumped with dump database increases. dump database copies all pages that store data, but does not dump pages that are not yet in use. Your dumps and loads will take longer to complete and possibly use more tapes.

    • Fillfactors fade away over time. If you use fillfactor only to help reduce the performance impact of lock contention on index rows, you may wish to use maxrowsper_page instead. If you use fillfactor to reduce the performance impact of page splits, you need to monitor your system and re-create indexes when page splitting begins to hurt performance.


    ADVANTAGES OF USING FILLFACTOR

    • Setting fillfactor to a low value provides a temporary performance enhancement. Its benefits fade away as inserts to the database increase the amount of space used on data pages.

    • The benefits are that a lower fillfactor:

      Reduces page splits.
      
      
      Can reduce lock contention, since it reduces the likelihood that two processes will need the same data or index page
      

      simultaneously.

      Can help maintain large I/O efficiency for the data pages and for the leaf levels of nonclustered indexes, since page splits occur
      

      less frequently. This means that a set of eight pages on an extent are likely to be read sequentially.

    Choosing Fill Factor :: http://www.lcard.ru/~nail/sybase/perf/9076.htm

    Details about Fill Factor:: http://msdn.microsoft.com/en-us/library/aa258260(v=sql.80).aspx


    PAD INDEX

    Specify the space to leave open on each page in the intermediate levels of the index. Because Pad index is only useful when Set fill factor is specified, it is disabled unless Set fill factor is selected.

    Syntax PAD_INDEX = { ON | OFF }

    Specifies index padding. The default is OFF.

    ON - The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index.

    OFF or fillfactor is not specified - The intermediate-level pages are filled to near capacity, leaving enough space for at least one row of the maximum size the index can have, given the set of keys on the intermediate pages.

    Specifies the space to leave open on each page (node) in the intermediate levels of the index. The PADINDEX option is useful only when FILLFACTOR is specified, because PADINDEX uses the percentage specified by FILLFACTOR. By default, SQL Server ensures that each index page has enough empty space to accommodate at least one row of the maximum size the index can have, given the set of keys on the intermediate pages. If the percentage specified for FILLFACTOR is not large enough to accommodate one row, SQL Server internally overrides the percentage to allow the minimum.

    Pad index details http://www.lcard.ru/~nail/sybase/perf/9076.htm

    I Dont know anything about Patfactor just including Pad index instead of patfactor.

    Thanks

    Yogesh

    Replied on Jan 24 2012 2:55AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    3

    A fill factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits when the data has to be accommodated in the future.

    As far as I understand there is no PATFACTOR in sql server,I guess you might be asking about Pad Index -

    A pad index specifies index padding. When it is set to ON, then the free space percentage per the fill factor specification is applied to the intermediate-level pages of the index. When it is set to OFF, the fill factor is not specified and enough space is left for a maximum size row that an index can have.

    Replied on Jan 24 2012 8:50PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    10

    "Because the data is stored in the order of the index, to insert a new row SQL Server must find the page with the two rows between which the new row shall be placed. Then, if there is not room to fit the row on that page, a split occurs and some of the rows get moved from this page to a newly created one."

    one way to counter this (at least to a degree) is to specify a value for the fill factor and possibly pad index for the clustered index. Fill factor specifies the percentage that SQL Server should fill every data page to when creating an index. Specifying a fill factor value of 50 therefore means that each data page will only be half full, in effect meaning that the storage space needed for the table is doubled (since there are twice as many data pages). The good thing is that page splits are not as necessary as with a higher fill factor, since there will usually be room on a page to insert a new row without splitting the page into two and moving rows between them. Fill Factor is used for index pages in the leaf levels.If fill factor is specified you can also specify the option to use pad index (it uses the same value as fill factor), which works in the same way as fill factor only it is used for index pages in the intermediate levels (between the root level and leaf level).

    Note that both options are only used when creating or rebuilding an index. They are not maintained after initial 'filling' by SQL Server, as that would defeat the original purpose to use them, i.e. avoiding page splits (as SQL Server would have to use page splits to maintain them). Also note that these options apply for non-clustered indexes as well, the index pages of non-clustered indexes use page splits in the same way as clustered indexes of course.

    http://www.sqlservercentral.com/columnists/chedgate/clusterthatindexparttwo.asp

    FILLFACTOR

    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. FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild.

    Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

    Specifying a fill-factor value of 70 would implies that 30 percent of each 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 setting applies only when the index is created or rebuilt.

    The SQL Server Database Engine does not keep the specified percentage of empty space in the pages AFTER the index is created.

    Points to remember while using the FILLFACTOR argument:

    1. If fill-factor is set to 100 or 0(Both are same), the Database Engine fills pages to their capacity while creating indexes.

    2. The server-wide default FILLFACTOR is set to 0.

    3. To modify the server-wide default value, use the sp_configure system stored procedure.

    4. To view the fill-factor value of one or more indexes, use sys.indexes.

    5. To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements.

    6. Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index.

    SQL Script to set Server level FILLFACTOR to 90

    EXEC sys.sp_configure 'show advanced options', '1'
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure 'fill factor (%)', '90'
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure 'show advanced options', '0'
    RECONFIGURE WITH OVERRIDE
    GO
    

    Examples to rebuild Index in SQL SERVER 2005:

    To Rebuild all the Indexes on Table with Specifying options:

    USE AdventureWorks
    GO
    ALTER INDEX ALL ON Production.Product
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON)
    GO
    

    PAD_INDEX = { ON | OFF } Specifies index padding. The default is OFF.

    ON
    The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index. If FILLFACTOR is not specified at the same time PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.

    OFF or fillfactor is not specified
    The intermediate-level pages are filled to near capacity. This leaves sufficient space for at least one row of the maximum size that the index can have, based on the set of keys on the intermediate pages.

    Specify the space to leave open on each page in the intermediate levels of the index. Because Pad index is only useful when Set fill factor is specified, it is disabled unless Set fill factor is selected, because PAD_INDEX uses the percentage specified by FILLFACTOR.

    FILL FACTOR represents the percentage to fill the data page files on index creation or rebuild at the leaf layer of the index.If fill factor is specified you can also specify the option to use pad index (it uses the same value as fill factor), which works in the same way as fill factor only it is used for index pages in the intermediate levels (between the root level and leaf level).

    http://blog.sqlauthority.com/2009/12/16/sql-server-fillfactor-index-and-in-depth-look-at-effect-on-performance/ http://blog.sqlauthority.com/2007/10/20/sql-server-set-server-level-fillfactor-using-t-sql-script/

    Advantages of using fillfactor

    • Setting fillfactor to a low value provides a temporary performance enhancement. Its benefits fade as inserts to the database increase the amount of space used on data or index pages.
    • A lower fillfactor provides these benefits:
    • It reduces page splits on the leaf-level of indexes, and the data pages of allpages-locked tables.
    • It improves data-row clustering on data-only-locked tables with clustered indexes that experience inserts.
    • It can reduce lock contention for tables that use page-level locking, since it reduces the likelihood that two processes will need the same data or index page simultaneously.
    • It can help maintain large I/O efficiency for the data pages and for the leaf levels of nonclustered indexes, since page splits occur less frequently. This means that eight pages on an extent are likely to be sequential.

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc200201251/html/databases/databases299.htm

    Disadvantages of using fillfactor

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc200201251/html/databases/databases299.htm

    Replied on Jan 25 2012 3:00AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    9

    Fill Factor

    The fill-factor option is provided for fine-tuning index data storage and performance. 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.

    Performance Considerations

    • Page Splits

    A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. When frequent page splits occur, the index can be rebuilt by using a new or existing fill-factor value to redistribute the data. For more information, see Reorganizing and Rebuilding Indexes.

    Although a low, nonzero fill-factor value may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.

    • Adding Data to the End of the Table

    A nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows.

    PAT Factor

    There is no such thing as Pat Factor in SQL Server as per my knowledge. PATINDEX is a string function tha returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

    Replied on Jan 27 2012 4:40AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    9

    FILLFACTOR

    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. FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild.

    Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage.

    Specifying a fill-factor value of 70 would implies that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table.The good thing is that page splits are not as necessary as with a higher fill factor, since there will usually be room on a page to insert a new row without splitting the page into two and moving rows between them

    What are its uses

    • If there are tables present that are primarily read only, a fillfactor setting of 100 should be used to reduce the number of produced index pages.

    • If, however, there are tables present with high rates of INSERT, UPDATE and DELETE activity, lower fillfactor settings of 50-60% should be used. This will need to be coupled with periodic index rebuilds that will re-establish the fillfactor setting to keep DML running smooth through the indexes.

    • A mixed environment can work well with fillfactor settings in the neighborhood of 75%.

    SQL Script to set Server level FILLFACTOR to 90

    EXEC sys.sp_configure 'show advanced options', '1'
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure 'fill factor (%)', '90'
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure 'show advanced options', '0'
    RECONFIGURE WITH OVERRIDE
    GO
    

    Examples to rebuild Index in SQL SERVER 2005:

    To Rebuild all the Indexes on Table with Specifying options:

    USE AdventureWorks
    GO
    ALTER INDEX ALL ON Production.Product
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON)
    GO
    

    PAD INDEX

    If fill factor is specified you can also specify the option to use pad index (it uses the same value as fill factor), which works in the same way as fill factor only it is used for index pages in the intermediate levels (between the root level and leaf level).

    When would you use the same?

    • The original purpose to use them to avoiding page splits.
    • Both options are only used when creating or rebuilding an index.
    • Apply for Cluster Inxex and non-clustered indexes as well
    Replied on Feb 4 2012 7:08AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    0

    What does "Normal Forms" have to do with selection of FILLFACTOR and PADINDEX settings?

    What is the value of asking a question, then having several people copy-and-paste the same content, usually directly out of Books Online?

    Replied on Feb 11 2012 3:01PM  . 
    Marc Jellinek
    95 · 2% · 586
  • Score
    7

    FILLFACTOR 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. FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild.

    Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage.

    Specifying a fill-factor value of 70 would implies that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table.The good thing is that page splits are not as necessary as with a higher fill factor, since there will usually be room on a page to insert a new row without splitting the page into two and moving rows between them

    What are its uses

    If there are tables present that are primarily read only, a fillfactor setting of 100 should be used to reduce the number of produced index pages.

    If, however, there are tables present with high rates of INSERT, UPDATE and DELETE activity, lower fillfactor settings of 50-60% should be used. This will need to be coupled with periodic index rebuilds that will re-establish the fillfactor setting to keep DML running smooth through the indexes.

    A mixed environment can work well with fillfactor settings in the neighborhood of 75%.

    SQL Script to set Server level FILLFACTOR to 90

    EXEC sys.sp_configure 'show advanced options', '1' 
    RECONFIGURE WITH OVERRIDE 
    GO 
    EXEC sys.sp_configure 'fill factor (%)', '90' 
    GO 
    RECONFIGURE WITH OVERRIDE 
    GO 
    EXEC sys.sp_configure 'show advanced options', '0' 
    RECONFIGURE WITH OVERRIDE 
    GO
    

    Examples to rebuild Index in SQL SERVER 2005:

    To Rebuild all the Indexes on Table with Specifying options:

    USE AdventureWorks 
    GO 
    ALTER INDEX ALL ON Production.Product 
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, 
    STATISTICS_NORECOMPUTE = ON) 
    GO
    

    PAD INDEX If fill factor is specified you can also specify the option to use pad index (it uses the same value as fill factor), which works in the same way as fill factor only it is used for index pages in the intermediate levels (between the root level and leaf level).

    When would you use the same?

    The original purpose to use them to avoiding page splits. Both options are only used when creating or rebuilding an index. Apply for Cluster Inxex and non-clustered indexes as well

    Replied on Feb 22 2012 10:54PM  . 
    Latheesh NK
    51 · 4% · 1178

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.