Perfmon and Profiler - Interesting side effect?

Question

Perfmon and Profiler - Interesting side effect?

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

I dont think this is particularly worrying , more worrying is that are running profiler live. Dont do this, use server side tracing . Profiler will bork the server

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

Clear Sky SQL
My Blog[/url]

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Thanks Dave, I'm running Profiler from a different, non-prod box and connecting remotely to the target, so little resources being consumed on the prod. I'm using Profiler for ease, frankly - so I can tie in the perfmon stats and start some analysis later on.

I agree, I don't think that there's much to worry about wrt the output files, I'm keeping a close eye on disk capacity to make sure it doesn't get eaten too quickly!

Thanks,

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Thats is not the point , profiler will introduce locking that can slow down a production server. By moving it to another server you have made it even worse.

http://weblogs.sqlteam.com/dang/archive/2007/12/16/Avoid-Causing-Problems-with-Profiler.aspx

Clear Sky SQL
My Blog[/url]

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 4

A valid point, however this particular prod server is not an OLTP and doesn't have high tx throughput, although available memory and CPU are low. My main concern by using a different box was to save memory rather than mitigate query tx time. Agree that with high throughput general performance of SQL Server will slow because of locking but not applicable here. I've also been keeping an eye on it today and prod impact has not been noticeable.

I don't want to get into a discussion about SST vs Profiler, original question was about output of the tools to the respective files. Not yet found an answer though. I will be stopping the trace this afternoon, if the log files go AWOL I'll rerun to local disk and possibly raise a ticket with MS.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 5

A valid point, however this particular prod server is not an OLTP and doesn't have high tx throughput, although available memory and CPU are low. My main concern by using a different box was to save memory rather than mitigate query tx time. Agree that with high throughput general performance of SQL Server will slow because of locking but not applicable here. I've also been keeping an eye on it today and prod impact has not been noticeable.

I don't want to get into a discussion about SST vs Profiler, original question was about output of the tools to the respective files. Not yet found an answer though. I will be stopping the trace this afternoon, if the log files go AWOL I'll rerun to local disk and possibly raise a ticket with MS.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 6

I have also seen this behavior. One of the things I've noticed is that if you run a query against the trace file (something like SELECT * FROM fn_trace_can't_remember_syntax), it will then show the amount of space used in the file in windows explorer. Otherwise, it will not appear to be growing. In our case, we used a smaller trace file size but expanded it to multiple files, and you wouldn't see the file reach it's full size until the trace had created a new one.

Anyway, we were also monitoring the server with perfmon and didn't have any issues as the file grew. So my experience was that this odd behavior is not a concern.