Perfmon and Profiler - Interesting side effect?

Question

Post reply

Perfmon and Profiler - Interesting side effect?

derek.colley

SSCrazy Eights

Points: 8040
More actions
May 21, 2012 at 3:30 am

#261236

Hi, I just want to see if anyone else has noticed an interesting effect with perfmon and Profiler?

I'm running a perfmon data collector set and Profiler SP_TSQL_Replay set on a production server from a different server (so I don't skew the results and possibly bork the prod server performance).

I'm going to collect about 6 hours of data then take it offline and crunch it in Excel, Profiler and PAL.

The destination file directories for both tools are on a UNC path i.e. \\MYSERVER\c$\myfolder\file1.blg and file2.trc.

The file size of both the .blg and .trc isn't increasing in real-time like I'd expect it to if the files remained local. The .blg is static at 65,536 and the .trc is 0 bytes.

Yet Profiler and perfmon are both happily collecting stats and (apparently) recording information.

I tested perfmon beforehand by running a trace for 30 minutes then stopping it. Once stopped, the file size of the destination file rose from 65,536 bytes to around 200k, suggesting the content had been dumped in afterwards.

So, if the real-time data isn't been written to disk in real-time, where is it going? And do I need to worry about this?

---
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.

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

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

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

Dave Ballantyne

SSC-Dedicated

Points: 33556

May 21, 2012 at 4:41 am

#1490324

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: 33556 More actions · Answer 3

Dave Ballantyne

SSC-Dedicated

Points: 33556

May 21, 2012 at 5:10 am

#1490344

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.