Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Yessir, it's right there! I missed that completely. Nice catch! –  SQLRockstar Jul 11 '13 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. –  SQLRockstar Jul 11 '13 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 '13 at 21:31
    
OK, thanks. I'll root around and get mine working. Very cool, thanks! –  SQLRockstar Jul 11 '13 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 '13 at 21:34

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.

share|improve this answer
1  
I don't believe this is correct. See my answer. –  Jon Seigel Jul 11 '13 at 16:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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