Archive
Monthly
Go
|
|
DNN Blog
Dec
3
Posted by:
Sebastian Leupold
12/3/2010
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 :)
Do you have other tips to share?
20 comment(s) so far...
Re: DotNetNuke Database Performance Tips
After switching to SIMPLE at the end you should also
Alter Database db_name Set Recovery Full
By Costas Zividis on
12/3/2010
|
Re: DotNetNuke Database Performance Tips
sure, if you use full recovery intentionally, you should switch back. However for many sites it might be sufficient to restore files and database consistently from nightly full backup.
By Sebastian Leupold on
12/3/2010
|
Re: DotNetNuke Database Performance Tips
Thanks Sebastian, I've added a link to this post to the performance page on the wiki www.dotnetnuke.com/Resources/Wiki/tabid/1409/Page/Performance_best_practices/Default.aspx
By cathal connolly on
12/4/2010
|
Re: DotNetNuke Database Performance Tips
Thanks a lot for those excellent tips.
I also execute the similar scripts below to clear db and the size of db will reduce by half, which is handy when you would like to backup your database:
TRUNCATE TABLE {databaseOwner}{objectQualifier}EventLog
TRUNCATE TABLE {databaseOwner}{objectQualifier}SiteLog
TRUNCATE TABLE {databaseOwner}{objectQualifier}schedulehistory
That is pretty easy. Just copy and run the those statement from Host->SQL.
Kind regards
Baldwin
By Baldwin on
12/6/2010
|
Re: DotNetNuke Database Performance Tips
Thanks Baldwin. But from my experience, not everyone is willing to loose visitor statics - if you don't need it, it is preferable to turn it off.
By Sebastian Leupold on
12/6/2010
|
Re: DotNetNuke Database Performance Tips
Hi Sebastian, this is really a good post and brillant effort. Many people do not know how the database is performing even they will work in it this usually happens. Your Database Performance tips will really help them to know how it works... Thanks for posting this topic.
By David p Joel on
12/22/2010
|
Re: DotNetNuke Database Performance Tips
I got an error
backup log ISPortal with no_log dbcc shrinkfile(DotNetNuke_Log) dbcc shrinkdatabase(ISPortal)
Msg 3032, Level 16, State 2, Line 1
One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options.
I removed the "with nolog" and got a different error
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
By michaelkbarnett on
1/27/2011
|
Re: DotNetNuke Database Performance Tips
Michael, if you are using simple recovery, there shouldn't be an issue with large log file. How large is your transaction log?
By Sebastian Leupold on
1/27/2011
|
Re: DotNetNuke Database Performance Tips
I need to do this to my database, but I cannot log in to my dnn site, and must do these commands via my hosting accoutn ( go daddy ) How are the commands written to execute within the sql database itself?
same as with Host>SQL?
TRUNCATE TABLE {databaseOwner}{objectQualifier}EventLog
TRUNCATE TABLE {databaseOwner}{objectQualifier}SiteLog
TRUNCATE TABLE {databaseOwner}{objectQualifier}schedulehistory
Thanks
Leona H
By LeonaH on
1/30/2011
|
Re: DotNetNuke Database Performance Tips
Leona,
if you need to run the same SQL statements from a SQL Server tool outside DNN, you need to replace variables in curly braces with its values specified in web.config. By default, {databaseOwner} is "dbo." (or can be omitted) and {objectQualifier} is empty, you may get the proper value from viewing table names. Try default values by running "TRUNCATE TABLE EventLog" etc.
By Sebastian Leupold on
1/30/2011
|
Re: DotNetNuke Database Performance Tips
I was pointed to this article when I asked how to managed my Transaction Log File for a DNN website.
What I wanted to do was schedule regular transaction log backups. What this article suggests with the "backup log DatabaseName with no_log" is apparently deplored and Deprecated!
This artcle explains well the dangers in doing that: www.sqlskills.com/blogs/paul/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
in summary: The whole point of FULL recovery modes is to preserve the transaction log so that the database can be recovered right up to the point of a failure, or to do point-in-time restores. Using BACKUP LOG WITH NO_LOG negates all of that.
Based upon this article is this something DNN admins should still be doing as I'm a little confused now?
Regards
Steve
By Steve Le Monnier on
3/17/2011
|
Re: DotNetNuke Database Performance Tips
after a full backup, the transaction log has to be truncated, because it is no longer needed after a restore. In some cases it doesn't work, and for this case you need to switch to "simple" restore and back (at least this is the only solution I found so far).
By Sebastian Leupold on
3/17/2011
|
Re: DotNetNuke Database Performance Tips
Some usefull exec's, these all run from Host->SQL
EXEC sp_spaceused N'{databaseOwner}{objectQualifier}EventLog'
EXEC sp_spaceused N'{databaseOwner}{objectQualifier}SiteLog'
EXEC sp_spaceused N'{databaseOwner}{objectQualifier}ScheduleHistory'
EXEC sp_spaceused
when you have access to your Database:
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
By Leigh Pointer on
3/18/2011
|
Re: DotNetNuke Database Performance Tips
I have fallow your instructions, but after deleting and truncating tables, my database size is same large. Please how can i optimize dazabase to real size ?
thank you
By Deni Alias on
3/27/2011
|
Re: DotNetNuke Database Performance Tips
Deni,
to reduce size of the database FILES, access your database in SQL Server Management Studio (Express), right click on the datababase root node and select task Shrink Files.
By Sebastian Leupold on
3/27/2011
|
Re: DotNetNuke Database Performance Tips
Mr. Sebastian,
i have connect to my database and i have try to shrink files, but this option is disabled for me. But i have enabled option SHRINK DATABASE, i have start this and file size is reduced, it looks ok for now.
Is it this also right way with SHRINK DATABASE ? I ask because i am not shore if there can be any problems with indexes or any other problem !?
Thank You
By Deni Alias on
4/11/2011
|
Re: DotNetNuke Database Performance Tips
Shrink database should be as efficient as shink files, especially if you are using Simple recovery model. If you are using full recovery, it might happen that transaction log cannot be shrinked - in these cases I suggest switching to simple recovery first, shrink and switch back to full recovery.
By Sebastian Leupold on
4/11/2011
|
Re: DotNetNuke Database Performance Tips
Mitchel Sellers has a nice little utility module called Scheduled SQL Jobs. It can be downloaded here for free:
www.iowacomputergurus.com/free-products/dotnetnuke-modules/scheduled-sql-jobs.aspx
By Roger E on
5/5/2011
|
Re: DotNetNuke Database Performance Tips
I have fallow your instructions, but after deleting and truncating tables, my database size is same large. Please how can i optimize dazabase to real size ?
i have the same problem :(
By morian27 on
3/1/2012
|
Re: DotNetNuke Database Performance Tips
morian27, you need to shrink the database files (right click database node in SQL Server Management Studio and select Tasks > Shrink > Files), which might require to stop the website and wait a bit, to get all requests closed.
By Sebastian Leupold on
3/1/2012
|
|