Database Performance

Database Performance

Database Index Fragmentation

  • Database indexes become fragmented over time
  • Heavily fragmented indexes can decrease performance
  • Reorganize and Rebuild the SQL Server indexes to address this issue
    • Reorganize is an online operation that can be done while users are accessing the table where the index exists. The reorganize process physically reorganizes the leaf nodes of the index to match the logical order in order to improve the performance of index scans
    • Rebuild means dropping an existing index and creating a new one where the logical order matches the physical order. Performance can degrade when the index is being rebuilt because users cannot access the table while the existing index is removed and recreated

Database Page Splitting

  • Occurs when an index or data page becomes full, and then is split between the current page and a newly allocated page
  • Can cause excess I/O on a SQL Server
  • Excess page splitting can cause slow performance

How to manually defragment SQL Server indexes used by Sitecore on SQL Server 2005 - 2012

  • Log on to the SQL Server Managment Studio
  • Right-click on a Sitecore database and select Reports > Standard Reports > Index Physical Statistics
  • Identify the table names in the report where the recommended operation is rebuild or reorganize
  • Expand each table under the Tables folder with such a recommended operation
  • Right click on the index under the Indexes folder that contained the recommended operation
  • Select the Rebuild or Reorganize command as recommended
  • Click OK to confirm and then move on to the next index that needs to be defragmented

How to automate the defragmentation of SQL indexes used by Sitecore on SQL Server 2008 - 2012

  • Install the Management components of SQL Server 2012 if they have not been installed
  • Log on to the SQL Server Managment Studio
  • Expand Management in the Object Explorer and right click on Maintenance Plans
  • Select the Maintenance Plan Wizard and go through the wizard
  • Add either (but not both) Reorganize Indexes or Rebuild Indexes to the Maintenance Plan
  • Once the Maintenance Plan has been created, right click on the new Maintenance Plan and select Execute

SQL Server Performance Counters related to fragmented indexes

  • SQL Server Access Methods object: Page Splits/sec
    • Displays page splits per second
    • Consider increasing the fill factor on indexes with high page splits so there is more space allocated to pages
  • SQL Server Buffer Manager Object: Cache Size (pages)
    • Displays how much physical RAM is being consumed by the SQL Server data cache
    • This number is displayed in page #s so multiply it by 8,192 to view the usage in KB
  • SQLServer: SQL Statistics: Batch Requests/Sec
    • Displays how busy SQL Server is keeping the CPU
    • Measures the number of batch requests that SQL Server receives per second