SQLServr.exe Spiking CPU to 100%

[Radiatorman]

After upgrading to Gold it seems like SQLServr.exe will spike to 100% when navigating through the site or performing a search. I would expect this if there were lots of visitors on the site but it can and will happen with one or two visitors. Maybe I don't have something set up right but this is serious impacting performance. Has anyone else encountered this behavior and have a fix?

Server VPS:
Windows Server 2008 R2 Standard 64-bit
Intel Xeon 1.87Ghz 8gb RAM

MSSQL:
SQLServer 2005 Standard
Version 9.00.5000.00

[Katie]

A lot of factors can be involved. For instance, a 1.87 Ghz is not that speedy. Is it dualcore, quad ?

What is the size of your database?

What version of Gold did you upgrade to?

Did you cleanup the AC7 database before upgrading (to remove anonymous users, activity logging, etc)? There were some maintenance issues in AC7, and if not taken care of before upgrading, then it might be a problem in Gold.

I didn't see any delays when searching the site. Hopefully, this is related to the maintenance...

Thanks

[Radiatorman]

Thank you for checking out the site.

It is a Quad Core Virtual Private Server

The database is 313mb. I have 5000 products at this moment with 102,000 entries in the ac_catalognodes table

AbleCommerce GoldR7 (build 7118)

I have 44635 anonymous users which are deleted every 15 days.
Search history is being deleted every 30 days
I turned off page tracking to speed things up but there was just 54,000 record in there being deleted every 15 days

I turned off Lucerne as well to try to speed things up.


But if you do a search for "HP" it takes a good 4 - 5 seconds to return results. Our try navigating categories and pages quickly to simulate a "heavy" load. The site will start to drag.

[Katie]

Are you testing from a separate network? When I searched on HP, it took less than a second to come back.

The menu system and categories also seem reasonable to load. The only delay (and it was very minor 2-3 seconds) were the Laptop AC Adapter and Laptop Battery categories. I noticed that both of them had between 1700 and 3500 products each. I believe you are using a Category page with "deep item" display. When you have a large number of products, the Category pages that are resulting in all child objects within all sub-categories can be a little slow. For me, it didn't seem unusually slow though. If you are able to, you might try changing to a different Category display page that only outputs the products within the selected category. If it speeds up, then we know that is the cause.

[Radiatorman]

Thank you for investigating. I will see if that makes a difference.