Just having a bit of a problem with my sql server.
I am a programmer for an ISP. We run our system on SQL Server 2k.
Our db is running a 1.8 GH pentium 4 with a gig ddr ram on win 2k sp2.
The problem we are having is sometimes our Databases machine's CPU jumps up to 100% and stays up there for about 10 - 30 seconds at any given time. I check the task manager and SQL Server has the CPU. When this happens our server performance (in particular our SQL Server software) decreases dramaticly and we get timeouts and slow response times.
I have checked the indexes on all the frequently used tables and they seem to be correct, I dont think there are many tables in our db that arnt indexed and that are over 500 records. We have tables in our db that are not indexed, but only have about 1 - 50 records in them, will this make a difference if they indexed or not??
Well if anyone could help me try and work out what this is and how to fix it that would be great.
Since you already know what process is causing the problem - I would try profiler using tsql events and see if a particular query is dogging your system. However, realize profiler has it's own overhead. Have you been able to find a pattern to this problem - time of day / certain users ... ? What else is running on the server at that time ?