Database – Sitecore basics!

Last week I was reading article on SQL Profiler from Sitecore Scrapbook and its really great document for all those who are new/already know how to use Sitecore Profiler. The one problem it has is that it has been uploaded as .doc and few people avoid to download and then read the document. So, I thought to create a web version of the same document. So, those few people can take benefit of document.

1. Start SQL Profiler from SQL Server Management Studio > Tools

Choose File > New Trace

Check Show All Columns

2. Choose Column Filters and set e.g. DatabaseId to a specific database

2.1 No idea, what is your Database ID? No worries, you can check using following way (Find the database id from Databases > System Databases > Master > Views > sys.databases)

3. Or filter e.g. by all requests taking more than 100ms.

4. Save the trace to a database table by checking the “Save to Table “ checkbox below. Then Choose Run to start profiling.

If you liked this article then credit should go to, its original Jesper Jørgensen – article writer.

Source : http://sdn.sitecore.net/Scrapbook/SQL%20Profiler.aspx

Happy SQL Profiling! 🙂

Challenge:

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!

Solution:

The tool’s documentation is available here :

http://sdn.sitecore.net/Articles/Administration/Sitecore%20Performance/Optimizing%20Sitecore%206%20and%20later/Database%20Administration.aspx

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:
http://www.theclientview.net/?p=40

Set up the database properties:
http://www.theclientview.net/?p=162

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.