2

Is there a way to filter a (2008 R2) profiler trace on a specific system process? I don’t mean the usual server process (SPID), but the system processes that append “s” onto the end of a SPID (eg .spid29s) as seen when looking at the SQL Server Logs.

The system processes are seen a lot when a server is first started.

0

2 Answers 2

3

So I actually went and tried this myself.

The "s" suffix in the log is only for display purposes to show that it was a system SPID. It's not actually part of the SPID.

In Profiler, there's an IsSystem column that you can filter on, but otherwise, the system SPIDs show up as per normal (i.e., "spid25s" is simply SPID 25), and you can filter as normal using the SPID column.

I'm not sure what problem you're having with that, as I was able to capture activity on the system SPIDs during my test.

That said, since traces don't persist on instance restart, it might be difficult, or impossible to capture startup activity immediately as the server comes up. You could start a trace by executing a startup stored procedure, or create an Extended Events session that does persist on startup.

While I didn't go to either of those extents, I tried resuming a trace using Profiler while I clicked Yes on the Service Control dialog to start the database engine service. I didn't see any activity at all.

Just because a process (system or otherwise) has activity (disk I/O, CPU, etc.) associated with it doesn't necessarily mean the process is running a T-SQL statement. It's possible there just isn't anything to see and analyze through this channel.

If you're having issues with slow startup, it may be more fruitful to look at the SQL Server error logs to see if there are any long-running operations that stick out.

6
  • Yessir, it's right there! I missed that completely. Nice catch! Jul 11, 2013 at 21:17
  • Wait...just to confirm...you are seeing spids < 50? I can capture system spids now, but I'm still not seeing any < 50. Jul 11, 2013 at 21:22
  • @SQLRockstar: Yep. I traced an instance where there was some Service Broker activity, and I saw that activity from SPIDs in the 20's (I don't remember the exact numbers).
    – Jon Seigel
    Jul 11, 2013 at 21:31
  • OK, thanks. I'll root around and get mine working. Very cool, thanks! Jul 11, 2013 at 21:32
  • @SQLRockstar: No problem. I didn't trace for very long, so I'm not sure if it's possible to see activity from all system processes, but I did see at least some.
    – Jon Seigel
    Jul 11, 2013 at 21:34
0

No, SQL Profiler only shows the server process ID (SPID) that is assigned by SQL Server to the process that is associated with the client.

You won't be able to filter for a specific system process using SQL Profiler.

You may want to look at using Extended Events instead.

1
  • 1
    I don't believe this is correct. See my answer.
    – Jon Seigel
    Jul 11, 2013 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.