Testing Database Performance
Have you ever need to bench mark your Database? How it is performing? How it has been indexed? How is the network connectivity? We need to do this and at that time [Necessity is the mother of discovery] we came across nice tool from Sitecore itself!
The tool’s documentation is available here :
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.
Instructions on running the tool and analyzing data everything is available in above given link.
I will just share my basic learning out of this tool:
1. If Average Time exceeds Boundary Average Time, this may imply SQL database performance problems.
2. Keep an eye on Index data. As per my knowledge there should be no index fragementation > 10%. If so Rebuild the indexes.
3. few more things just striking in my mind, which is not related to this tool. But related to DB performance.
- Make sure that all the connected SQL databases are on the same network. For example, going through the public firewall is a no-go for the database performance.
For information about testing connection speeds, see the Testing Database Performance article.
- Use the database cleanup tool to remove artifact data from the databases. Click Desktop, Sitecore, Control Panel, Database, Clean Up Databases to open the database cleanup tool.
- Cleanup the following tables from the artifact data. It is safe to completely truncate them before publishing your website. All of these tables should be automatically cleaned up on a periodic basis:
- History – cleaned up by Sitecore.Tasks.CleanupHistory agent
- PublishQueue – cleaned up by Sitecore.Tasks.CleanupPublishQueue agent
- EventQueue – cleaned up by Sitecore.Tasks.CleanupEventQueue
- Rebuild all the database indexes and implement maintenance plans:
- Set up the database properties:
- Run DBCC CHECKDB to check for database consistency errors
- Tune the Analytics database.
For more information about improving the performance of the Analytics database, see the OMS Performance Tuning Guide.
Happy Database Performance Testing/Tuning!