I have a MS Access 2007 application using SQL Server 2008 R2 as a backend. This is pretty much a brand new install of a recently revamped MS Access application. Many times throughout the day the application will freeze up - When I check the SQL server during that time, the CPU is pretty much maxed out by the SQLServer process. I can barely remote in to the server during this time because there are close to no resources left. When this occurs it can last up to about 5-10 minutes.
The SQL Server is in a virtual enverionment (using VMWare ESXi). 4 Cores and 6GB of memory are allocated to this server.
Of course it could be poor DB design, missing indexes, etc contributing to this problem...but considering the length of time the CPU was maxed out, I'm thinking there could be a larger issue. The issue also 'appears' to be intermittent. DB integrity checks, index rebuilds occur nightly.
I want to rule out SQL Server instance itself as the issue. Where should I begin troubleshooting the SQL side? Also, SQL installatioin was mainly defaults.