Opinion: Get used to reading traces and logs before you need them

I used to do a lot of work at the operating system and network level. I was always fascinated watching people use network trace tools when they were trying to debug a problem. The challenge was that they had no idea what was normal activity on the network, and what wasn’t.

The end result of this is that they’d then spend huge amounts of time chasing down what were really just red herrings.

When you don’t know what normal activity looks like, everything looks odd.

Today, I see the same thing with traces of SQL Server activity, either using SQL Profiler (and/or SQL Trace), and Extended Events Profiler. I also see the same thing with insights data sent to Log Analytics, and the outcomes of many expensive SQL Server monitoring tools.

For example, if you are looking at a SQL Server trace, and you see a large number of sp_reset_connection commands. Is that an issue? When would it be an issue, and when is it just normal?

If I see an sp_reset_connection executed on a connection followed by a number of other commands, I know that the application is using connection pooling. If however, I see a bunch of those on the same connection, without any commands executed in between, I know that the application code is opening connections when it doesn’t need to. Perhaps it should be opening the connection closer to where it decides if it needs it.

The key point is that it’s really important that you learn to use these tools before you have a problem. You need to be able to recognize what’s normal, and what isn’t.

2018-12-11