![]() ![]() |
Testing Database Performance
The recommendation below is primarily addressed to Sql Server environments.
A fast database connection is an important prerequisite for a stable Sitecore environment. Although data is cached in memory, you will eventually end up with an empty cache, resulting in a read from the database on each Item request for the first requests to your web server. Imagine your website’s front page navigates through 8000 Items to render itself, and each read from the database takes 5ms. On that initial request to the front page, 40 seconds would be the smallest timeframe this request would take to execute. Meanwhile new incoming requests could pile up and overload the server.
So what is a fast connection, and how do we measure it?
You can download the test aspx code which allows to obtain the basic benchmark.
Downloads for Sitecore CMS 6.x-7.2
- Download test aspx code for Sitecore CMS 6.x-7.2, put it under the site root folder and open it in a Web browser, such as {site name}/databasetest.html.
The page content:
Server Information table – provides general information about the SQL Server instance.
Testing result table – contains the following columns:
Action – shows the performed testing action and how many times it had been performed (the default cycles count is 10000, but it can be changed in the source code manually in the “cycles” variable).
Min Time – the minimal period of time taken to perform one test action.
Max Time – the maximal period of time taken to perform one test action.
Average Time – the average period of time taken to perform one test action.
Boundary Average Time – the approximate limit of average time that must be taken to perform one test action. If Average Time exceeds Boundary Average Time, this may imply SQL database performance problems.
Indexes Information table – provides information about database indexes (see note 2 for additional information).
SQL Server Required Permissions:
ALTER permission
CONTROL permission
VIEW DATABASE STATE permission
EXECUTE permission
Notes:
- The SQL server 2005 sp2 or later is required.
- 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)
- "Server Information" is taken from that server where corresponding database is hosted.
![](/SdnArchive/images/sdn5/prev.gif)
![](/SdnArchive/images/sdn5/next.gif)