Default trace consuming too much IO

Question

Default trace consuming too much IO

yo.mawtin

SSC-Addicted

Points: 444
More actions
October 17, 2016 at 6:07 am

#400876

Resource Monitor tells me that sqlservr.exe is continuously reading about 1,200,000 B/sec from log_636.trc. Seems crazy. It's something like 10x - 100x what other IO operations do. I believe this is the default trace. According to sys.traces I have a trace running that writes to this file and the id=1. According to sys.configurations, my default trace is enabled.
The sql server version is 11.0.6518 which is SQL Server 2012. It's a development (virtual) machine and it's the primary node in an availability group. I have a production computer which is the primary node in an availability group. Its default trace is enabled also but I don't generally notice disk activity to its trace file when I look in Resource Monitor.
I suspect I have something misconfigured. Any idea where I should look? I was thinking I might disable the default trace.
Thanks.
Image FileRead (B/sec) Response Time (ms)
sqlservr.exe M:\MSSQL11.MSSQLSERVER\MSSQL\Log\log_636.trc1,199,673 108

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

yo.mawtin (10/17/2016)
Resource Monitor tells me that sqlservr.exe is continuously reading about 1,200,000 B/sec from log_636.trc. Seems crazy. It's something like 10x - 100x what other IO operations do. I believe this is the default trace. According to sys.traces I have a trace running that writes to this file and the id=1. According to sys.configurations, my default trace is enabled.
The sql server version is 11.0.6518 which is SQL Server 2012. It's a development (virtual) machine and it's the primary node in an availability group. I have a production computer which is the primary node in an availability group. Its default trace is enabled also but I don't generally notice disk activity to its trace file when I look in Resource Monitor.
I suspect I have something misconfigured. Any idea where I should look? I was thinking I might disable the default trace.
Thanks.
Image FileRead (B/sec) Response Time (ms)
sqlservr.exe M:\MSSQL11.MSSQLSERVER\MSSQL\Log\log_636.trc1,199,673 108

My first inclination would be to see what's in the trace and what is prevalent. It may actually be correctly identifying a fundamental problem with the system and/or code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yo.mawtin SSC-Addicted Points: 444 More actions · Answer 2

Thanks for the suggestion. The trace activity is coming from Red Gate Software - SQL Tools. If I ask my coworkers to close SSMS (because we use several Red Gate tools), the log activity dies down, the disk queue length returns to normal, and the system performs better. Perhaps I'll check with Red Gate.