Interview questions

SQL Server - What is page splits?

Next>>         SQL Server tutorial  

SQL Server - What is page splits? - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

What is page splits? How many page splits is too many? What can I do to help reduce them?

A page is 8Kb of data which can be related to an index, data, lob’s etc. When we INSERT data in a table, they go on a page (into slots). Based on row length, a number of rows can be stored on a 8kb page. If the row length increases (longer data value), SQL Server will move the other rows in the page to accommodate the change. If the page turns out to be small for all these rows, then SQL Server grabs another new page and moves rows to the left/right of the modification onto it. This is termed as a page split.

One can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page. The fill factor ranges from 1 to 100 specifying the percentage of the index page to be left empty. If a table contains read only data, set fill factor to 100. When a table’s modified often, set its fill factor to 70. Following this we can know how many page splits is too many.

SQL Server - What is page splits? - May 05, 2009 at 22:00 PM by Rajmeet Ghai

What is page splits? How many page splits is too many? What can I do to help reduce them?

Page splits are performed when there is no space in a data page to accommodate more inserts or updates. In a page spilt, SQL server removes some data from the current data page and moves it to another data page. This is done in order to make space for the new rows. 

Too many page splits can decrease the performance of the SQL server because of the large number of I/O operations. Number of page splits can be observed by using the Performance Monitor and watch the SQLServer:Access Methods:Page Splits/sec counter. Following remedies can be taken to avoid too many page splits:-

  • Rebuild indexes frequently to empty the fillfactor space for more data.
  • Increased use of primary keys and use of clustered index on them.
  • A faster hardware system can also reduce page splits. 

SQL Server - What is page splits? - May 05, 2009 at 22:00 PM by Rajmeet Ghai

What is page splits? How many page splits is too many? what can I do to help reduce them?

  • A page split occurs when there is no enough space left to perform an INSERT in the available memory/space.
  • In this case the SQL Server also needs to updae the indexes related to the queries depending on the page splits.
  • The SQL Servers performance is affected if there are a lot of page splits. Te factors that affect the number of page splits are:
    1. Number of users
    2. Level of user activity
    3. The frequency of the rebuild of indexes
    4. The primary key being a clustered index
    5. The performance of your I/O subsystem
    6. Read or write operations
    7. The fill-factor used in table indexes
  • Performance Monitor can be used to watch the SQLServer:Access Methods:Page Splits/sec counter.
  • Solution to reduce the number of page splits:
    1. Increase the fillfactor on your indexes.
    2. Rebuild your indexes more often.
    3. Add clustered indexes to your monotonically increasing primary keys.
    4. Get a faster I/O subsystem.

Next>>

Also read

Describe in brief SQL Server monitoring ways.

Answer - SQL Profiler is a tool that stores events from the server. SQL Profiler saves the events in the trace file......

What are the lock types?

Answer - Shared Lock allows simultaneous access of record by multiple Select statements. Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading......

What are the guidelines to use bulk copy utility of SQL Server?

Answer - While importing data, the destination table must already exist......



Write your comment - Share Knowledge and Experience


 

 
Latest placement tests
Latest links
 
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring