July 28, 2005 at 10:22 am
Hi,
I'm got something weird happening with profiler, it is showing 0 duration for all RPC:Completed and SQL:BatchCompleted events??!!! Now whilst I should be happy that the database is running so quickly I know this is not really the case! (We are getting timeouts from some of the applications).
Has anyone ever found that profiler doesn't capture all the data that it should? Or any other strange occurrences and how you fixed them?
I've tried stopping and starting profiler and re-booting my pc. Do you think that a server re-boot would help?
cheers
Dave
August 1, 2005 at 4:18 am
Hi Dave,
Can I just confirm that you are capturing the SQL:BatchCompleted events rather than the batch starting ones, I know you've said you are but please double-check, if it's completed then you do have a problem. Also which version of SQL are you on and what service pack?
You should make sure you are running profiler on the server as sometimes running it from a PC can cause problems like this (I've not seen them myself but have heard about them), also, I have seen it not capture everything on a very busy server but if that happens then you get a message logged saying that it has missed some results.
August 1, 2005 at 5:51 am
thanks Mike,
I've just re-checked and I'm definitely logging the completed events, and we're running SQL 2000 standard with SP4
Where is the message logged about missing data? the SQL event log?
I'm reluctant to run profiler on the server because of the performance hit (it is creaking at the moment!). I'm using the trace to give an early warning of timeouts from our applications, do you know any other way to monitor for long running stored procedures?
Dave
August 1, 2005 at 6:20 am
I tend to run profiler on the server but have it set to only log queries with a duration of 15000 ms (15 seconds), this significantly reduces the results being returned and doesn't seem to have much of a hit on the server. If 15 seconds is too long then simple reduce the value to a more acceptable one.
If you're running profiler remotely then it's still going to have a similar hit, but with added network traffic too. other than profiler I don't really know of any other mechanisms for identifying long running procedures, obviously there are some available but I haven't tested them. To my mind they'd still incur the same hit so i don't know if you'd be better off using one or not.
August 1, 2005 at 6:36 am
I was filtering the trace for everything over 1500 ms, for the same reason (to reduce impact on the server) but one day when I started the trace there was nothing all day!! If I remove the filter I see all the completed events flying up the screen.
I'll try running profiler form the server and see if it makes any difference.
thanks
Dave
August 1, 2005 at 6:40 am
that kind of implies that none of the queries were running more than 1500 ms, but if that's not the case then you should definately double-check by running it on the server.
As for the message you get (forgot to include it in the last post), it's actually in the text in profiler rather than any logs, and if your system is as busy as you say then it wouldn't be unexpected to see it happening.
Mike
August 1, 2005 at 6:47 am
I thought it might be because there were no events longer than 1500ms, so I set the filter to anything greater or equal to 1ms and ran the trace again and nothing was picked up. Which lead me to the conclusion that something strange was happening.
(Also when the trace didn't have a filter on the duration, all the events have a duration of 0)
I'll let you know what happens on the server.
Dave
August 3, 2005 at 11:33 am
Diagnostic Manager is an excellent SQL Server monitoring tool that will capture this and much more for you.
(Not involved, just a satisfied user.)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply