Tips and trix for the EPiServer CMS database
Update statistics
Before you starting troubleshooting the database ensure that all statistics is up to data for the database (otherwise you can’t trust the results from the profiling tools).
SQL2008
EXEC sp_updatestats
List all page types for a specific property name
select pt.Name
from tblPageDefinitionType dt
inner join tblPageDefinition pd on pd.fkPageDefinitionTypeID = dt.pkID
inner join tblPageType pt on pt.pkID = pd.fkPageTypeID
where pd.Name = 'MyPropertyName'
Returns a list of page type that includes a property named “MyPropertyName”
EPiServer Connection timeout CMS 5 and above
EPiserver CMS 5 and above doesn’t respect the "connection timeout" in the connectionstring, it has to be set in the site configuration in EPiserver.config
<episerver xmlns="http://EPiServer.Configuration.EPiServerSection">
...
<sites>
<site description=" site1" siteId=" site1">
<siteSettings databaseQueryTimeout="1:0:0"
SQL timeout in EPiServer.DataAbstraction.DynamicProperty.ListTree()
Dynamic Properties performance revisited A great finding by Svante regarding dynaimc properties before EPiServer CMS 6 R2