April 12, 2010 at 7:17 pm
How can i confirm that sql server was using 100% cpu 2 days back. I am sure that it was sql server that took 100% cpu but how do i find such report where it shows what process was using that ?
April 13, 2010 at 6:45 am
The reason i am looking for this information is last day when there was regular job runing whch usualy uses only 25% of CPU but it took 100% CPU instead as i was also running a profiler background and inserted the result into a sql table which had only 10k records after i stopped thr profiler. But when i stopped the profiler the CPU usage came down to 25% again. This clearly proves that profiler was taking that much of CPU which was wiered to me.
Does the profiler take so much of CPU usage making the system hang in there ? How would i prove that profiler was the one using that much of CPU ? any idea
April 13, 2010 at 7:18 am
I have definitely seen Profiler bring a PC to a hault and would not be at all surprised if it did the same to a server. Unless you were pulling some pretty specific performance monitor counters you will not be able to prove it, but it seems that you are confident that this was the cause.
So I would say the best thing to do is to not run profiler on that server and do a server side trace
http://www.sqlservercentral.com/articles/trace/69559/
Much safer and less resource intensive that Profiler. I would not expect a trace that pulled 10,000 rows to be problematic but if that is what you are seeing this is a much safer bet.
April 13, 2010 at 7:27 am
is there a way i can find from the past run which process was taking so much of CPU, though i beleive that profiler caused it but still i am seeing only few records in the events table so its strange to me.
If profiler is runing bkgrd and causing the issue why would i end up having only few records for the events ?
April 13, 2010 at 7:33 am
As Mike has stated server-side trace is much less intensive in that the trace will only capture records of the events you specify at the beginning. If you use the GUI to perform a trace it captures all events first and then filters it down to display only those records you specified.
In either process it is better to save the trace to a file and then import into a table. If you were running profiler and saving straight to a table I would be pretty confidient that, that was your CPU hog. If it was not the profiler process byitself could have been combined with SQL Server processing the profiler table.
As far as proving it was the profiler process taking up CPU, you would only be able to truly prove it if you were using performance monitor to capture the information or a 3rd party monitor tool was tracking the server usage. I am not aware of Windows logging this information by default.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 13, 2010 at 7:35 am
It is surprising that it would only return a few records and still cause you problems, but that does not make it impossible by any stretch. It depends on a lot of different things (in my experience profiler was taking up a bunch of memory and that dominoed into taking up a bunch of CPU).
The only way that you could prove that this was the cause would be to have had either been watching it when it was running and actually saw the CPU spike was attributed to that specific process or to have been gathering some specific perfmon data to show this (the dmv sys.dm_os_performance_counters does not give you this sort of detail). Without that, going with your best guess which you seem relatively certain of seems reasonable to me.
The safest bet is to not run profiler on the server because it can be resource intensive. Server side traces definitely are not, however.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply