Optimizing Sitecore Databases: Part-2 (Using SSMS)

Applies to: Sitecore 6.X, IIS 7 and Higher, MSSQL 2005 and Higher

Solution Tested On:

CMS Version: Sitecore 6.2(rev 10.01.04)

Database Server: MSSQL 2005 SP2, 8 core processor, 8GB RAM

Database Test: Master (all the performance test were evaluated on master database)

Browser: IE8, IE9

Introduction:

This post deals with optimizing sitecore databases by applying a series of maintenance tasks which helps in keeping the Sitecore Database perform to the best of its abilities.

The maintenance list is further broken down into two parts

PART 1: Using Sitecore Content Editor 

PART 2: Using SQL Server Management Studio

  • Shrink databases and Logs
  • Check Database for fragmented indexes and rebuild the indexes
  • Database Consistency Check
  • Set auto close and auto shrink properties to false

In this post we will be only discussing PART 2 which is using SQL Server Management Studio

Click here for part 1 of this article

_______________________________________________________________________________________

Shrink Databases and Logs

Overview:

Shrinking database operations recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.

The shrink database task should be performed after the Sitecore Clean Up task because

A shrink operation is most effective after an operation that creates lots of unused space such as database cleanup operation in sitecore

How to shrink the database:

1.)    Open SQL Management Studio and connect to the SQL server.

2.)    In Object Explorer Expand the Databases, and then right-click the database that you want to shrink.

3.)    Point to Tasks, point to Shrink, and then click Database.

4.)    Click Ok to complete the task

How to shrink the database Logs:

1.)    Open SQL Management Studio and connect to the SQL server.

2.)    In Object Explorer Expand the Databases, and then right-click the database that you want to shrink.

3.)    Point to Tasks, point to Shrink, and then click Files.

4.)    In the pop up, Change File Type to LOG

5.)    Click Ok to complete the task

Tip:

Data that is moved to shrink a database can be scattered to any available location in the database. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the database after shrinking.

_______________________________________________________________________________________

 Check Database for fragmented indexes

Overview:

One of the most important routes to high performing Sitecore Websites (SQL Server Database) is the Index. Indexes speed up the querying process by providing swift access to rows in the data tables.

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.

Heavily fragmented indexes can degrade query performance and cause sitecore application to respond slowly. This tasks deals with remediating index fragmentation by reorganizing or rebuilding an index.

This task can get a little complex especially with users who do not have experience with SQL Server Management Studio (SSMS). So I have decided to beak this task into 2 parts, the first part will accomplish the task without using SSMS and the second part using SSMS

 

PART 1: USING browser to rebuild Indexes

Check for Fragmented Indexes:

Download this file  (SDN access required) and place it under the site root folder and open it in a Web browser, such as {site name}/databasetest.aspx. Now select the database and click Test. If you have fragmented indexes the results panel will show them at the bottom.

How to:

If you have fragmented indexes, they will be displayed in the results panel (see attached image)

Image

Simply click “Rebuild Indexes” to completed the task.

Notes:

1.)    In the index information, it only indexes when more than 7000 rows and more than 5% fragmentation is displayed. (Rebuilding indexes with a low count of rows has no effect)

Tips:

1.)    If you want to fine tune the operation of rebuilding indexes, you can edit the values in the databasetest.aspx file.

   

PART 2: USING SQL Server Management Studio

Check for Fragmented Indexes:

1.) Open SQL Management Studio and connect to the SQL server.

2.) In Object Explorer, right click the database and select properties, Click options from the pop up, Make sure Compatibility Level is set to SQL Server 2008 (100) *  and then click ok

Image

3.) In the object explorer, right click on the database; select Reports à Standard Reports à Index Physical Statistics.

4.) The report on the right will show overview of the utilization of disk spaces within the database

5.) Check for the word “Rebuild” in the Operation Recommended Column (Rebuild Indicates fragmented index in the table)

Image

6.) The above image suggest that the table “dbo.UnversionedFields” has fragmented indexes

How to:

1.)    Open the  Database, Open the Tables

2.)    Open fragmented index table (dbo.UnverisonedFields in the above case)

3.)    Right click “Indexes”; click “Rebuild All” and finally click OK

Tips:

1.)    You can also use Maintenance Plan Wizard Feature in SSMS to schedule the rebuilding operations.

* Compatibility Level effects SQL syntax and query parsing, and should have no impact of performance. Setting the Compatibility Level to a value of SQL Server 2008(100) will take advantage of new T-SQL features

 ______________________________________________________________________________________

 Database Consistency Check

Overview:

 SQL server can make a consistency check of the database it’s working with. The consistency check searches for database file structure errors. It’s always a good idea to run this test to make sure the databases are running smoothly

How to:

1.)    Open SQL Management Studio and connect to the SQL server.

2.)    Highlight the database Object Explorer pane.

3.)    Click the New Query button.

4.)    In the pane which opens, enter DBCC CHECKDB

5.)    Click Execute (or press F5)

6.)    The check may take a little while to run. The results are displayed in the Messages window.

7.)    If the check completed successfully, the messages should end with CHECKDB found 0 allocation errors and 0 consistency errors in database.

8.)    A sample result output is attached below

Image

  

Tip:

If the DBCC reports any error, it would be a good idea to refer this article .

_______________________________________________________________________________________

Set AutoClose Property to False

Overview:

The AutoClose property exposes server behavior for databases when not accessed by a user. When SQL Server opens a database, resources are allocated to maintain that state. Memory is assigned for various operations which take time to complete.

If the Auto Close property is set to FALSE, then when the last connection is closed these resources are deallocated. Although if a new connection is established within small period of time then all of those resources need to be reallocated (this may take quite a bit of time). Setting the Auto Close property to FALSE will prevent this from happening.

How to shrink the database:

1.)    Open SQL Management Studio and connect to the SQL server.

2.)     In Object Explorer, right click the database and select properties, Click options from the pop up, Make sure Auto Close property is set to false and then click ok

_______________________________________________________________________________________

Set AutoShrink Property to False

Overview:

The AutoShrink property exposes Microsoft SQL Server sizing behavior for operating system files that maintain table and index data. Whenever this property is called, SQL Server uses a lot of resources to shrink the databases; moreover we have no control as to when this property is called.

In my opinion we should set this AutoShrink property to false which will deactivate this feature and help the database in performing better.

How to shrink the database:

1.)    Open SQL Management Studio and connect to the SQL server.

2.)     In Object Explorer, right click the database and select properties, Click options from the pop up, Make sure AutoShrink property is set to false and then click ok

 _____________________________________________________________________________________

SUMMARY

In my opinion carrying out this maintenance task quarterly will make sure that the database is performing well. The part1 is written keeping the Sitecore Content Managers in mind who are admin users but mostly do not have access to the database servers. The part 2 aimed at Sitecore database admins or users who have access to the sitecore database server.

Should you have any questions, please feel free to comment on this post.