Deciphering the 8.0.12 ASPDNSF Monthly Maintenance Script01/23/2013
AspDotNetStorefront is an e-commerce system used by more than 8,000 ASP.NET developers. Being an ASP.NET development shop we support it. It works and can semi-easily support sites earning $50-100MM/year. We don't reccomend AspDotNetStorefront because we prefer custom ecommerce soltuions (and our clients need flexibility unavailable by AspDotNetStorefront and other SaaS solutions). That said, AspDotNetStorefront is in our wheelhouse. We know it all too well.
Monthy SQL Maintenance: Do It
The Monthly Maintenance script is a Stored Procedure. In version 8 it is not found in the admin interface and can only be run from the SQL management studio. It should be run at least every two weeks, possibly more often, on large scale ASPDNSF sites. Database maintenance and tuning the SQL database for performance become an issue at ~25-40 concurrent cart sessions. Especially if you’re seeing deadlocks on aspdnsf_SynchronizeCart or aspdnsf_GetCartSubTotalAndTaxand. Some of the ASPDNSF sites that we work with take a order every 60 seconds, so tuning and maintenance is critical. A rewrite of aspdnsf_GetCartSubTotalAndTaxand sProc is warranted in version 8 because it can be problematic on larger sites.
The Maintenance Script Parameters:
InvalidateCustomerCookies: There’s almost no reason to ever set this to true. All it does is force customers to log in again the next time they visit your site, if they’d had their credentials saved.
PurgeAnonCustomers: Do this, as long as you’re not running the maintenance more often than every 2 weeks or so. If you’re going to do it more frequently than that, maybe only do this every other time. Anonymous carts don’t last too long, but you don’t want to be killing them early and maybe chasing off a customer.
CleanShoppingCartsOlderThan: Generally we recommend this be set to just a couple of months. We go as long as 90 days.
CleanWishListsOlderThan: This is probably safe to leave at a relatively high number. Most queries on the ShoppingCart table include are looking for the normal shopping cart CartType, so these records will be ignored for the most part. You’d have to have a pretty incredible number of them to have much of an impact. We typically set this to 1500.
CleanGiftRegistriesOlderThan: This only impacts sites with the gift registry turned on, so this one doesn’t really matter for most folks.
EraseCCFromAddresses: Unless you have old data that you want to keep hold of for some reason, we would set this to true just for safety’s sake. We generally disabled the PABPEraseCCInfo sProc and run a custom script to clean up cc numbers nightly. CC information in the database shouldn’t be kept if it’s not necessary.
EraseSQLLogOlderThan: 90 days is a safe number.
ClearProductViewsOrderThan: I believe the ProductView table is only used for dynamic related products in your version, which you guys don’t have turned on. It should be safe to set this pretty low to keep that table small.
EraseCCFromOrdersOlderThan: This setting clears CC information from the Orders table, but skips the CC information in the Address table that the setting above nukes. I would make a decision based on the info I gave above, and then set these 2 the same.
DefragIndexes: This should probably only be done if you’re doing the maintenance in ‘off hours’ for your site. It can be slow. If your DB has been modified you should put a little time into deciding if this is safe to run or not.
PurgeDeletedRecords: We usually advise people to set this to true every time, provided that they’re doing frequent backups. In the off chance you end up needing a record that you had deleted in one of the ~30 tables it cleans up, you can always dig that data out of a backup and in the meantime it saves on DB size.
Most of the time we run this script with the website offline (e.g. app_offline.htm). It will take longer to run maintenance if the website is running. Plan on 6-30 minutes of downtime on your first run if you have good hardware and a large site.
Give Dirigo a call if you're having performance issues with ASPDNSF. We can help!