Running the following set up for our dedicated MS SQL server.
Windows Server 2003 SP2 32 Bit
12 GBs of Ram
2.25 GHz Intel Zeon L5520
Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3625
Operating System 5.2.3790
When things are running normally we are consuming between 35 - 40% CPU and about a 30% of our available memory. 9.91GBs PF Usage.
The DB is quite large, about 3.5GBs judging by the backup file size.
The transaction logs are stored on a separate disc.
We have one particularly large table with 1,252,803 records.
So for a several years we have had no problems running this DB and this box but lately things have been going awry. For starters, the scheduled maintenance kept failing when trying to reorganize indexes. I have since excluded that and now must rebuild indexes and stats nightly.
About once or twice a week, we notice that the CPU usage spikes to 100% and stays there and DB reads slow to a crawl. Nothing will bring the CPU usage back to normal levels (including restarting the SQL service) except rebuilding the indexes and sometimes the stats as well.
I have very little experience debugging such problems (basically I know nothing) so I'm hoping one of you fine experts can help me debug and solve this problem.
Thanks in advance.