I'm collecting data on the performance of our Microsoft SQL 2008 server in preparation for moving it to Azure. It's a very low load server (currently max concurrent transactions max out at 3), however, about once a day there's a CPU and hard drive activity spike for about 20-30 minutes. Transaction activity during this time is the same as other times, however, the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly). Free pages fluctuate during the spike and after the spike is done the number of free pages is higher than before. Any way to find out what might be causing this spike? Could this be some sort of garbage collection? Anything specific I could monitor via Performance Monitor to give me a better idea of what's going on?
...once a day there's a CPU and hard drive activity spike for about 20-30 minutes...the buffer cache hit ratio drops to almost 0 at the beginning of this spike (returns to 99+% quickly).
This spike is almost certainly due to daily maintenance tasks - specifically, index rebuilds and
DBCC CHECKDB. These are very resource-intensive operations. For instance, the buffer cache hit ratio change you mentioned is likely due to index rebuilds reading every page out of various indexes as they are processed.
Any way to find out what might be causing this spike?
Look for SQL Server Agent jobs that are configured to start during this time period. You can see an overview of scheduled jobs on the instance by double-clicking "Job Activity Monitor" in SSMS under the SQL Server Agent node.
You can also check for Maintenance Plans under the "Management" -> "Maintenance Plans" node (also in SSMS).
I'm collecting data...in preparation for moving it to Azure
In terms of cost-saving in the cloud, it's possible you could forego some, or all, of the index maintenance. One way to do this would be to use Ola Hallengren's Index and Statistics Maintenance procedure, which will skip rebuilds on indexes that are small or not-very-fragmented.