Background - I support an application which runs on ASP and little bit of .NET and SQL server 2000. Have a separate IIS web and sql server running on windows. Lately I have been seeing performance issues. I notice the application becoming very slow every day at a particular time for few mins.. The time it happens is not consistent.. It's been happening at 10.45 AM for past few days and it might start at 4 PM from tomorrow.. There is no pattern..At this time I see the CPU utilization reaching 99% and is consumed by sqlservr.exe and CPU utilization drops drastically in few mins and the application runs normal.. I have tried using profiler and can't find any thing. I saw this message "Some trace events have not been reported to SQL Profiler because the server has reached its maximum amount of available memory for the process." being logged quite a few times in trace file.. Not able to find the bad process which is consuming entire CPU..I did check the paging, disk i/o and everything seem to function normal. I have also disabled all db processes running at that time.. but i still continue to see the problem.. there was no recent changes to ASP code either... Any thoughts on why this issue could occur ?
Welcome to the forum!! What SQL sp do you have? Is sql server dedicated? When you say 99%, this value came from Performance Monitor?
SQL SP2 is what I have and Yes, the sql server is dedicated.. I found sqlservr.exe consuming 99% in windows task manager on the windows server.
It's been a while since I have used SQL 2000, but in general sql server is rather aggressive and will use all available resources. Is this a new behavior or are you just noticing it?
