Troubleshoot high CPU usage of the EpiServer database.
Symptom:
The database server has a CPU load over 80%.
An easy way to see what’s going on in the database is to use SQL Server Profiler to see the questions that the application queries the database with.
I usually set up a filter on "LoginName" and add the field "RowsCount" to the trace.
Normal problems with an EPiServer database
EPiServer build in search
exec netKeywordSearch …
generate a high value in “Duration“, this means that the search functionality takes a long time to get its values. By default the build in search functionality uses OR notation for the search words and indexing all words in the searchable properties on the page. That means if you search for “at or not in” the database would properly returns all the EPiServer pages in the database and files in the filesystem.
Solution:
I recommend using another search engine, if you don’t have budget or money for this I recommend to use “Google AJAX Search API” it better and more robust. It takes about a day to implement organic search functionality with it (I have never test it with documents, but is should work).
ObjectStore
exec RelationListFrom… or exec RelationListTo…
return a large number of rows in the "RowsCount" field (more than 1000). It means that you probably have an unbalanced tree in object store. This can happen when the versioned file system has extremely many file in one folder.
Solution:
Call EPiServer support and ask for an application to examine your sites object store.
exec ItemFindByName… or exec ItemLoad
appears many times, this means that many object store objects are realized to memory objects.
Solution:
Execute the following SQL query it will give you a hint what’s going on
select tblItem.pkID, tblItem.name, tblSchema.SchemaId from tblItem
join tblSchema on fkSchemaId = tblSchema.pkID
where tblItem.pkId = '<the pkId from the query>'
To be written...