Profiling item fetch database performance

Any schema-change decision must be driven by data/statistics analysis.

One needs to test same functionality before, and after implementing the suggested change to see the real outcome.

A SQL Server Profiler provides statistics on how much resources were spent to process the SQL query.

An analysis of the statistics would actually show if any improvements were introduced, and if it is worth implementing the change.

Test scenario

1. Create database backup
2. Rebuild SQL Indexes to ensure low fragmentation rates
3. Use stock Sitecore Testing DB performance page to collect the baseline first
4. Implement database schema changes
5. Ensure that SQL indexes are in a healthy state
6. Restart SQL, and Sitecore to clean all cache layers
7. Use stock Sitecore Testing DB performance page to collect second profile

Configuring Profiler

The SQL Server Profiler is a super-powerful tool that allows to capture dozen of various metrics.

We`ll use only base ones ( CPU, Reads, Writes, and Duration) in this session, though.

1. Start SQL Server Profiler application
2. Select 'New Trace' ( CTRL+N hotkey )
3. Open 'Event Selection' tab and apply filters as per screenshot
4. Ensure SQL Indexes on Items, and Field-related tables are in a healthy state, and start the test

Profiler configuration

After the test is finished stop tracing, and save performance trace as a table:

Once data is saved, implement needed database changes, restart both Sitecore, and MSSQL Server.

Now we can repeat same set of operations, and record a new profile.

Comparing resulting profiles

A handly SQL query can be used to get aggregated statistics:

SELECT 'Optimized' as 'Where', 

      count(1) as TotalUp

      ,sum(op.[CPU]) as TotalCPU

      ,sum(op.[Reads]) as TotalReads

 FROM [master].[dbo].[big_Optimized] as op

 WHERE op.CPU IS NOT NULL

The results in my local environment are:

• 35% less reads
• 25 % less CPU

Please keep in mind that test results much depend on variety of data, and custom logic implemented in solution.

An end decision must be made after a few rounds of testing of following functionality:

• Publishing
• Live site navigation
• Content Editing

The best performance optimization is to cache as much data inside Sitecore Caches as possible, thereby minimizing the number of requests to database.

We`ll cover Sitecore Caches configuration in next article