Customer Support Portal
Language
 
Home>Knowledge Base>Root>Hosting>DNN hosting>Tips to improve DNN database performance
User Login
Username
Password
 
 Login
Information
Article ID34
Created On12/23/2010
Modified12/23/2010
Share With Others

Tips to improve DNN database performance

Besides web server configuration, DotNetNuke performance and cache settings or the number of modules installed, the database is a key for performance of your web site. There are a number of items, you may take care of:
  • Check the size of your transaction log, in DNN 5 you may read it from Database tab inside Host :: Dashboard. Usually the size should not exceed more than a few MB and get truncated on nightly backups. To truncate it, run the following statement from Host :: SQL
    backup log DatabaseName with no_log 
    dbcc shrinkfile(Database_Logname)
    dbcc shrinkdatabase(DatabaseName)
    In some cases, you need to switch to simple recovery model first:
    ALTER DATABASE  DatabaseName SET RECOVERY SIMPLE
  • In a number of DNN versions, the Eventlog table affects performance due to an inefficient index and shouldn't grow beyond a few hundred entries. To check number of entries, run the following statement from Host ::SQL:
    SELECT COUNT(*) N from {databaseOwner}{objectQualifier}EventLog
    If clearing the eventlog fails inside Admin :: EventViewer, you may do it from Host :: SQL by running:
    TRUNCATE TABLE {databaseOwner}{objectQualifier}EventLog
  • Same applies to Scheduler log:
  • TRUNCATE TABLE {databaseOwner}{objectQualifier}ScheduleHistory
  • sometimes a search table may exaggerate, you may safely clear the whole tables by running
    TRUNCATE TABLE {databaseOwner}{objectQualifier}SearchItemWordPosition
    DELETE {databaseOwner}{objectQualifier}SearchItemWord
    DELETE {databaseOwner}{objectQualifier}SearchWord
    DELETE {databaseOwner}{objectQualifier}SearchItem
    and re-index from Host :: Search Administration
  • Update statistics used for query optimisation:
    Use DatabaseName
    exec sp_updatestats;
  • consider setting up a proper maintenance plan with integrity checks, rebuilding indexes etc.
A performing database is fundamental for your web site speed :)

Text by:Sebastian Leopold Original article