clear sessions table

Yes, to clarify this change. There was a cleanup routine that ran on the sessions table which was triggered when each session started. On big sites, this could become slow, and would run often (each new session).

The solution did two things. First, it put an index on the dates fields in the sessions table:

/****** Improve cleanup of expired sessions ******/CREATE NONCLUSTERED INDEX [SESS_DateLastUpdated] ON [dbo].[tblKartrisSessions](	[SESS_DateLastUpdated] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO

This makes the deletion of expired sessions far quicker because it's deleting based on the date, and the index allows it to identify and find items by date much more quickly.

Secondly, we moved the cleanup sessions routine out of the session_start and into the application_start part of the global.asax. This way, the routine only runs occasionally, instead of many times a day. Because it only really needs to do an occasional clean up.

Author	Message
SAbow	Posted Tue 6 Mar 2018
Supreme Being Group: Forum Members Last Active: Tue 2 Feb 2021 Posts: 77, Visits: 334 45,081	My sessions table gets large enough to crash my site a little too often. I'd like to automatically clear my sessions table every few days - has anyone done this before? Tags clear session
	REPLY QUOTE LIKE 277
Neil	Posted Wed 7 Mar 2018
Supreme Being Group: Forum Members Last Active: Sat 3 Nov 2018 Posts: 192, Visits: 5,220 123,411	You may want to look at the V2.910 update here https://www.kartris.com/Kartris-v2.9010__n-35.aspx quote : ''There are some major performance improvements in this release; various index improvements and also the code to clean up sessions has been optimized which results in a big boost particularly to very busy sites.'' This update has made a huge difference to the stability/speed of our install Edited Wed 7 Mar 2018 by Neil
	REPLY QUOTE LIKE 249
Paul	Posted Fri 9 Mar 2018
große Käse Group: Administrators Last Active: Tue 10 Sep 2024 Posts: 807, Visits: 2,748 508,410	Yes, to clarify this change. There was a cleanup routine that ran on the sessions table which was triggered when each session started. On big sites, this could become slow, and would run often (each new session). The solution did two things. First, it put an index on the dates fields in the sessions table: /**** Improve cleanup of expired sessions ****/CREATE NONCLUSTERED INDEX [SESS_DateLastUpdated] ON [dbo].[tblKartrisSessions]( [SESS_DateLastUpdated] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO This makes the deletion of expired sessions far quicker because it's deleting based on the date, and the index allows it to identify and find items by date much more quickly. Secondly, we moved the cleanup sessions routine out of the session_start and into the application_start part of the global.asax. This way, the routine only runs occasionally, instead of many times a day. Because it only really needs to do an occasional clean up. -- If my post solves your issue, can you 'Mark as Answer' so it's easier for other users to find in future. If you would like to be informed of new features, new releases, developments and occasional special bonuses, please sign up to our mailing list: http://bit.ly/19sKMZb
	REPLY QUOTE LIKE 269
SAbow	Posted Tue 13 Mar 2018
Supreme Being Group: Forum Members Last Active: Tue 2 Feb 2021 Posts: 77, Visits: 334 45,081	I added the index to the date fields using the code you posted. I didn't move the deletion of expired sessions out of the session_start - I specifically want the sessions table to be cleaned up often. In fact, I really want to have the code that runs when you clear the sessions from the backend (Configuration>Database Admin>Clear Data>Sessions Related Data) run often. Thank you for your help
	REPLY QUOTE LIKE 263
Paul	Posted Fri 16 Mar 2018
große Käse Group: Administrators Last Active: Tue 10 Sep 2024 Posts: 807, Visits: 2,748 508,410	The code to clear up sessions really doesn't need to run more than once or twice a day. In fact, even once a week should be fine. If you move the code to the application_start as suggested, then it will run if you restart Kartris - which you can do from the back end (the restart button on the home page). What that actually does is resave the web.config file (so needs right permissions), and this will trigger a restart of the application (unless you've disabled this default IIS behaviour, which is possible but not recommended). Running it on every session means you have an extra query run each time a new visitor arrives at the site each day. On a busy site, that's a lot of extra queries. I don't see any cases where the benefit of having a few more session records purged (by running the purge every minute or more) will offset the extra 100s or 1000s of queries. -- If my post solves your issue, can you 'Mark as Answer' so it's easier for other users to find in future. If you would like to be informed of new features, new releases, developments and occasional special bonuses, please sign up to our mailing list: http://bit.ly/19sKMZb Edited Fri 16 Mar 2018 by Paul
	REPLY QUOTE LIKE 252
SAbow	Posted Mon 19 Mar 2018
Supreme Being Group: Forum Members Last Active: Tue 2 Feb 2021 Posts: 77, Visits: 334 45,081	I hear what you're saying. I'll move the code into the application_start, as you are suggesting.But I still need the sessions table to be cleared out every couple of weeks - have any of your other customers needed to automate this? Or do I need to manually click Restart Katris every other week?
	REPLY QUOTE LIKE 246
Mart	Posted Tue 20 Mar 2018
Top Banana Group: Administrators Last Active: Wed 18 Nov 2020 Posts: 148, Visits: 3,450 95,667	If you need something to run eg every week then best way is to write a vbs script and set it to run weekly using a scheduled task. Obviously this is only an option if you have your own server or vps. In most cases a server is going to be restarted at least monthly (windows updates) so the application_onstart would fire at least this frequently.
	REPLY QUOTE LIKE 233
Paul	Posted Thu 22 Mar 2018
große Käse Group: Administrators Last Active: Tue 10 Sep 2024 Posts: 807, Visits: 2,748 508,410	The default time in IIS to recycle an app pool is 1740 minutes, or 29 hours. So assuming you've not changed anything, and your host hasn't, the application_start will be triggered every 29 hours anyway. -- If my post solves your issue, can you 'Mark as Answer' so it's easier for other users to find in future. If you would like to be informed of new features, new releases, developments and occasional special bonuses, please sign up to our mailing list: http://bit.ly/19sKMZb
	REPLY QUOTE LIKE 275
SAbow	Posted Thu 22 Mar 2018
Supreme Being Group: Forum Members Last Active: Tue 2 Feb 2021 Posts: 77, Visits: 334 45,081	That's a useful piece of information. Thanks, I'll put it into Application_Start and that should solve my issue
	REPLY QUOTE LIKE 254

clear sessions table

clear sessions table

Similar Topics

Reading This Topic