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