adTempus database performance - Community

smp (34 posts)

May 15, 2009 09:51 AM

Accepted Answer

adTempus 3.0.5

While doing some administration, I noticed that the sqlservr.exe process was consuming all of the available CPU cycles on the server for several minutes when no jobs were executing. I am using the MSDE engine for the database. While looking at that, I noticed that the sqlservr.exe process is consuming roughly 650MB of memory according to the Mem Usage column in Task Manager.

This got me thinking and searching. I found an article which indicates adTempus has problems if the database is over 2GB. My database is only ~110MB (assuming the database is "adTempus_<server>.mdf".

I guess I'm wondering if the memory usage of the database seems within a normal range? Is the CPU behavior I described normal activity? What other factors besides the size of the database should I be looking at when trying to determine if I need to move the database from MSDE to the full SQL Server?

smp (34 posts)

May 15, 2009 09:54 AM

Accepted Answer

Uh, just found this:x

www.arcanadev.com/support/kb/K00000059.aspx

Seems the MSDE memory usage is normal. And since the database is well below 2GB, it is likely I'm just fine sticking with MSDE.

Bill Staff (604 posts)

May 19, 2009 06:25 AM

Accepted Answer

There is no need for or benefit to moving to a full SQL Server instance for your setup.

As you noted from the article you cited, it is normal for MSDE to use more memory than it seems like it should. You can limit the memory usage as described in that article, but we don't recommend it.

You are correct in identifying your database file, and the size is fine, so that's not an issue.

It is less normal for sqlservr.exe to be using all the CPU cycles, so you might want to keep an eye on that and see if it happens often. If so we may need to look at it further. There are two likely reasons why it would have been using CPU time when no jobs were running:

A) A database backup is performed every day around 1:30 AM.

B) There is a "scavenger" thread that occasionally runs to clear out old job history. If you are generating a lot of history records (running a lot of jobs) it will have more work to do and will run longer/harder. This purge process runs about 2 hours after the adTempus service starts, and about every 12 hours thereafter. So if the problem continues to occur at roughly 12 hour intervals, that's probably the cause. If so, we can increase the purge frequency so less history accumulates between rounds; this might improve the performance.

smp (34 posts)

May 19, 2009 07:22 AM

Accepted Answer

Thank you very much for your detailed response. Regarding the service and CPU cycles, it does not appear that the process is causing the condition with any sort of consistency. I must have caught it by chance. I'm not too worried about it since the server appears to be fine.

Thanks again.