I have been seeing a large number of questions recently regarding DotNetNuke performance and things that can be done to help improve the performance of the site. Typically these questions surround the SiteLog and EventLog tables so I thought I would post a few simple queries that you can run to help keep your database lean which will keep your DotNetNuke site running at peak performance. Site LogI will start with the Site Log table, the site log functionality is not one that I actually utilize in my site, however for those of you who use it in yours you will want to keep an eye on it, I have noticed that after the log has grown to over about 4-5 thousand records that you can start to notice some performance decreases. Additionally if you are like myself and have turned off the site log I found out that it does not necessarily clean itself up if you turned it off, therefore in my case I had 1000 lingering records, which just unnecessarily increased the size of my database. To check the number of records in your Site Log use the following query SELECT Count(*)
FROM SiteLog To find the oldest record in the site log use the following. SELECT MIN([DateTime])
FROM SiteLog After you have viewed this information you can determine your needed course of action. In my case since the site log is disabled on all portals I simply deleted all records from the table using a simple delete statement. In your case you might only want to purge specific records but that determination is outside the scope of this article. Event LogThe Event log stores portal specific event information such as logins, invalid login attempts and actual errors. If you have a fairly active portal this table can grow very large quite quickly. For my sites with their traffic of about 100-120 visitors per day I have noticed an EventLog of over 2000 records in a 2.5 month period. Now unless you are auditing logins or doing specific tracking you can typically rid yourself of these records without much effect. You can purge these portal by portal via the "Event Viewer" within DotNetNuke, or you can view and modify records via the database. Below I will provide you two helpful queries. To identify the number of records, and the number of records per portal. Note, in the results of the per portal query, you will find many entries listed with a NULL portal id, this is for DNN installation wide items, such as scheduler and application events. --Overall total
SELECT COUNT(*)
FROM EventLog
--Total by portal (null is not portal specific)
SELECT LogPortalId, COUNT(*)
FROM EventLog
GROUP BY logPortalId To identify the oldest records you can use one of the following queries, again an overall minimum and a per portal minimum will be found. SELECT MIN(LogCreateDate)
FROM EventLog
SELECT LogPortalId, MIN(LogCreateDate)
FROM EventLog
GROUP BY logPortalId Again for deleting these records the decision really depends on your portals use of the Event Log and any need for user login auditing. In my case I again do not need this information and currently I have resolved all of my routine errors that were happening, therefore I removed all records in my listing across all sites. I try to complete this at least once as month to ensure that the table size has been reduced. Viewing DB SizeIf you would like to view the size of your database files you can use the query below to view the size of each file for the current database. SELECT *
from SYSFILES Using these tips you can start to get a better understanding of your database size and you can help manage it and keep your DotNetNuke site running at peak performance! Please share your thoughts and concerns below. |